none
How to display second column in ComboBox after selection? RRS feed

  • Question

  • I can click on my ComboBox and see the values of Column1 and Column2, but after I click on off the ComboBox, the value in Column1 is always displayed and I want the value of Column2 displayed.

    I tried this:

            With ComboBox2
                .Value = "None"
                .ColumnHeads = True
                .ColumnCount = 2
                .ColumnWidths = "50;100"
                .RowSource = "SetupQuestions!A42:B48"
                .BoundColumn = 2
            End With

    That didn't set the value as I thought it would.

    I tried this:

    Private Sub ComboBox2_AfterUpdate()
        ComboBox2.Value = ComboBox2.Column(2)
    End Sub

    That didn't set the value as I thought it would.

    How can I force the ComboBox to display the value in Column2 after a selection is made?

    Here is a screen shot.

    I want to see 'Campaign Name' in the ComboBox, but all I see is 1.


    MY BOOK

    Tuesday, January 10, 2017 9:20 PM

All replies

  • The text box part of a combo box displays the value of the first column whose width is larger than 0. So to display the second column, set the width of the first column to 0.

    Which column contains the Value of the combo box is determined by the BoundColumn property. If you set it to 1 (the default), the Value of the combo box is the value of its first column (even if that column is hidden because its width is 0). If you set it to 2, the Value of the combo box is the value of its second column, etc. And if you set BoundColumn to 0, the Value of the combo box equals the zero-based index of the selected item (the ListIndex).


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Tuesday, January 10, 2017 9:57 PM
  • Hi ryguy72,

    please use Combobox_click event.

    Combobox_afterevent will not execute when you change the selection.

    Combobox_change executes multiple time so it will give error.

    with Combobox_click it will work properly.

    Private Sub ComboBox1_Click()
    Dim str, str1 As String
    str = ComboBox1.ListIndex
    str1 = ComboBox1.Column(1, str)
    ComboBox1.Text = str1
    End Sub
    
    Private Sub UserForm_Initialize()
    ComboBox1.ColumnCount = 2
    
    Dim Films(1 To 5, 1 To 2) As String
    Dim i As Integer, j As Integer
    
     Films(1, 1) = "1"
     Films(2, 1) = "2"
     Films(3, 1) = "3"
     Films(4, 1) = "4"
     Films(5, 1) = "5"
    
     Films(1, 2) = "Adventure"
     Films(2, 2) = "Action"
     Films(3, 2) = "Sci-Fi"
     Films(4, 2) = "Crime"
     Films(5, 2) = "Drama"
    
     ComboBox1.List = Films
    End Sub

    Output:

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, January 11, 2017 4:54 AM
    Moderator
  • Deepak, your animation seems to do what I want to do, but I can't seem to get this working.  Basically, I'm referencing an array on a sheet.  It should be something like this:

    .RowSource = "Questions!A3:B45"

    OR

    .List = "Questions!A3:B45"

    I can't believe there is not an easier way to do this kind of thing.


    MY BOOK


    • Edited by ryguy72 Wednesday, January 11, 2017 2:53 PM
    Wednesday, January 11, 2017 2:52 PM
  • Actually, I just concatenated everything into one column.  This is so much easier for me and I'm umping up against a deadline soon.

    Thanks for the help!


    MY BOOK

    Wednesday, January 11, 2017 3:49 PM
  • Hi ryguy72,

    you had mentioned that,"Basically, I'm referencing an array on a sheet. "

    it doesn't matter how you fill the combobox. it is ok that you are referencing an array on the sheet.

    it is not the issue.

    issue is how to display value from column 2 when you select it from combobox.

    I just post the whole code for a sample demo.

    in the image that you had posted. in that I can see that values in combobox are displaying properly.

    so you just need to focus on code below.

    Private Sub ComboBox1_Click()
    Dim str, str1 As String
    str = ComboBox1.ListIndex
    str1 = ComboBox1.Column(1, str)
    ComboBox1.Text = str1
    End Sub
    

    it is very simple 4 line of code. I don't think that you can find any other option like settings property of combobox.

    if you have any problem to implement the suggestion then let me know about that. I will try to solve that.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, January 12, 2017 5:50 AM
    Moderator