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 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
Dowload a sample file for practice:
i am getting runtime error 9 subscript out of range
LastRow = ws.Range(“A” & Rows.Count).End(xlUp).Row
pls help me out o fthis.
I am also getting the same error not sure what it means?
did you ever find a solution?
Hi
I have worked. You need to do some changes if you copy from here. I rewrote it from the video.
Private Sub CommandButton1_Click()
Unload Me
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
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
Hi thank you for the information,
i watched the video “Fill Text Boxes on User Form Based on Selection in Combobox” and managed to get it to work.
but once i pull the details using the combo box, i need a “Submit” button on the form, to update any changes we need to make.
as example, i need to change the Attendance or Marks etc…
are you able to provide the code for this
Thank you
Sir I Am using This
If Me.ComboBox4.Text = “Cleared” Then
Worksheets(“General Ledger”).Cells(tx + 1, 1) = TextBox2.Text
End If
Worksheets(“General Ledger”).Cells(i, 2) = TextBox3.Text
If Me.ComboBox4.Text = “Cleared” Then
Worksheets(“General Ledger”).Cells(tx + 1, 2) = TextBox3.Text
End If
Worksheets(“General Ledger”).Cells(i, 3) = TextBox1.Text
If Me.ComboBox4.Text = “Cleared” Then
Worksheets(“General Ledger”).Cells(tx + 1, 3) = TextBox1.Text
End If
Worksheets(“General Ledger”).Cells(i, 4) = “Cash”
If Me.ComboBox4.Text = “Cleared” Then
Worksheets(“General Ledger”).Cells(tx + 1, 4) = “Cash”
End If
Worksheets(“General Ledger”).Cells(i, 5) = TextBox11.Text
If Me.ComboBox4.Text = “Cleared” Then
Worksheets(“General Ledger”).Cells(tx + 1, 5) = TextBox13.Text
End If
Worksheets(“General Ledger”).Cells(i, 6) = “Advance”
If Me.ComboBox4.Text = “Cleared” Then
Worksheets(“General Ledger”).Cells(tx + 1, 6) = “Total Bill”
End If
Worksheets(“General Ledger”).Cells(i, 7) = TextBox11.Text
If Me.ComboBox4.Text = “Cleared” Then
Worksheets(“General Ledger”).Cells(tx + 1, 7) = TextBox13.Text
End If
Worksheets(“General Ledger”).Cells(i, 8) = Me.TextBox10.Text
If Me.ComboBox4.Text = “Cleared” Then
Worksheets(“General Ledger”).Cells(tx + 1, 8) = “Bill Cleared”
End If
Sir I Want If Me.Combobox4.Text Is Changed Form “In Progress” To Cleared” Then
Data Goes to Work Sheet using +1
But If It Is Already Cleared
THan Do not Use +1 Just Change The Value Where It Is Already
Hello. Is it possible to replicate the user form created above in Microsoft word and draw data from excel? If so, please could you show me what the code would look like?
I am looking to select data from excel and copy it to a word user form. The selected information will be used to populate a word document.
I look forward to hearing from you
please Can you help me with this excel file. I trie but the userform doesn’t file automatically
coud you give me you gmail to send you the file?
Sir
I visited your website and check vba tutorials.
I want Vlookup formulae for Textbox to another textbox
can the same be done if i am using excel sheet instead of userform? i have inserted combo box and text boxes on sheet 1 and data is on sheet2.