PDA

View Full Version : USERFORM WITH MULTIPLE ENTRY LINES



DeanP
12-02-2018, 03:34 AM
I have created a userform (see attached). I'm having difficulty in finding information for setting up

(a) each of the combo-boxes
(b) auto populating cust name, loc & BS based on combo box selections

Do I have to name each of the boxes individually or can I leave them as ComboBox1, 2, 3 etc?

So far I have this code:

Private Sub UserForm_Initialise()
ComboBox1.List = Worksheets("Validation").Range("B2:B88").Value
End Sub
Private Sub ComboBox1_Change()
TextBox9.Text = Application.VLookup(ComboBox1.Value, Worksheets("Validation").Range("B2:C88"), 2, False)
TextBox10.Text = Application.VLookup(ComboBox1.Value, Worksheets("Validation").Range("B2:D88"), 3, False)
TextBox11.Text = Application.VLookup(ComboBox1.Value, Worksheets("Validation").Range("B2:E88"), 4, False)
End Sub

All the lines will be identical in terms of the data validation lists and vlookup ranges.

djemy1975
12-02-2018, 03:48 AM
Test this code please:


Private Sub ComboBox1_Change()
On Error Resume Next
'Look up values based on the first control
With Me
TextBox9 = Application.WorksheetFunction.VLookup(Me.ComboBox1.Value, Worksheets("Validation")..Range("B2:C88"), 2, 0)
TextBox10 = Application.WorksheetFunction.VLookup(Me.ComboBox1.Value, Worksheets("Validation").Range("B2:D88), 3, 0)
TextBox11 = Application.WorksheetFunction.VLookup(Me.ComboBox1.Value,Worksheets("Validation").Range("B2:E88"), 4, 0)




I have made a working userform herewith illustrating this code and do not forget to mark the threas as solved if the code worked for you