Pass data via "ListBox"

user_13675328 0
Viewed 37

I have a table with two columns:

enter image description here

I fill out a 'listbox' (cmbPersonal) as follows:

planPersonal.Range("A1", planPersonal.Range("A" & Application.Rows.Count).End(xlUp)).Name = "PersonalDynamic"
.cmbPersonal.RowSource = "PersonalDynamic"
.cmbPersonal.Value = ""
.cmbPersonal.Enabled = True

however, I wanted that when selecting a name in the 'listbox' VBA would fill in a 'textbox' (ID) with column B of my "Personal" spreadsheet. For example: Listbox: Erick TextBox: 3

How could I do this kind of combination?

vba excel
1 Answers

Try following in Userform object VBA code

If you have column B as it appears in the screen shot and you want TextBox1 value to be from column B corresponding to the ComboBox selected value.

Private Sub ComboBox1_Change()
UserForm1.TextBox1.Value = Application.VLookup(ComboBox1.Value, Range("PersonalDynamic"), 2, False)
End Sub

Private Sub UserForm_Initialize()
Dim planPersonal As Worksheet
Set planPersonal = ThisWorkbook.Sheets("Sheet1")

planPersonal.Range("A1", planPersonal.Range("B" & Application.Rows.Count).End(xlUp)).Name = "PersonalDynamic"

Me.ComboBox1.RowSource = "PersonalDynamic"
Me.ComboBox1.Value = ""
Me.ComboBox1.Enabled = True
End Sub

If you don't have column B and just want Index number of the ComboBox value in "PersonalDynamic"

Private Sub ComboBox1_Change()
UserForm1.TextBox1.Value = WorksheetFunction.Match(ComboBox1.Value, Range("PersonalDynamic"), 0) - 1
End Sub

Private Sub UserForm_Initialize()
Dim planPersonal As Worksheet
Set planPersonal = ThisWorkbook.Sheets("Sheet1")

planPersonal.Range("A1", planPersonal.Range("A" & Application.Rows.Count).End(xlUp)).Name = "PersonalDynamic"

Me.ComboBox1.RowSource = "PersonalDynamic"
Me.ComboBox1.Value = ""
Me.ComboBox1.Enabled = True
End Sub
user_98080630
Related