HELP - Advanced Userform 3 level dependent drop down

B

bemcbride

New Member
Joined
May 21, 2012
Messages
47
I wanted to make a drop down userform with 3 levels. (Level 1 decides Level 2 decides Level 3). The goal is to have the users of the form be able to select from drop downs to get to level 3 instead of having to look through all the level 3 items.

I was able to get 1 and 2 to work however level 3 isn't working correctly becuase in my code it is not considering the first level only the second.

I copied a snapshot of the levels (there is about 10 level 1, 80 level 2, and 400 level 3 options (which is why we want the drop down obviously)and the code I used previously.







DivisionMajorIndustry
DIVISION A. AGRICULTURE, FORESTRY, AND FISHING 01 AGRICULTURAL PRODUCTION-CROPS 011 CASH GRAINS
DIVISION A. AGRICULTURE, FORESTRY, AND FISHING 01 AGRICULTURAL PRODUCTION-CROPS 013 FIELD CROPS, EXCEPT CASH GRAINS
DIVISION A. AGRICULTURE, FORESTRY, AND FISHING 01 AGRICULTURAL PRODUCTION-CROPS 016 VEGETABLES AND MELONS
DIVISION A. AGRICULTURE, FORESTRY, AND FISHING 01 AGRICULTURAL PRODUCTION-CROPS 017 FRUITS AND TREE NUTS
DIVISION A. AGRICULTURE, FORESTRY, AND FISHING 01 AGRICULTURAL PRODUCTION-CROPS 018 HORTICULTURAL SPECIALTIES
DIVISION A. AGRICULTURE, FORESTRY, AND FISHING 01 AGRICULTURAL PRODUCTION-CROPS 019 GENERAL FARMS, PRIMARILY CROP
DIVISION A. AGRICULTURE, FORESTRY, AND FISHING 02 AGRICULTURAL PRODUCTION-LIVESTOCK AND ANIMAL SPECIALTIES 021 LIVESTOCK, EXCEPT DAIRY AND POULTRY
DIVISION A. AGRICULTURE, FORESTRY, AND FISHING 02 AGRICULTURAL PRODUCTION-LIVESTOCK AND ANIMAL SPECIALTIES 024 DAIRY FARMS
DIVISION A. AGRICULTURE, FORESTRY, AND FISHING 02 AGRICULTURAL PRODUCTION-LIVESTOCK AND ANIMAL SPECIALTIES 025 POULTRY AND EGGS
DIVISION A. AGRICULTURE, FORESTRY, AND FISHING 02 AGRICULTURAL PRODUCTION-LIVESTOCK AND ANIMAL SPECIALTIES 027 ANIMAL SPECIALTIES
DIVISION A. AGRICULTURE, FORESTRY, AND FISHING 02 AGRICULTURAL PRODUCTION-LIVESTOCK AND ANIMAL SPECIALTIES 029 GENERAL FARMS, PRIMARILY LIVESTOCK AND ANIMAL SPECIALTIES
DIVISION A. AGRICULTURE, FORESTRY, AND FISHING 07 AGRICULTURAL SERVICES 071 SOIL PREPARATION SERVICES
DIVISION A. AGRICULTURE, FORESTRY, AND FISHING 07 AGRICULTURAL SERVICES 072 CROP SERVICES
DIVISION A. AGRICULTURE, FORESTRY, AND FISHING 07 AGRICULTURAL SERVICES 074 VETERINARY SERVICES
DIVISION A. AGRICULTURE, FORESTRY, AND FISHING 07 AGRICULTURAL SERVICES 075 ANIMAL SERVICES, EXCEPT VETERINARY
DIVISION A. AGRICULTURE, FORESTRY, AND FISHING 07 AGRICULTURAL SERVICES 076 FARM LABOR AND MANAGEMENT SERVICES
DIVISION A. AGRICULTURE, FORESTRY, AND FISHING 07 AGRICULTURAL SERVICES 078 LANDSCAPE AND HORTICULTURAL SERVICES
DIVISION A. AGRICULTURE, FORESTRY, AND FISHING 08 -FORESTRY 081 TIMBER TRACTS
DIVISION A. AGRICULTURE, FORESTRY, AND FISHING 08 -FORESTRY 083 FOREST NURSERIES AND GATHERING OF FOREST PRODUCTS
DIVISION A. AGRICULTURE, FORESTRY, AND FISHING 08 -FORESTRY 085 FORESTRY SERVICES
DIVISION A. AGRICULTURE, FORESTRY, AND FISHING 09 FISHING, HUNTING, AND TRAPPING 091 COMMERCIAL FISHING
DIVISION A. AGRICULTURE, FORESTRY, AND FISHING 09 FISHING, HUNTING, AND TRAPPING 092 FISH HATCHERIES AND PRESERVES
DIVISION A. AGRICULTURE, FORESTRY, AND FISHING 09 FISHING, HUNTING, AND TRAPPING 097 HUNTING AND TRAPPING, AND GAME PROPAGATION
DIVISION B. MINING 10 METAL MINING 101 IRON ORES
DIVISION B. MINING 10 METAL MINING 102 COPPER ORES
DIVISION B. MINING 10 METAL MINING 103 LEAD AND ZINC ORES
DIVISION B. MINING 10 METAL MINING 104 GOLD AND SILVER ORES
DIVISION B. MINING 10 METAL MINING 106 FERROALLOY ORES, EXCEPT VANADIUM
DIVISION B. MINING 10 METAL MINING 108 METAL MINING SERVICES
DIVISION B. MINING 10 METAL MINING 109 MISCELLANEOUS METAL ORES
DIVISION B. MINING 12 COAL MINING 122 BITUMINOUS COAL AND LIGNITE MINING
DIVISION B. MINING 12 COAL MINING 123 ANTHRACITE MINING
DIVISION B. MINING 12 COAL MINING 124 COAL MINING SERVICES
DIVISION B. MINING 13 OIL AND GAS EXTRACTION 131 CRUDE PETROLEUM AND NATURAL GAS
DIVISION B. MINING 13 OIL AND GAS EXTRACTION 132 NATURAL GAS LIQUIDS
DIVISION B. MINING 13 OIL AND GAS EXTRACTION 138 OIL AND GAS FIELD SERVICES
DIVISION B. MINING 14 MINING AND QUARRYING OF NONMETALLIC MINERALS, EXCEPT FUELS 141 DIMENSION STONE
DIVISION B. MINING 14 MINING AND QUARRYING OF NONMETALLIC MINERALS, EXCEPT FUELS 142 CRUSHED AND BROKEN STONE, INCLUDING RIPRAP
DIVISION B. MINING 14 MINING AND QUARRYING OF NONMETALLIC MINERALS, EXCEPT FUELS 144 SAND AND GRAVEL
DIVISION B. MINING 14 MINING AND QUARRYING OF NONMETALLIC MINERALS, EXCEPT FUELS 145 CLAY, CERAMIC, AND REFRACTORY MINERALS
DIVISION B. MINING 14 MINING AND QUARRYING OF NONMETALLIC MINERALS, EXCEPT FUELS 147 CHEMICAL AND FERTILIZER MINERAL MINING
DIVISION B. MINING 14 MINING AND QUARRYING OF NONMETALLIC MINERALS, EXCEPT FUELS 148 NONMETALLIC MINERALS SERVICES, EXCEPT FUELS
DIVISION B. MINING 14 MINING AND QUARRYING OF NONMETALLIC MINERALS, EXCEPT FUELS 149 MISCELLANEOUS NONMETALLIC MINERALS, EXCEPT FUELS
DIVISION C. CONSTRUCTION 15 BUILDING CONSTRUCTION-GENERAL CONTRACTORS AND OPERATIVE BUILDERS 152 GENERAL BUILDING CONTRACTORS-RESIDENTIAL BUILDINGS
DIVISION C. CONSTRUCTION 15 BUILDING CONSTRUCTION-GENERAL CONTRACTORS AND OPERATIVE BUILDERS 153 OPERATIVE BUILDERS
DIVISION C. CONSTRUCTION 15 BUILDING CONSTRUCTION-GENERAL CONTRACTORS AND OPERATIVE BUILDERS 154 GENERAL BUILDING CONTRACTORS-NONRESIDENTIAL BUILDINGS

<colgroup><col><col><col span="2"></colgroup><tbody>
</tbody>






Private Sub UserForm_Initialize()


With ComboBox1
.AddItem "Division A: Agriculture, Forestry, And Fishing"
.AddItem "Division B: Mining"
.AddItem "Division C: Construction"
.AddItem "Division D: Manufacturing"
.AddItem "Division E: Transportation, Communications, Electric, Gas, And Sanitary Services"
.AddItem "Division F: Wholesale Trade"
.AddItem "Division G: Retail Trade"
.AddItem "Division H: Finance, Insurance, And Real Estate"
.AddItem "Division I: Services"
.AddItem "Division J: Public Administration"
.AddItem "Division K: Nonclassifiable Establishments"

End With


End Sub


Private Sub ComboBox1_Change()


Dim index As Integer
index = ComboBox1.ListIndex


ComboBox2.Clear


Select Case index
Case Is = 0
With ComboBox2
.AddItem "Major Group 01: Agricultural Production Crops"
.AddItem "Major Group 02: Agriculture production livestock and animal specialties"
.AddItem "Major Group 07: Agricultural Services"
.AddItem "Major Group 08: Forestry"
.AddItem "Major Group 09: Fishing, hunting, and trapping"
End With
Case Is = 1
With ComboBox2
.AddItem "Major Group 10: Metal Mining "
.AddItem "Major Group 12: Coal Mining"
.AddItem "Major Group 13: Oil And Gas Extraction"
.AddItem "Major Group 14: Mining And Quarrying Of Nonmetallic Minerals, Except Fuels"
End With
Case Is = 2
With ComboBox2
.AddItem "Major Group 15: Building Construction General Contractors And Operative Builders"
.AddItem "Major Group 16: Heavy Construction Other Than Building Construction Contractors"
.AddItem "Major Group 17: Construction Special Trade Contractors"
End With
Case Is = 3
With ComboBox2
.AddItem "Major Group 20: Food And Kindred Products"
.AddItem "Major Group 22: Textile Mill Products"
.AddItem "Major Group 23: Apparel And Other Finished Products Made From Fabrics And Similar Materials"
.AddItem "Major Group 24: Lumber And Wood Products, Except Furniture"
.AddItem "Major Group 25: Furniture And Fixtures"
.AddItem "Major Group 26: Paper And Allied Products"
.AddItem "Major Group 27: Printing, Publishing, And Allied Industries"
.AddItem "Major Group 28: Chemicals And Allied Products"
.AddItem "Major Group 29: Petroleum Refining And Related Industries"
.AddItem "Major Group 30: Rubber And Miscellaneous Plastics Products"
.AddItem "Major Group 31: Leather And Leather Products"
.AddItem "Major Group 32: Stone, Clay, Glass, And Concrete Products"
.AddItem "Major Group 33: Primary Metal Industries"
.AddItem "Major Group 34: Fabricated Metal Products, Except Machinery And Transportation Equipment"
.AddItem "Major Group 35: Industrial And Commercial Machinery And Computer Equipment"
.AddItem "Major Group 36: Electronic And Other Electrical Equipment And Components, Except Computer Equipment"
.AddItem "Major Group 37: Transportation Equipment"
.AddItem "Major Group 38: Measuring, Analyzing, And Controlling Instruments; Photographic, Medical And Optical Goods; Watches And Clocks"
.AddItem "Major Group 39: Miscellaneous Manufacturing Industries"
End With
Case Is = 4
With ComboBox2
.AddItem "Major Group 40: Railroad Transportation"
.AddItem "Major Group 41: Local And Suburban Transit And Interurban Highway Passenger Transportation"
.AddItem "Major Group 42: Motor Freight Transportation And Warehousing"
.AddItem "Major Group 43: United States Postal Service"
.AddItem "Major Group 44: Water Transportation"
.AddItem "Major Group 45: Transportation By Air"
.AddItem "Major Group 46: Pipelines, Except Natural Gas"
.AddItem "Major Group 47: Transportation Services"
.AddItem "Major Group 48: Communications"
.AddItem "Major Group 49: Electric, Gas, And Sanitary Services"
End With

Case Is = 5
With ComboBox2
.AddItem "Major Group 50: Wholesale Trade-durable Goods"
.AddItem "Major Group 51: Wholesale Trade-non-durable Goods"
End With

Case Is = 6
With ComboBox2
.AddItem "Major Group 52: Building Materials, Hardware, Garden Supply, And Mobile Home Dealers"
.AddItem "Major Group 53: General Merchandise Stores"
.AddItem "Major Group 54: Food Stores"
.AddItem "Major Group 55: Automotive Dealers And Gasoline Service Stations"
.AddItem "Major Group 56: Apparel And Accessory Stores"
.AddItem "Major Group 57: Home Furniture, Furnishings, And Equipment Stores"
.AddItem "Major Group 58: Eating And Drinking Places"
.AddItem "Major Group 59: Miscellaneous Retail"
End With

Case Is = 7
With ComboBox2
.AddItem "Major Group 60: Depository Institutions"
.AddItem "Major Group 61: Non-depository Credit Institutions"
.AddItem "Major Group 62: Security And Commodity Brokers, Dealers, Exchanges, And Services"
.AddItem "Major Group 63: Insurance Carriers"
.AddItem "Major Group 64: Insurance Agents, Brokers, And Service"
.AddItem "Major Group 65: Real Estate"
.AddItem "Major Group 67: Holding And Other Investment Offices"
End With

Case Is = 8
With ComboBox2
.AddItem "Major Group 70: Hotels, Rooming Houses, Camps, And Other Lodging Places"
.AddItem "Major Group 72: Personal Services"
.AddItem "Major Group 73: Business Services"
.AddItem "Major Group 75: Automotive Repair, Services, And Parking"
.AddItem "Major Group 76: Miscellaneous Repair Services"
.AddItem "Major Group 78: Motion Pictures"
.AddItem "Major Group 79: Amusement And Recreation Services"
.AddItem "Major Group 80: Health Services"
.AddItem "Major Group 81: Legal Services"
.AddItem "Major Group 82: Educational Services"
.AddItem "Major Group 83: Social Services"
.AddItem "Major Group 84: Museums, Art Galleries, And Botanical And Zoological Gardens"
.AddItem "Major Group 86: Membership Organizations"
.AddItem "Major Group 87: Engineering, Accounting, Research, Management, And Related Services"
.AddItem "Major Group 88: Private Households"
.AddItem "Major Group 89: Miscellaneous Services"
End With

Case Is = 9
With ComboBox2
.AddItem "Major Group 91: Executive, Legislative, And General Government, Except Finance"
.AddItem "Major Group 92: Justice, Public Order, And Safety"
.AddItem "Major Group 93: Public Finance, Taxation, And Monetary Policy"
.AddItem "Major Group 94: Administration Of Human Resource Programs"
.AddItem "Major Group 95: Administration Of Environmental Quality And Housing Programs"
.AddItem "Major Group 96: Administration Of Economic Programs"
.AddItem "Major Group 97: National Security And International Affairs"
End With



Case Is = 10
With ComboBox2
.AddItem "Major Group 99: Nonclassifiable Establishments"
End With


End Select


End Sub




Private Sub ComboBox2_Change()
Dim index As Integer
index = ComboBox2.ListIndex


ComboBox3.Clear


Select Case index
Case Is = 0
With ComboBox3
Me.ComboBox3.List = Worksheets("SicRevised").Range("G4:G9").Value
End With

Case Is = 1
With ComboBox3
Me.ComboBox3.List = Worksheets("SicRevised").Range("G10:G14").Value
End With

Case Is = 2
With ComboBox3
Me.ComboBox3.List = Worksheets("SicRevised").Range("G15:G20").Value
End With

Case Is = 3
With ComboBox3
Me.ComboBox3.List = Worksheets("SicRevised").Range("G21:G23").Value
End With

Case Is = 4
With ComboBox3
Me.ComboBox3.List = Worksheets("SicRevised").Range("G24:G26").Value
End With

Case Is = 5
With ComboBox3
Me.ComboBox3.List = Worksheets("SicRevised").Range("G27:G33").Value
End With



....etc.
End Select

End Sub
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Sort by date Sort by votes
Based on your Data in "Sheet8" Change Sheet Name in "Code" where shown, to suit
With Userform1 and Comboboxes 1 to 3, Try this in your Userform Module.
Code: 
Option Explicit
Dim Dic As Object
Private Sub UserForm_Initialize()
Dim Dn As Range, Rng As Range
Dim Q As Variant, k As Variant
Me.ComboBox1.Clear
    With Sheets("Sheet8") 'Change data sheet name to suit !!!
        Set Rng = .Range(.Range("A2"), .Range("A" & Rows.Count).End(xlUp))
    End With
 Set Dic = CreateObject("Scripting.Dictionary")
        Dic.CompareMode = 1
   For Each Dn In Rng
            If Not Dic.exists(Dn.Value) Then
                Set Dic(Dn.Value) = CreateObject("Scripting.Dictionary")
            End If
        
        If Not Dic(Dn.Value).exists(Dn.Offset(, 1).Value) Then
              ReDim nray(1 To Rng.Count)
                 nray(1) = Dn.Offset(, 2).Value
                Dic(Dn.Value).Add (Dn.Offset(, 1).Value), Array(nray, 1)
        Else
                Q = Dic(Dn.Value).Item(Dn.Offset(, 1).Value)
                Q(1) = Q(1) + 1
                Q(0)(Q(1)) = Dn.Offset(, 2).Value
                Dic(Dn.Value).Item(Dn.Offset(, 1).Value) = Q
        End If
    Next Dn
  Me.ComboBox1.List = Application.Transpose(Dic.keys)
End Sub
Private Sub ComboBox1_Click()
Dim p As Variant
 With Me.ComboBox2
   .Clear
   .List = Dic(ComboBox1.Value).keys
 End With
End Sub

Private Sub ComboBox2_Click()
Dim p As Variant
 With Me.ComboBox3
    .Clear
    .List = Dic(ComboBox1.Value).Item(ComboBox2.Value)(0)
 End With
End Sub

Regrds Mick
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question
Code to use multiple trigger cells to hide / unhide rows of corresponding question sets
Replies
1
Views
748
dmt32
D
B
  • Question
Dependent ComboBox
Replies
3
Views
1K
AlphaFrog
AlphaFrog
B
  • Question
Dependent ComboBox Drop Down ISSUES
Replies
3
Views
1K
L. Howard
L
B
  • Question
Shorten code...possible?
Replies
4
Views
895
Rick Rothstein
Rick Rothstein

Forum statistics

Threads
1,214,592
Messages
6,120,433
Members
448,961
Latest member
nzskater

Share this page

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
Back
Top