Excel dependent drop down forms?

dafreedomfita

New Member
Joined
Aug 11, 2007
Messages
37
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
 

Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
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

Code:
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

New Member
Joined
Aug 11, 2007
Messages
37
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

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
1) What is the sheet name that you have 2 comboboxes?
2) where do you have the list? (Sheet name and range)
 

tusharm

MrExcel MVP
Joined
May 28, 2002
Messages
11,028

ADVERTISEMENT

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

New Member
Joined
Aug 11, 2007
Messages
37
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

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995

ADVERTISEMENT

I have edited my previous post.

Follow the step then try.
 

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,416
Messages
5,596,010
Members
414,037
Latest member
Roamingsmile

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top