How to loop through Userform ComboBoxes and perform actions

[jmwbowen]

I am struggling with VBA code to loop through ComboBoxes on a userform and perform actions. The userform has other controls on it besides ComboBoxes.

I've tried:

Private Sub UserForm_Initialize()
Dim ctrl As Control
numTemplates = Application.WorksheetFunction.Count(shtDSORTTemplates.Range("A:A"))
For i = 2 To numTemplates + 1
    Me.lstTemplates.AddItem Application.WorksheetFunction.Index(shtDSORTTemplates.Range("C:C"), i)
Next i


For Each ctrl In Me.Controls
    If TypeName(ctrl) = "ComboBox" Then
        For Each cell In shtTrainingAids.Range("tblTrainingAidsList[[#Data],[s_TrainingAidName]]")
            ctrl.AddItem cell.Value
        Next cell
        
    End If
Next ctrl
End Sub

The part in question is the 2nd "For" loop. Using that method of looping through controls doesn't give me access to properties that are specific to comboboxes, such as ".locked".

How can I loop through ONLY the comboboxes in the userform?

 

[VBA Geek]

declare another variable as MsFORMS.COMBOBOX

Private Sub UserForm_Initialize()
Dim ctrl As Control, cbox As MSForms.ComboBox
numTemplates = Application.WorksheetFunction.Count(shtDSORTTemplates.Range("A:A"))
For i = 2 To numTemplates + 1
Me.lstTemplates.AddItem Application.WorksheetFunction.Index(shtDSORTTemplates.Range("C:C"), i)
Next i


For Each ctrl In Me.Controls
If TypeName(ctrl) = "ComboBox" Then
Set cbox = ctrl

For Each cell In shtTrainingAids.Range("tblTrainingAidsList[[#Data],[s_TrainingAidName]]")
cbox.AddItem cell.Value
Next cell

End If
Next ctrl
End Sub