November 15, 2017

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 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:

11 thoughts on “Fill Text Boxes on User Form Based on Selection in Combobox

  1. 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.

    1. 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

      1. 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

  2. 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

  3. 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

  4. 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?

  5. 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.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.