Fill Text Boxes on User Form Based on Selection in Combobox

How to fill text box controls on user-form with data from Excel worksheet based on selection in a combobox. First we use a looping process to fill the combo-box will relevant items. This creates a dynamic combo-box which gets updated automatically when we add more data to our Excel worksheet. Next we select an item from the combo-box and then using a looping process find the item selected by the user and fill the text boxes with data from the other columns accordingly. If the data in the combo-box is a number, we need to convert it into a number by using the ‘val’ function. This is because the combo-box is a form control which is a combination of a text-box and a list-box and any data in the combo-box is ‘text’ data. Even if you have an item like ‘101’ in the comb0box, it is the data type text. If you don’t perform the conversion from text to number, you will not get the correct results. The close button on the user-form closes the form and removes it from memory. If you wish to fire the user-form when you open the workbook you will need to show the user-form as shown in the code below. Watch the video:

Watch this video on YouTube.

Here’s the complete macro VBA code to populate the text boxes automatically on selection made in the combo-box:

Private Sub CommandButton1_Click()
Unload Me
End Sub

Private Sub Image1_BeforeDragOver(ByVal Cancel As MSForms.ReturnBoolean, ByVal Data As MSForms.DataObject, ByVal X As Single, ByVal Y As Single, ByVal DragState As MSForms.fmDragState, ByVal Effect As MSForms.ReturnEffect, ByVal Shift As Integer)

End Sub

Private Sub UserForm_Initialize()
Dim i As Long, LastRow As Long, ws As Worksheet
Set ws = Sheets(“Sheet1”)
LastRow = ws.Range(“A” & Rows.Count).End(xlUp).Row
For i = 2 To LastRow
Me.ComboBox1.AddItem ws.Cells(i, “A”).Value
Next i
End Sub

Private Sub ComboBox1_Change()
Dim fpath As String
fpath = “C:\student-pics”
‘fpath = ThisWorkbook.Path
If Right(fpath, 1) <> “\” Then
fpath = fpath & “\”
End If
‘Me.Image1.Picture = LoadPicture(fpath & “ebadi.jpg”)
‘fpath = ThisWorkbook.Path & “\”
Dim i As Long, LastRow As Long, ws As Worksheet
Set ws = Sheets(“Sheet1”)
LastRow = ws.Range(“A” & Rows.Count).End(xlUp).Row

For i = 2 To LastRow
If Val(Me.ComboBox1.Value) = ws.Cells(i, “A”) Then
MsgBox Me.ComboBox1.Value
Me.TextBox1 = ws.Cells(i, “B”).Value
Me.TextBox2 = ws.Cells(i, “C”).Value
Me.TextBox3 = ws.Cells(i, “D”).Value
Me.TextBox4 = ws.Cells(i, “E”).Value

End If

Next i

End Sub

Private Sub Workbook_Open()
UserForm1.Show
End Sub