Vlookup Textbox in Userform

[Robby19]

Hello all,

I have a combo box on a userform set to a specific range. Whenever the user selects a value from within the combobox, I need the values of 26 textboxes within the same userform to populate numbers based off of a Table (Table7) in Sheet1.

Can you provide assistance with this?

 

[Robby19]

Bump.

 

[Leith Ross]

Hello Robby19,

The easiest way to solve this is for you to post a copy of the workbook to a file sharing site and post the link.

 

[Norie]

How is the combobox populated?

 

[Robby19]

@Leith Ross I cannot post this workbook.

How is the combobox populated?

The combobox is populated using RowSource = MAWMOS
MAWMOS is a defined range based off a list of numbers within a table.

 

[Norie]

Is MAWMOS a column in the table, a subset of a column in the table or something else?

Also, based on what's selected in the combobox how would you populate the 26 textboxes from the table?

For example, does each textbox correspond to a specific column in the table?

 

[Robby19]

Is MAWMOS a column in the table, a subset of a column in the table or something else?

This is the first column within the table that has all of the data.

For example, if the user selected from the drop down "111"

I would need the textboxes to populate the individual cell data from that row. There are, in total 24 columns (not counting the first). So a line for each textbox.

I would also need to populate 2 other textboxes from another sheet inside the workbook. But I am sure I could figure that out if we can get the other boxes to work.

 

[Norie]

Assuming the first column has unique values it should be possible to find the row in the table that corresponds to the value the user has selected from the combobox without using Vlookup/Find.

The reason for that is that the ListIndex of the selected item in the combobox should directly correspond to the (list) row in the table.

For example.

Private Sub ComboBox1_Change()
Dim rw As ListRow
Dim rng As Range
Dim idx As Long

    idx = ComboBox1.ListIndex
    
    If idx <> 0 Then

        ' get listrow that corresponds to selection in combobox
        Set rw = tbl.ListRows(ComboBox1.ListIndex + 1)

        ' get the range for that listrow
        Set rng = rw.Range
        
        ' code to populate textboxes from selected row in table
    End If

End Sub

 

[Robby19]

Would I use this to populate the textboxes?

Dim lRw As Long
lRw = Me.ComboBox1.ListIndex + 2
Sheet1.Cells(lRw, 2).Value = Me.TextBox1.Value
Sheet1.Cells(lRw, 3).Value = Me.TextBox2.Value

 

[Norie]

No, it would be more like this.

Me.TextBox1.Value = rng.Cells(1,2).Value      ' populate textbox from column 2 of the table

Me.TextBox2.Value = rng.Cells(1,3).Value      ' populate textbox from column 3 of the table