Excel - Synchronizing two combo boxes 1

[Zariic]

Hello:
I'm pretty much a VBA newbie and maybe this can't be done at all, but I've been stuck on this one for at least a week now and I have yet to find an example that helps me out. I need to have two combo boxes synchronized. Basically, I have a worksheet that has a column with Vendor Numbers and another column with their corresponding Vendor Names. I have been successful at filling the two combo boxes (VendorNumber and VendorName) from the worksheet but I need the combo boxes to sync with each other. In other words, if I select a specific Vendor Number from the Vendor Number Combo Box, it will select the corresponding Vendor Name in the Vendor Name combo box and visa versa. Any help with this would be immensely appreciated.

Thank you,

-Joe

 

[Zariic]

Oops! That should have been "vice versa." Oh well, there are probably more spelling errors than that. Can you edit threads in this forum? I don't see the option to. Anyway, can the above be done in Excel VBA? I have found examples for doing it with Access queries but none have helped me for Excel.

Thank you again,

-Joe

 

[rmikesmith]

Zariic,

You don't say if the combo boxes are on the worksheet or a Userform. I'll assume on the worksheet, for now, as well as the following: Vendor Numbers in, say A1:A20 and Vendor Names in B1:B20; Two combo boxes added to the worksheet from the Control Toolbox; ComboBox1's ListFillRange set to A1:A20 and ComboBox2's ListFillRange = B1:B20. For each combo, right-click and select View Code... from the shortcut menu. Make the Change event procedures look like:

Private Sub ComboBox1_Change()
  Application.EnableEvents = False
  With ComboBox1
    ComboBox2.ListIndex = .ListIndex
  End With
  Application.EnableEvents = True
End Sub

Private Sub ComboBox2_Change()
  Application.EnableEvents = False
  With ComboBox2
    ComboBox1.ListIndex = .ListIndex
  End With
  Application.EnableEvents = True
End Sub

Note, The Vendor Numbers and Names must be synchronized on the worksheet (and stay that way) in order for this to work.

It might be better to use a multicolumn combo that displays both the Vendor Numbers & Names together.

HTH
Mike

 

[Zariic]

Thanks, Mike.
Sorry I didn't mention it but it's a UserForm. Will this work for a UserForm? I'll try it out. Also, I didn't think to use a multicolumn combo. Actually I didn't know that existed until now. Haha.

Thank you,

-Joe

 

[rmikesmith]

Hi Joe,

The two event handlers I posted should work fine for comboboxes on a Userform.

For multi-column listboxes & comboboxes:

The column indexes are zero-based, same as the rows. You can access a partucular column entry using List; Example:

ComboBox1.List(2,1) = "Item X"

assigns the string "Item X" to the second column of the third row of the combo.

You can store associated info without displaying it to the user. In the Properties window, assign a value of zero to the ColumnWidths property for that column. This is a very handy feature. You can store an internal index number, part number, etc. with a descriptive phrase. The user selects an item and your code can access the index and use it as a lookup key.

Check out the Help for ListBox and ComboBox objects for additional info on how to use these objects.


Regards,
Mike

 

[Zariic]

Thanks, Mike!
The above code actually worked great for syncing the two boxes. I would have used the multi but I actually needed two separate boxes for my application. I appreciate the help!

Thank you,

-Joe