adding items to combox dynamically

[davide128]

I have a combo form control directly on my worksheet, it's not in a userform. I'm having a hard time trying to reference the controls..for instance I have a control called ProdLineCombo. In my Module I have

Sub ProdLineCombo_Change()
 
End Sub

How do I reference another combo box called ProdTypeCombo from within the ProdLineCombo_Change() sub?

basically, depending on what a user selects from a certain comboxbox will determine or should I say populate other controls or another combox box..Can someone please give an example on how this is done..for example if Combo1 the user selects Type1 then Combo2 will contain "1","2","3" if they choose Type2 then Combo2 will contain "4","5,","6"

 

[tweedle]

Quick Sample:

Private Sub Worksheet_Activate()
ComboBox1.List = Array("1", "2", "3")
End Sub
 
Private Sub ComboBox1_Change()
Select Case ComboBox1.Value
Case Is = "1"
    ComboBox2.List = Array("Alligators", "Tigers", "Bears")
Case Is = "2"
    ComboBox2.List = Array("Worms", "Beetles", "Grubs")
Case Is = "3"
    ComboBox2.List = Array("Caterpillar", "Pupae", "Butterfly")
End Select
End Sub

 

[davide128]

I found this as well..is this up to date or the correct way to do it? Code is kind of hard to read so was wondering if it's the best way to achieve dependant combox boxes

http://www.xldynamic.com/source/xld.Dropdowns.html

 

[tweedle]

Yes, that (at a glance) appears to be a very dynamic method of producing cascading/dependent comboboxes.
There is a good deal of setup involved; but if you get it right, it looks like it would be manageable from an end-user perspective (regarding the Data sheet).

 

[davide128]

To expand on it..how do I show checkboxes only if a certain value is chosen from the combo dropdown list?

 

[davide128]

also, how do I expand the function below to handle more than 1 drop down..the way it's written especially with the line With combo.cboPrimary. I'd like for this function to be able to handle more than 1 combo primary. can something like combo.variable be used?

'---------------------------------------------------------------------
Public Function fzPopulatList1()
'---------------------------------------------------------------------
Dim i As Long
    Application.EnableEvents = False
    'On Error GoTo pl1_exit
 
    With combo.cboPrimary
        .Clear
        For i = 2 To Range(kList1Hnd).Count + 1
            .AddItem data.Cells(1, i).Value
        Next i
 
        Application.EnableEvents = True
        .ListIndex = 0
    End With
pl1_exit:
    Application.EnableEvents = True
 
End Function

 

[davide128]

think I got it..I call the function like this.

fzPopulatList1 ControlPanel.cboGlassType, "List1Values", 1

'---------------------------------------------------------------------
Public Function fzPopulatList1(cboCTRL As ComboBox, kRange As String, row_idx As Long)
'---------------------------------------------------------------------
Dim i As Long
    Application.EnableEvents = False
    'On Error GoTo pl1_exit
    
    'formula = kList1Hnd & CStr(idx)
    
    'With ControlPanel.cboGlassType
    With cboCTRL
        .Clear
        For i = 2 To range(kRange).Count + 1
            .AddItem ConfigData.Cells(row_idx, i).Value
        Next i
        
        Application.EnableEvents = True
        .ListIndex = 0
    End With
pl1_exit:
    Application.EnableEvents = True
    
End Function