How to populate a combobox based on selection from another combobo

[LinnT]

Hi,
I have 2 combobox with data inside.
When I select an item (eg. x) from ComboBox1, I require ComboBox2 to
contain/filter only related items to x.

Can I confirm that I couldn't use any existing Excel functions to do that?
How do I do that with macro?

I tried to do that by creating a Form in macro and adding comboboxes to the
Form. However I would still prefer to do it in a worksheet.

Thanks.

 

[trevosef]

Hi,
I have 2 combobox with data inside.
When I select an item (eg. x) from ComboBox1, I require ComboBox2 to
contain/filter only related items to x.

Can I confirm that I couldn't use any existing Excel functions to do that?
How do I do that with macro?

I tried to do that by creating a Form in macro and adding comboboxes to the
Form. However I would still prefer to do it in a worksheet.

Thanks.

Hi LinnT,

The example 'Dependent Lists Country City' in this website
http://www.contextures.com/excelfiles.html#DataVal uses existing Excel
functions to achieve this dependency.

Regards
trevosef

 

[LinnT]

Hi Trevosef,

Thanks! It was a big help.

I have another question.
Once I have made a selection in combobox1 and combobox2, when I reselected
another item from combobox1, the combobox2 still show the last selected value
which might not be very "friendly". How do I set combobox2 to blank when I do
a reselection from combobox1?

Regards.

 

[trevosef]

Hi Trevosef,

Thanks! It was a big help.

I have another question.
Once I have made a selection in combobox1 and combobox2, when I reselected
another item from combobox1, the combobox2 still show the last selected value
which might not be very "friendly". How do I set combobox2 to blank when I do
a reselection from combobox1?

Regards.








- Show quoted text -

Hi LinnT,

Try it this works. Open VBE (Press Alt+F11). Double-click on Sheet1
(the sheet with the validation combo boxes). Then, Copy and Paste the
following code:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel
As Boolean)
Dim rngCountry As Range
Dim rngCity As Range
Set rngCountry = Sheet1.Range("B3")
Set rngCity = Sheet1.Range("C3")
If Target.Address = rngCountry.Address Then
If Not IsEmpty(rngCity) Then
rngCity.Value = ""
End If
End If
End Sub

The above assumes that country is specified in cell B3 and city in
cell C3. Double-click on country (B3) should clear the value in city
(C3).

Regards
trevosef

 

[LinnT]

Hi Trevosef,

The script does not really do what I wanted. Instead of double-click I was
actually thinking of on selectionchange of the combobox1.
Nevertheless, thanks for the suggestion.

Best Regards.

 

[trevosef]

Hi Trevosef,

The script does not really do what I wanted. Instead of double-click I was
actually thinking of on selectionchange of the combobox1.
Nevertheless, thanks for the suggestion.

Best Regards.










- Show quoted text -

Hi LinnT,

To clear the value in city combobox2, while in design mode, double-
click on the Country combobox1 to get to the code window. In the code
window, it should show combobox1 on the left box. Select the change
event on the right box. It should display:
Private sub combobox1_change()

'To clear city combobox2 when country combobox1 value is changed
combobox2 = ""

End Sub
The above code assumes that country is combobox1 and city is
combobox2.
Hope this helps

Regards
Trevosef





In the code window,

 

[LinnT]

Thanks!

Hi LinnT,

To clear the value in city combobox2, while in design mode, double-
click on the Country combobox1 to get to the code window. In the code
window, it should show combobox1 on the left box. Select the change
event on the right box. It should display:
Private sub combobox1_change()

'To clear city combobox2 when country combobox1 value is changed
combobox2 = ""

End Sub
The above code assumes that country is combobox1 and city is
combobox2.
Hope this helps

Regards
Trevosef





In the code window,