MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Synchronize two combo boxes on a user form

  Asked By: Mona    Date: Feb 10    Category: MS Office    Views: 1741

I know you can help. Would you please suggest me how to Synchronize
two combo boxes on a user form. I have a combo box named categories
and another one with products. I am looking to synchronize them but
don't know how. The purpose is to make the categories combo box filter
only those products which are under its category. Suppose I have many
products which are under different categories. It's hard to find a
certain product, but if it would be possible to select a specific
category to filter only those products of the category selected.



8 Answers Found

Answer #1    Answered By: Doyle Gonzalez     Answered On: Feb 10

What form  is your data in? Is it in a spreadsheet with a column for category
and a column for the product  name?

In principle I would set up a filter  and then filter on the category and use
the filtered data to fill the second combo  box.

Answer #2    Answered By: Balbir Kaur     Answered On: Feb 10

The data is going to be large, that's why I will collect it on a spreadsheet.
Obviously I will have two columns, one with the category info and the other with
the products. I was thinking to make it based on a filter, but don't know how. I
know how two make it in MS Access, by means of a query, but not in Excel. If you
suggest me how to solve this "tinny" problem I will be very happy with my boss.
I promised him this problem can be solved, taking into consideration the help  I
can get from you. I told the boss how you helped with the combo  box problem, he
was very exited.

Answer #3    Answered By: Rene Sullivan     Answered On: Feb 10

I will have to play around with this and will get back to you if nobody else
posts a good answer before I do.

Answer #4    Answered By: Milton Robinson     Answered On: Feb 10

This is not difficult. Do the following:

1. Setup two columns of data in worksheet (sheet1):

- Enter "Category" in cell(A1).
- Enter "Products" in cell(B1).
- Enter the different products  starting from cell(B2) going
downwards, with the corresponding category for each product  starting
from cell(A2) downwards.

2. Create a new form  (frm1) with two listboxes (lstCategory &

3. Paste the following lines of code in the code window of frm1:


Private Sub UserForm_Activate()
Dim rngCategory As Range, rngDummy As Range
Dim colCategories As New Collection
Dim element As Variant

' Identify the range of category cells
With ThisWorkbook.Worksheets("Sheet1")
Set rngCategory = .Range(.Range("A2"), .Range("A2").End
End With

' Get a list of UNIQUE categories into a new collection
On Error Resume Next
For Each rngDummy In rngCategory
colCategories.Add rngDummy, CStr(rngDummy)
Next rngDummy
On Error GoTo 0

' Fill the lstCategories listbox with the UNIQUE categories
For Each element In colCategories
lstCategory.AddItem element
Next element

select  the first category in lstCategory listbox
lstCategory.ListIndex = 0
End Sub

Private Sub lstCategory_Change()
Dim strSelectedCategory As String
Dim rngCategory As Range, rngDummy As Range

' Get the name of the selected  category
strSelectedCategory = lstCategory.Column(0, lstCategory.ListIndex)

' Empty lstProducts listbox

' Identify the range of category cells
With ThisWorkbook.Worksheets("Sheet1")
Set rngCategory = .Range(.Range("A2"), .Range("A2").End
End With

' Show products that match the selected category
For Each rngDummy In rngCategory
If rngDummy = strSelectedCategory Then
lstProducts.AddItem rngDummy.Offset(0, 1)
End If
Next rngDummy
End Sub

Answer #5    Answered By: Vinit Online     Answered On: Feb 10

It works perfectly. You say it's not so difficult and it is so when get
somebody's help.

Answer #6    Answered By: Jake Williams     Answered On: Feb 10

Now that I managed to synchronize the boxes  another problem arised. Maybe you
can help  me?
In the third column I have information on the price of the product. Now I want
to make some kind of code to place the price of the product  on the form. It will
be used later for order information which going to be placed on the "Order's
data" spreadsheet. What I need is to get the cost information of the product on
the form  with two combo  boxes. If you have any idea how to make this I will be
very pleased.

Answer #7    Answered By: Muriel Dunn     Answered On: Feb 10

Again, this is not difficult. Do as follows:

1. Setup the "Price" column in worksheet (sheet1), with "Price" in
cell(C1), and the price of each product  starting from cell(C2) going
downwards. Fill all cells with prices, without any blank cells.

2. Add a new textbox (txtPrice) to form.

3. Add the following NEW code:


Private Sub lstProducts_Change()
Dim strSelectedProduct As String
Dim rngProducts As Range, rngDummy As Range

' Get the name of the selected  product
strSelectedProduct = lstProducts.Column(0, lstProducts.ListIndex)

' Identify the range of product cells
With ThisWorkbook.Worksheets("Sheet1")
Set rngProducts = .Range(.Range("B2"), .Range("B2").End
End With

' Show the price of the selected product
For Each rngDummy In rngProducts
If rngDummy = strSelectedProduct Then
txtPrice = rngDummy.Offset(0, 1)
Exit For
End If
Next rngDummy
End Sub


Also, add the following pieces of code in the previously-created
event procedures:

Private Sub lstCategory_Change()

' Clear the txtPrice textbox
txtPrice = ""
End Sub

Private Sub UserForm_Activate()

' Lock the txtPrice textbox
txtPrice.Locked = True
End Sub


When you select  a product from lstProducts listbox, the selected
string is compared against the "Products" column in worksheet, until
a match is found, and the price value is written into the txtPrice
LOCKED textbox.

Answer #8    Answered By: Trae Thompson     Answered On: Feb 10

I am sending this to corrent a line of code in my last post (with the
new txtPrice textbox).

Please change the line:

Private Sub lstProducts_Change()


Private Sub lstProducts_Click()

Hopefully, it will work smoothly. Sorry, for the error.

Didn't find what you were looking for? Find more on Synchronize two combo boxes on a user form Or get search suggestion and latest updates.