Userform Comboboxes - how to populate back and forth

[Erich Duff]

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

 

[JONESY]

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