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
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

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,029

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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,669
Messages
5,838,685
Members
430,563
Latest member
Raeyven

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