Auto populate 2nd combobox based on 1st combobox data

[vknowth]

Hi friends,

I need a help and its been very long time posting here for a help from my friends.

I need to populate two combo boxes from excel sheet, the data will be like below:

Column A Column B
A 1
A 2
A 3
A 4
A 5
B 100
B 101
B 102

So from the above data, one combo box should hold unique values A & B.

On selecting a value from the 1st combo box A or B, respective values should be populated in 2nd combo box.

So the data should be like below:

If A is selected in the 1st combo box, then 2nd combo box should only show the values 1,2,3,4 & 5.
If B is selected in the 1st combo box, then 2nd combo box should only show the values 100,101 & 102.

Friends I need it in a macro and one important point is, this is dynamic and it is not static and the data can be more.

Please help me out to get out from this.

Thanks in advance.

 

[MickG]

Try this:-
Basic List ion sheet1 (Alter code to suit)
Combobox1 & 2 in same or any other sheet.
Place code in sheet with Combobox1 & 2

Option Explicit
[COLOR="Navy"]Dim[/COLOR] Dic [COLOR="Navy"]As[/COLOR] Object
Private [COLOR="Navy"]Sub[/COLOR] ComboBox1_Change()
ComboBox2.List = Application.Transpose(Split(Dic.Item(ComboBox1.Value), ","))
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]


Private [COLOR="Navy"]Sub[/COLOR] ComboBox1_GotFocus()
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Dn [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]With[/COLOR] Sheets("Sheet8")
[COLOR="Navy"]Set[/COLOR] Rng = .Range(.Range("A1"), .Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]End[/COLOR] With


[COLOR="Navy"]Set[/COLOR] Dic = CreateObject("scripting.dictionary")
Dic.CompareMode = vbTextCompare
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
       [COLOR="Navy"]If[/COLOR] Not Dic.Exists(Dn.Value) [COLOR="Navy"]Then[/COLOR]
            Dic.Add Dn.Value, Dn.Offset(, 1)
        [COLOR="Navy"]Else[/COLOR]
            Dic.Item(Dn.Value) = Dic.Item(Dn.Value) & "," & Dn.Offset(, 1)
        [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR]
ComboBox1.List = Dic.keys
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick