VBA - Handling Events from many Comboboxes, using class

Question

I have the following issue.

My VBA program generates dinamically many comboboxes as I expected.

The problem comes when I try to handle their change-events. Searching on the web I saw that a class module is needed in these situations, so that's what I did.

Here is my class-module-code (class name: DB_ComboBox)

    Private WithEvents DB_ComboBoxEvents As MSForms.ComboBox
    Private DB_ComboBox_Line As Integer

    Private Sub DB_ComboBoxEvents_Change()
        MsgBox ("Line : " & DB_ComboBox_Line)
        'Here I will handle The comboboxes changes
    End Sub

    Public Property Let Box(value As MSForms.ComboBox)
        Set DB_ComboBoxEvents = value
    End Property
    Public Property Get Box() As MSForms.ComboBox
        Set Box = DB_ComboBoxEvents
    End Property


    Public Property Let Line(value As Integer)
        DB_ComboBox_Line = value
    End Property

    Public Property Get Line() As Integer
        Line = DB_ComboBox_Line
    End Property

Here the (simplified) code-portion in which I generate the comboboxes. As you can see, I'm trying to insert all the comboboxes into the array "customBox()"

Option Explicit

Private customBox() As New DB_ComboBox

Dim G_DBRigaInizioErrori As Integer
Dim G_IncBoxes As Integer

G_DBRigaInizioErrori = 5

For G_IncBoxes = G_DBRigaInizioErrori To 10
    CreateComboBox G_DBRigaInizioErrori, G_IncBoxes
Next


Sub CreateComboBox(DBRigaInizioErrori, IncCBoxes)

    Dim curCombo As Object
    Dim ws As Worksheet
    Dim rng As Range


    ReDim Preserve customBox(IncCBoxes - DBRigaInizioErrori)


    Set ws = ActiveSheet

    With ws

        Set rng = .Range("J" & IncCBoxes)


        Set curCombo = .Shapes.AddFormControl(xlDropDown, _
                                          Left:=rng.Left, _
                                          Top:=rng.Top, _
                                          Width:=rng.Width, _
                                          Height:=rng.Height)


        With curCombo

            .ControlFormat.AddItem "1", 1
            .name = "myCombo" & IncCBoxes

        End With

        Set customBox(IncCBoxes - DBRigaInizioErrori) = New DB_ComboBox
        customBox(IncCBoxes - DBRigaInizioErrori).Box = curCombo
        customBox(IncCBoxes - DBRigaInizioErrori).Line = IncCBoxes


    End With
End Sub

When I run this, I get the error "13" (not corresponding type) on the following line:

    customBox(IncCBoxes - DBRigaInizioErrori).Box = curCombo

How can I fix this?

Thank you in advance

EDIT

Basing on the suggestions, I made the following changes:

1) Class Module

        Private WithEvents DB_ComboBoxEvents As Excel.OLEObject
    Private DB_ComboBox_Line As Integer

    Private Sub DB_ComboBoxEvents_Change()
        MsgBox ("Line : " & DB_ComboBox_Line)
        'Here I will handle The comboboxes changes
    End Sub

    Public Property Let Box(value As Excel.OLEObject)
        Set DB_ComboBoxEvents = value
    End Property
    Public Property Get Box() As Excel.OLEObject
        Set Box = DB_ComboBoxEvents
    End Property


    Public Property Let Line(value As Integer)
        DB_ComboBox_Line = value
    End Property

    Public Property Get Line() As Integer
        Line = DB_ComboBox_Line
    End Property

(I'm not sure about "Excel OLEObject", but the compiler doesn't complain , so I guess it is compatible with "WIthEvents")

"Main" Module:

    Option Explicit

    Private customBox() As New DB_ComboBox

    Dim G_DBRigaInizioErrori As Integer
    Dim G_IncBoxes As Integer

    G_DBRigaInizioErrori = 5

    For G_IncBoxes = G_DBRigaInizioErrori To 10
        CreateComboBox G_DBRigaInizioErrori, G_IncBoxes
    Next


    Sub CreateComboBox(DBRigaInizioErrori, IncCBoxes)

        Dim curCombo As Object
        Dim rng As Range
        Dim tot_items As Integer
        Dim incAddItem As Integer

        ReDim Preserve customBox(IncCBoxes - DBRigaInizioErrori)


        tot_items = 5

        Set rng = ActiveSheet.Range("J" & IncCBoxes)
        Set curCombo = ActiveSheet.OLEObjects.Add(ClassType:="Forms.ComboBox.1", Link:=False, DisplayAsIcon:=False, Left:=rng.Left, Top:=rng.Top, Width:=rng.Width, Height:=rng.Height).Object


        For incAddItem = 1 To tot_items
            curCombo.AddItem "Hi"
        Next

        Set customBox(IncCBoxes - DBRigaInizioErrori) = New DB_ComboBox
        Set customBox(IncCBoxes - DBRigaInizioErrori).Box = curCombo
        customBox(IncCBoxes - DBRigaInizioErrori).Line = IncCBoxes


    End Sub

When I run this, I manage to create the first ComboBox (along with its items), but then I get an error "91" --> I verified that the execution stops at the following line

Set customBox(IncCBoxes - DBRigaInizioErrori).Box = curCombo

Any further help would be really appreciated

Thank you in advance

Answer 1

Implicit default member references strike again!

You're missing a Set keyword:

Set customBox(IncCBoxes - DBRigaInizioErrori).Box = curCombo

Without the Set keyword, what you're assigning is .Box.Value on LHS, which can't be right.

But there's another problem.

    Set curCombo = .Shapes.AddFormControl(xlDropDown, _
                                      Left:=rng.Left, _
                                      Top:=rng.Top, _
                                      Width:=rng.Width, _
                                      Height:=rng.Height)

That's not a MSForm.ComboBox control, that's an Excel.ComboBox. You need to add an ActiveX control, not a "form control". This answer covers it.