HELP - Advanced Userform 3 level dependent drop down

[bemcbride]

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.







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






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

 

[MickG]

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.

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