Add "All" option for each drop-down list of combo box

[Manish Sharma]

Hi Friends,

I have 3 ActiveX Combo box which are dynamic, I mean to say that the drop-down list changes by its own if we make any changes in the 3 column (Column XFA:XFC) from where the combo boxes are pulling it's list.

Now I am trying to insert "All" option for each of the possible drop-down list in Combo-box 2 and Combo-box 3 without disturbing the data in column (XFA:XFC).

For example:-
If in comb-box 1 "Group 2" is selected then
in combo-box 2 drop-down list should display (All, Latin America, Asia Pacific)
and combo-box 3 should contain 3 different drop-down list for the above mentioned 3 Subgroups which are (All); (All, China H, Indonesia, Korea); (All, Malaysia, Philippines, Thailand)

If required you can change the VBA code.
Here is the file

If i am not clear with my description, then please ask me.

Regards,

 

[SirJB7]

Hi, Manish Sharma!
At a first glance what's more evident is that you need a map, man. If you choose Latin America in 2nd combo box, you get China, Indonesia and Korea...
Shouldn't you change them by Mars, Saturn and Andromeda?
Regards!

 

[Manish Sharma]

With help from my other online friends, i have find out the code for my above problem. Here it is so that some one else can get help from it...

Private Sub ComboBox1_Change()
ComboBox2.Clear
ComboBox3.Clear
If ComboBox1.ListIndex > -1 Then ComboBox2.List = Split(roy(ComboBox1.Value, 1), ","): _
ComboBox2.ListIndex = 0
End Sub

Private Sub ComboBox2_Change()
ComboBox3.Clear
If ComboBox2.ListIndex > -1 Then ComboBox3.List = Split(roy(ComboBox2.Value, 2), ","): _
ComboBox3.ListIndex = 0
End Sub

Function roy(s As String, k As Long) As String
Dim deb, i&, raj$
deb = Me.Range("XFA1").CurrentRegion: raj = ",All,"
If s = "All" Then
For i = 2 To UBound(deb)
If InStr(raj, "," & deb(i, k + 1) & ",") = 0 Then raj = raj & deb(i, k + 1) & ","
Next
Else
For i = 2 To UBound(deb)
If deb(i, k) = s Then If InStr(raj, "," & deb(i, k + 1) & ",") = 0 Then raj = raj & deb(i, k + 1) & ","
Next
End If
roy = Mid(raj, 2, Len(raj) - 2)
End Function

 

[SirJB7]

Hi, Manish Sharma!
Thanks for sharing the solution with the community.
Regards!