none
Cascading Combo box VBA for Excel 2007 4 or 5 Level RRS feed

  • Question

  • i am stuck up in Cascading Combo Box

    I want to made the following

    Combo Box -1 :::Comtype::  Knee,DBR, HCB, PG
    Combo Box -2 ::: Comvoltage:: DBR( 400,245,145,72.5), HCB( 245,145,72.5), PG( 245,145), Knee(765KV)
    Combo Box -3 ::: Commaterial:: AL& CU ::: DBR( 400-CU,400-AL,245-CU,145-AL,72.5-AL), PG( 245-AL,145-AL)
    Combo Box -4 ::: Comcurrent:: 4000/3150/2500/2000/1600 IF I Chose DBR,420,AL, then only 3150A will show. If i chose DBR 245,AL,then 4000,2500 will show in the drop down,

    Further i have one Drop down Required say combo Box- 5

    comboBox5- comstc= 40,50,63

    I dont want to use Excel Range i want to use the complete VBA script
    Please support so far i have use following.

    Private Sub comtype_Change()
    Dim index1 As Integer
    index1 = comtype.ListIndex
    comvoltage.Clear
    Select Case index1
    Case Is = 0
    With comvoltage
    '' Knee Type
    .AddItem "765KV"
    End With

    Case Is = 1
    With comvoltage
    '' DBR
    .AddItem "420KV"
    .AddItem "245KV"
    .AddItem "145KV"
    .AddItem "72.5KV"
    .AddItem "36 KV"
    End With
    Case Is = 2
    With comvoltage
    '' HCB
    .AddItem "420KV"
    .AddItem "245KV"
    .AddItem "145KV"
    .AddItem "72.5KV"
    .AddItem "36 KV"

    End With
    Case Is = 3
    With comvoltage
    ''PG
    .AddItem "420KV"
    .AddItem "245KV"

    End With


    End Select
    End Sub


    Private Sub UserForm_Initialize()
    With comtype
    .AddItem "Knee"
    .AddItem "DBR"
    .AddItem "HCB"
    .AddItem "PG"

    End With

    End Sub

    Thanking you in Advance

    Tuesday, September 9, 2014 8:01 AM

Answers

  • i am stuck up in Cascading Combo Box

    That is not surprising, if the nesting is deep you either need a lot of code or a good organization of the data. I prefer the second. :-)

    Let us assume that we have a tree, we add a node and this node can have some values, then we are finished with this node. Now we can add another node and do the same.

    To get this to work we need 3 routines:

    SubNode (to add a node)
    AddItems (to add the items)
    EndNode (to finish the node)

    When we further assume that each node can have also (sub-) nodes, then we can build a tree in each style we like. And we can use the names of the nodes as values and fill our combo boxes.

    Okay, sounds complicated, but it isn't, have a look at this:

        .SubNode "America"
          .SubNode "Canada"
            .SubNode "Quebec"
              .AddItems "Montreal", "Quebec City", "Laval"
            .EndNode
            .SubNode "Yukon"
              .AddItems "Carmacs", "Dawson"
            .EndNode
          .EndNode
          
          .SubNode "USA"
            .SubNode "Oregon"
              .AddItems "Portland", "Salem", "Eugene"
            .EndNode
            .SubNode "Utah"
              .AddItems "Fairfield", "Vineyard"
            .EndNode
          .EndNode
        .EndNode
        
        .SubNode "Europe"
          .SubNode "Germany"
            .SubNode "Niedersachsen"
              .AddItems "Hannover", "Oldenburg"
            .EndNode
            .SubNode "Bayern"
              .AddItems "München", "Augsburg", "Nürnberg"
            .EndNode
          .EndNode
        .EndNode
    

    Even if you don't understand whats going one in my code, you can see that this builds a tree of continent\country\state\cities right?

    When we save that tree into a appropriate structure, then we can fill your combo boxes with just a few lines of code. Here is the sample file:

    https://dl.dropboxusercontent.com/u/35239054/Cascade%20ComboBox.xls

    Andreas.

    Tuesday, September 9, 2014 4:44 PM