Combobox selection populates textboxes

TOMTRUCK · Sep 19th 2017

Hi all,

If I have a userform with three text boxes and one combo box. (like attached)

I am looking to be able to select an option in the combobox and the textboxes auto-fill with the data from the same row as the selected option in the combo box.

The code I have so far is this

Private Sub CommandButton1_Click()
'Copy input values to sheet.
Dim lRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Data")
lRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
With ws
.Cells(lRow, 1) = TextBox1.Value
.Cells(lRow, 2) = TextBox2.Value
.Cells(lRow, 3) = TextBox3.Value
.Cells(lRow, 4) = ComboBox1.Value
End With
'Clear input controls.
TextBox1.Value = ""
TextBox2.Value = ""
TextBox3.Value = ""
ComboBox1.Value = ""
End Sub

Private Sub ComboBox1_DropButtonClick()
ComboBox1.List = Worksheets("Data").Range("D2:D10").Value
End Sub

So if the selection in the combobox relates to the cell D2 for example, then textbox1 would show data from cell A2, TextBox2 fromB2 on so on.

Thank you in advance.

Tom

sktneer · Sep 19th 2017

Re: Combobox selection populates textboxes

Please give this a try...

Place the following code on UserForm Module.

Code

Private Sub ComboBox1_Change()
Dim ws As Worksheet
Dim Rng As Range
Dim Sel
Set ws = Sheets("Data")
Sel = Me.ComboBox1.Value
If Sel <> "" Then
    Set Rng = ws.Columns(4).Find(Sel, lookat:=xlWhole)
    If Not Rng Is Nothing Then
        Me.TextBox1.Value = ws.Cells(Rng.Row, "A")
        Me.TextBox2.Value = ws.Cells(Rng.Row, "B")
        Me.TextBox3.Value = ws.Cells(Rng.Row, "C")
    Else
        Me.TextBox1.Value = ""
        Me.TextBox2.Value = ""
        Me.TextBox3.Value = ""
    End If
End If
End Sub

Display More

TOMTRUCK · Sep 19th 2017

Re: Combobox selection populates textboxes

Works perfectly, thank you sktneer

Tom.

sktneer · Sep 19th 2017

Re: Combobox selection populates textboxes

You're welcome Tom! Glad it worked.

Combobox selection populates textboxes

Participate now!

Share