How to select an option in the combobox and the textboxes to auto-fill with data
Category: [Excel] Demo Available
How to select an option in the combobox and the textboxes to auto-fill with data
Requirement:
If the user has a userform with three text boxes and one combo box. (like attached)
The user is 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 the user has 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.
Solution:
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
Obtained from the OzGrid Help Forum.
Solution provided by sktneer.
See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets
See also:
| How to copy master sheet as values and automatically set new name |
| How to change fill color of autoshapes based on result of formula in a cell |
| How to copy cell data to another sheet and save it automatically |
| How to create and auto run macro if value on cell A1 is less than value on B2 |
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.