How to display data in textbox using combobox selection in excel VBA RRS feed

  • Question

  • Hi there ,

    I'm working in excel 2013 and I need help in manipulating combo box

    I have one worksheet "sheet1" full of huge data of employees (ID, Name , Nationality ,...etc.) more than 4000 names

    anyway I make a user form with one combo box and 6 textbox and tow Command Button .

    I need to select the id number from combo box and show the details of employee in that textboxes,  please advice how.

    Hope you can understand what I'm trying to ask here...  

    Saturday, August 24, 2013 10:23 PM

All replies

  • Use the following code to fill the list box with the values from the column in question - here Column A

    Private Sub Userform_Initialize()
    Dim vCol As Variant
    Dim Lrow As Long
        Lrow = Sheets("Sheet1").UsedRange.Rows(Sheets("Sheet1").UsedRange.Rows.Count).Row
        vCol = Sheets("Sheet1").Range("A1:A" & Lrow).Value
        Me.ListBox1.List = vCol
    End Sub

    Then when you click the list, use the following to display the values from the sheet

    Private Sub ListBox1_Click()
    Dim sRow As Long
        If Me.ListBox1.ListIndex > -1 Then
            sRow = Me.ListBox1.ListIndex + 1
        End If
        Me.TextBox1.Text = Cells(sRow, 5) 'value from Column 5 (E)
        Me.TextBox2.Text = Cells(sRow, 6)
    End Sub

    Graham Mayor - Word MVP

    Sunday, August 25, 2013 1:08 PM