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
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,999
Messages
5,834,813
Members
430,323
Latest member
Regash

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top