+ Reply to Thread
Results 1 to 11 of 11

Excel 2016 - Userform: ComboBox multiple columns selected show all columns in text field

  1. #1
    Registered User
    Join Date
    09-22-2017
    Location
    Thailand
    MS-Off Ver
    2016
    Posts
    32

    Excel 2016 - Userform: ComboBox multiple columns selected show all columns in text field

    Hi I know this question have been up some times now when looking on google but I haven't found one solution yet that works and now I'm in need for this so maybe someone can actually solve this.

    If you have a userform ComboBox list with 3 populated columns normally if you selected on that list you will get a return from column a ofc you can change this around to make it show column b or c but how can you make it show all if not is it possible to make a workaround so the text field in the combobox show all 3 column i the textfield when you select from the list

    maybe a solution like CONCATENATE all 3 columns first and then write them to the text field of the combobox is this possible and if so how

    hope you can help

  2. #2
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    Re: Excel 2016 - Userform: ComboBox multiple columns selected show all columns in text fie

    It would be much easier to populate the three data columns into one column of the combobox.

    Please Login or Register  to view this content.
    Surround your VBA code with CODE tags e.g.;
    [CODE]your VBA code here[/CODE]
    The # button in the forum editor will apply CODE tags around your selected text.

  3. #3
    Registered User
    Join Date
    09-22-2017
    Location
    Thailand
    MS-Off Ver
    2016
    Posts
    32

    Re: Excel 2016 - Userform: ComboBox multiple columns selected show all columns in text fie

    yes maybe you are right then I just have to rewrite my search then but if you tell me that it would be a mess to do it then I have to do that

    Thx for the replay AlphaFrong

  4. #4
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    Re: Excel 2016 - Userform: ComboBox multiple columns selected show all columns in text fie

    If you better explain your whole userform, I could then make an informed recommendation. Or better yet, explain it and attach an example workbook.

    An alternative is to populate the first column of the combobox with the concatenated three data columns and then also populate three more columns each with the individual data columns. The first column could be hidden in the dropdown list but it would show up as the selection in the combobox text.

    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    09-22-2017
    Location
    Thailand
    MS-Off Ver
    2016
    Posts
    32

    Re: Excel 2016 - Userform: ComboBox multiple columns selected show all columns in text fie

    That one worked perfectly thx.. I didn't drop the code or sheet because I didn't think I could expect people to look though a lot of code just to help me out


    Thx a lot AlphaFrog

  6. #6
    Registered User
    Join Date
    09-22-2017
    Location
    Thailand
    MS-Off Ver
    2016
    Posts
    32

    Re: Excel 2016 - Userform: ComboBox multiple columns selected show all columns in text fie

    AlphaFrong is there a way to use that code also in a button I use for search in the same ComboBox

    I have a textbox where I write a number to search and then press a search button in column 3 of the combobox the search is working and give me back what I want but when I selected it will only show the column 1 as in the beginning before you gave me the other code


    my code for the search button looks like this:

    Please Login or Register  to view this content.


    and my code for the combobox looks like this "UserForm_Initialize()":

    Please Login or Register  to view this content.
    I have no code in
    Please Login or Register  to view this content.
    this is how my sheet looks like

    (A1)First name (B1)Last name (C1)PSC Number
    Alan Newell 37128
    Allan Pilkington 20883
    Last edited by Ankers; 09-23-2017 at 03:05 PM.

  7. #7
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    Re: Excel 2016 - Userform: ComboBox multiple columns selected show all columns in text fie

    Quote Originally Posted by Ankers View Post
    AlphaFrong is there a way to use that code also in a button I use for search in the same ComboBox

    I have a textbox where I write a number to search and then press a search button in column 3 of the combobox the search is working and give me back what I want but when I selected it will only show the column 1 as in the beginning before you gave me the other code

    Your description doesn't match your code and I'm not sure what result you want. Your current search code is searching four columns (not just column 3) on the sheet and populating Combobox2 with five columns of data for each match. Your description reads as if you want to match the PSC Number from textbox1 and re-populate combobox2 with the matched results. is that correct?

  8. #8
    Registered User
    Join Date
    09-22-2017
    Location
    Thailand
    MS-Off Ver
    2016
    Posts
    32

    Re: Excel 2016 - Userform: ComboBox multiple columns selected show all columns in text fie

    yes it have to re-populate combobox2 with the matched results and then if I press/selected one of the results in the combobox it will write all the columns from that row
    there in the text field of the combobox at it is now if I press one after a search it will only show column 1 "first name"
    the reason I do it like this is because the list is 45000 long so if I make it with formula instead excel will crash

  9. #9
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    Re: Excel 2016 - Userform: ComboBox multiple columns selected show all columns in text fie

    Quote Originally Posted by Ankers View Post
    yes it have to re-populate combobox2 with the matched results and then if I press/selected one of the results in the combobox it will write all the columns from that row
    there in the text field of the combobox at it is now if I press one after a search it will only show column 1 "first name"
    the reason I do it like this is because the list is 45000 long so if I make it with formula instead excel will crash
    Instead of searching the sheet data for a match, search column 4 of combobox2 (the column with the PSC Number) and if it doesn't match, delete the item from the combobox.

    Please Login or Register  to view this content.
    Last edited by AlphaFrog; 09-24-2017 at 08:55 AM. Reason: sp

  10. #10
    Registered User
    Join Date
    09-22-2017
    Location
    Thailand
    MS-Off Ver
    2016
    Posts
    32

    Re: Excel 2016 - Userform: ComboBox multiple columns selected show all columns in text fie

    OK that I can see the idea in then I just need to call the hole list before ever search in case of the person write wrong the first timer and have to do more then one search but that I know how to do. Thanks a lot for your help it's much appreciated I have been stuck on the for way to many hours ��
    Last edited by Ankers; 09-24-2017 at 04:20 AM.

  11. #11
    Registered User
    Join Date
    09-22-2017
    Location
    Thailand
    MS-Off Ver
    2016
    Posts
    32

    Re: Excel 2016 - Userform: ComboBox multiple columns selected show all columns in text fie

    BTW thanks for the comment you put in so I can learn the codes

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Userform Combobox to populate form: Can I change the order of the columns in the combobox?
    By CraigWiggins in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 10-31-2014, 12:14 PM
  2. Looking up values based on a field of text/ number, multiple columns
    By engen44 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-25-2014, 09:26 AM
  3. [SOLVED] Multiple Column ComboBox in UserForm to two different Columns in Worksheet
    By Dezertdog in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-24-2014, 08:45 PM
  4. [SOLVED] Macros to transpose from multiple columns to selected columns and maintaining cell format
    By rrajnish in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 10-09-2013, 01:45 PM
  5. Transposing data from multiple columns to selected columns
    By rrajnish in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-15-2013, 02:49 PM
  6. Userform Combobox - Show 2 columns Use data from 2nd Column
    By CRIMEDOG in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-12-2011, 03:26 PM
  7. Replies: 3
    Last Post: 05-03-2010, 11:23 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1