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

Private customBox() As New DB_ComboBox is rather suspicious, and has all ingredients for a nice memory leak. Drop the As New there. Why not just use a Collection? — Mathieu Guindon
– Mathieu Guindon, Commented Nov 28, 2018 at 17:02
+You add a dropdown (form control) instead of an ActiveX control — EvR
– EvR, Commented Nov 28, 2018 at 17:05

Mathieu Guindon · Accepted Answer · 2018-11-28 17:07:15Z

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.

answered Nov 28, 2018 at 17:07

Mathieu Guindon

71.5k8 gold badges113 silver badges250 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Ftoso91 Over a year ago

Thank you, I modified my code but I still have some problems as you can see. Regarding using a Collection instead of an array, I will surely change this if it is necessary, but first I would like to be sure that the remaining code works propoerly (my experience with VBA is very limited, and my experience with VBA Collections is null so far). Thanks also to @EvR

Mathieu Guindon Over a year ago

For object properties, you need a Property Set; property Let is for value assignments, not objects. Also change the type back to MSForm.ComboBox, since you're setting it to OleObject.Object, which is a msforms combobox.

Ftoso91 Over a year ago

Thank you Mathieu, it works now. Thank you for the great explanation

Collectives™ on Stack Overflow

VBA - Handling Events from many Comboboxes, using class

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related