Excel dependent drop down forms?

[dafreedomfita]

I am trying to put together two combo boxes, Combo1 and Combo2, the content of Combo2 will depend on the selection made in Combo1, for example Combo1 will have 10 items, once an item is selected Combo2 will have different sub selections relating to that item. I know you can do this via Validation, but the problem with that I have about 4000 options in the combo1 and about 60000 in the second, here is the structure of my data:

First Option /Second Option
Option1_____1.1
Option1_____ 1.2
Option1_____ 1.3
Option2_____ 2.1
Option2_____ 2.2
Option2_____ 2.3
Option3_____ 3.1
Option3_____ 3.2
Option3_____ 3.3

My ultimate final result should look like this, two drop down menus once Option1 is selected second drop down form should display these options:

Option1 /_____ 1.1
____________ 1.2
____________ 1.3

see attachment for complete clarity

http://www.605am.com/excel.gif

Thanking in advance for your help,
Stan

 

[jindon]

1) Right click on Sheet1 sheet tab then click [ViewCode]
2) Paste the code there and click on x to close the window
3) select other sheet once and select sheet1 again

Private dic As Object

Private Sub Worksheet_Activate()
Dim a, i As Long, w()
Set dic = CreateObject("Scripting.Dictionary")
dic.CompareMode = vbTextCompare
With Sheets("sheet2")
     a = .Range("f17", .Range("f" & Rows.Count).End(xlUp)).Resize(,2).Value
End With
For i = 2 To UBound(a,1) '<- change to 1 if you don't have header
     If Not IsEmpty(a(i,1)) Then
          If Not dic.exists(a(i,1)) Then
               ReDim w(0) : w(0) = a(i,2)
               dic.add a(i,1), w
          Else
               w=dic(a(i,1)) : ReDim Preserve w(UBound(w) + 1)
               w(UBound(w)) = a(i,2)
               dic(a(i,1)) = w
          End If
     End IF
Next
ComboBox1.List = dic.keys
End Sub

Private Sub ComboBox1_Click()
ComboBox2.Clear
If dic.exists(ComboBox1.Value) Then ComboBox2.List = dic(ComboBox1.Value)
End Sub

 

[dafreedomfita]

sorry the link with the attachment is fixed, can I please ask for a crash course in VB as I am VB challenged and this script doesn't make a lot of sense

 

[jindon]

1) What is the sheet name that you have 2 comboboxes?
2) where do you have the list? (Sheet name and range)

 

[tusharm]

For a non-VBA solution that works with data in relational tables see
Cascading queries
http://www.tushar-mehta.com/excel/newsgroups/cascading_dropdowns/index.html

I am trying to put together two combo boxes, Combo1 and Combo2, the content of Combo2 will depend on the selection made in Combo1, for example Combo1 will have 10 items, once an item is selected Combo2 will have different sub selections relating to that item. I know you can do this via Validation, but the problem with that I have about 4000 options in the combo1 and about 60000 in the second, here is the structure of my data:

First Option /Second Option
Option1_____1.1
Option1_____ 1.2
Option1_____ 1.3
Option2_____ 2.1
Option2_____ 2.2
Option2_____ 2.3
Option3_____ 3.1
Option3_____ 3.2
Option3_____ 3.3

My ultimate final result should look like this, two drop down menus once Option1 is selected second drop down form should display these options:

Option1 /_____ 1.1
____________ 1.2
____________ 1.3

see attachment for complete clarity

http://www.605am.com/excel.gif

Thanking in advance for your help,
Stan

 

[dafreedomfita]

Great, thanks for you help,

These names and ranges could really be anything, but for the sake of this example lets say

1)"What is the sheet name that you have 2 comboboxes? " Sheet1
2) "where do you have the list? (Sheet name and range)"

Sheet2, first Option which is the data in column F (F17:F25), and the second set of data which is in column G in the attachment above will be (G17:G25).

Thanks heaps jindon,

Stan

 

[jindon]

I have edited my previous post.

Follow the step then try.

 

[dafreedomfita]

appreciate your help, but this doesn't seem to work, unless I am doing something wrong

 

[jindon]

Arrr

Can you change
a = .Range(17, .Range("f" & Rows.Count).End(xlUp)).Resize(,2).Value

to

a = .Ragne'("f17", .Range("f" & Rows.Count).End(xlUp)).Resize(,2).Value

 

[dafreedomfita]

see screen shots

http://www.605am.com/excel2.gif
http://www.605am.com/excel3.gif