Making a combo box dependent on the value of another

[Sauver78]

Hi All,


I have searched through this and other forums about dependent combo boxes and have found the excel way of linking them, not the vba way though.


Here is my case. I have the following piece of code which populates 3 combo boxes from a matrix sheet with the necessary information:

[pre]

With WSMatrix
For i = 5 To 208
If .Cells(7, i).Value <> "" Then
Categories.Add .Cells(7, i).Value
End If
Next i
Set Categories = SortData(Categories)
n = Categories.Count
For i = 1 To n
Me.ComboBoxFilterCategory.AddItem Categories(i)
Next i

For i = 163 To 195
If .Cells(2, i).Value <> "" Then
IndicCategories.Add .Cells(2, i).Value
End If
Next i
Set IndicCategories = SortData(IndicCategories)
n = IndicCategories.Count
For i = 1 To n
Me.ComboBoxIndicCategory.AddItem IndicCategories(i)
Next i

For i = 163 To 195
If .Cells(7, i).Value <> "" Then
Indicators.Add .Cells(7, i).Value
End If
Next i
Set Indicators = SortData(Indicators)
n = Indicators.Count
For i = 1 To n
Me.ComboBoxIndicator1.AddItem Indicators(i)
Next i

Me.ComboBoxIndicator1.Text = Range("D3").Value
End With

[/pre]

i is the numeric value of the columns with the information I want. As it is right now ComboBoxIndicator1 has all the indicators on the list. How can I make the indicators that appear in comboBoxIndicator1 vary according to what the user chose on ComboBoxIndicCategory?


Thanks in advance for your help. I have tried many ways but without success.

 

[NARAYANK991]

Hi ,


Two points :


1. Asking anyone to debug code without the help of data or objects is expecting a lot ; you can make others' job easier if you can upload your workbook.


2. The code you have posted is only populating all the 3 comboboxes ; if at all you want the comboBoxIndicator1 to be dependent on the choice made in the ComboBoxIndicCategory , then the populating of the comboBoxIndicator1 has to be done separately in an event procedure , not in a procedure which populates all the comboboxes.


Narayan

 

[Sauver78]

Thanks Narayan,


I'm sorry for posting only a part of it. I am new to VBA and these forums and still trying to learn the rules for posting.


Your comment though made it easy for me to find a solution. I created another event and now it works fine, with the Indicator combobox being dependent on the Category one.


The sub I added was:


*******

Private Sub ComboBoxIndicCategory_Change()


Dim i As Long

Dim n As Long

Dim Indicators As New Collection

Dim Cel As Range


Me.ComboBoxIndicator1.Clear


With WSMatrix

For i = 163 To 170

If Me.ComboBoxIndicCategory.Text = "Supply of Vehicles and Road" And .Cells(7, i).Value <> "" Then

Indicators.Add .Cells(7, i).Value

End If

Next i

For i = 171 To 172

If Me.ComboBoxIndicCategory.Text = "Affordability" And .Cells(7, i).Value <> "" Then

Indicators.Add .Cells(7, i).Value

End If

Next i

Set Indicators = SortData(Indicators)

n = Indicators.Count

For i = 1 To n

Me.ComboBoxIndicator1.AddItem Indicators(i)

Next i


End With


ExitSub:


Set Cel = Nothing


End Sub


********


Thanks again for your help.