Option Explicit

Const sngUFM_WIDTH As Single = 300
Const sngUFM_HEIGHT As Single = 500

Private pComboboxes As Collection

Private Sub UserForm_Initialize()

    'resize the userform
    Me.Width = sngUFM_WIDTH
    Me.Height = sngUFM_HEIGHT
    
    AddComboboxes
End Sub



Private Sub AddComboboxes()
    Const sngCBX_WIDTH As Single = 100
    Const sngCBX_HEIGHT As Single = 20
    Const intCBX_TO_ADD As Integer = 10
    
    Dim intCounter As Integer
    Dim cbx As c_Combobox
    Dim ctl As MSForms.ComboBox
    
    If pComboboxes Is Nothing Then Set pComboboxes = New Collection
    
    'for this example let's add 10 comboboxes to our userform
    For intCounter = 1 To intCBX_TO_ADD
    
        'add the control
        Set ctl = Me.Controls.Add(bstrProgID:="Forms.ComboBox.1", _
                                    Name:="Combobox" & intCounter, _
                                    Visible:=True)
                      
        With ctl
            'position the combobox
            .Width = sngCBX_WIDTH
            .Height = sngCBX_HEIGHT
            .Left = Me.Left + ((Me.Width - ctl.Width) / 2)
            .Top = Me.Top + (Me.Height / (intCBX_TO_ADD + 2) * intCounter)
            
            
            'populate the combobox
            .List = Sheet1.Range(Sheet1.Cells(2, intCounter), Sheet1.Cells(9, intCounter)).Value
            
            
            Set cbx = New c_Combobox
            cbx.SetCombobox ctl
            pComboboxes.Add cbx
        End With
        
    Next intCounter
    
                                    
    
End Sub

Option Explicit

Public WithEvents cbx As MSForms.ComboBox

Sub SetCombobox(ctl As MSForms.ComboBox)
    Set cbx = ctl
End Sub

'a generic change event handler for our comboboxes
Private Sub cbx_Change()
    If cbx.ListIndex > -1 Then
        MsgBox "You clicked on " & cbx.Name & vbLf & _
                "The value is " & cbx.Value
    End If
End Sub