Userform Comboboxes - how to populate back and forth

Erich Duff

Board Regular
Joined
Apr 20, 2005
Messages
129
All,

I have a userform with two comboboxes on it (for the purposes of this question, anyway). One would have customer numbers and one would have customer names. I would like the user to be able to select from either box and have the other box populate with the appropriate information: e.g.

Cust #----Cust Name
1----------Customer 1
2----------Customer 2
3----------Customer 3

I have these lists as named ranges on a sheet. Once these boxes have been populated properly I have other Comboxes and textboxes that will reference the customer number and use it to add data via a Vlookup elsewhere in the workbook.

So any ideas how to do this? It's really just the back-and-forth I'm looking for now, I think I can figure out the Vlookup and data entry once I've got that down.

Thanks,

Erich
 

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

JONESY

Board Regular
Joined
Jul 23, 2004
Messages
63
Hi Erich

don't know whether this will be of help but...

Its a scripting dictionary writtin by one of the board members Jindon
It populates comboboxes with data that matches the first criteria, then the next and so forth. I have a similar version of the below populating about 19 comboboxes. Hope this helps

L



Private r As Range, dic As Object

Private Sub userform_initialize()
Dim x
Set dic = CreateObject("Scripting.Dictionary")
With Sheets("ComboInfo")
For Each r In .Range("a2", .Range("a65536").End(xlUp))
If Not IsEmpty(r) And Not dic.exists(r.Value) Then
dic.Add r.Value, Nothing
End If
Next
End With
x = dic.keys
Me.ComboBox1.List = x
End Sub

Private Sub ComboBox1_Change()
Me.ComboBox2.Clear: Me.ComboBox2.Clear
Set dic = CreateObject("Scripting.dictionary")
With Sheets("ComboInfo")
For Each r In .Range("a2", .Range("a65536").End(xlUp))
If r = Me.ComboBox1.Value Then
If Not dic.exists(r.Offset(, 1).Value) Then
Me.ComboBox2.AddItem r.Offset(, 1)
dic.Add r.Offset(, 1).Value, Nothing
End If
End If
Next
End With
With Me.ComboBox2
If .ListCount = 1 Then .ListIndex = 0
End With
End Sub

Private Sub ComboBox2_Change()
Dim x
Me.ComboBox3.Clear
With Sheets("ComboInfo")
For Each r In .Range("a2", .Range("a65536").End(xlUp))
If r = Me.ComboBox1.Value And r.Offset(, 1) = Me.ComboBox2.Value Then
x = r.Offset(, 2) & r.Offset(, 3)
Me.ComboBox3.AddItem x
End If
Next
End With
With Me.ComboBox3
If .ListCount = 1 Then .ListIndex = 0
End With
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,118,436
Messages
5,572,094
Members
412,441
Latest member
kelethymos
Top