Thread: Class module with ActiveX ComboBox

Hello,

I am trying to use Class modules for my ActiveX ComboBox which are on my sheet.

Here is my problem :
By using the class module, I would like to affect to all of them, the same items and also would like to link to the same Combo_change function.

I searched in the forum and tried but failed with that :

Dim objHandler As ComboPtype
Dim ctl As Object

Set colCheckBoxHandlers = New Collection
With ActiveSheet
    For Each ctl In .OLEObjects
'       ' only interested in checkboxes
      If TypeName(ctl.Object) = "ComboBox" Then
'          ' create a new handler
          Set objHandler = New ComboPtype
          ' assign the checkbox to it
          Set objHandler.Combo = ctl.Object
          ' add the handler to the collection
          colCheckBoxHandlers.Add objHandler
       End If
    Next ctl
End With

I am not familiar with class modules, can someone help me please ?

Thank you in advance

Welcome to the forum.

This is very doable and I'd be more than happy to post some example code, but why don't you use Forms comboboxes instead and assign them all the same macro? This would be simpler and more reliable (no concerns about state loss) to implement and, when embedded on worksheets, Forms controls are less buggy than ActiveX comboboxes.

Hi,

I've attached an example of each.

For the ActiveX example, you have to create an instance of the custom class for each combobox control. The event one would typically use for this is the Workbook_Open() event. Then, in case there's a state loss, the collection of custom class objects is checked each time the worksheet is activated.

ThisWorkbook

Option Explicit

Private Sub Workbook_Open()
    If wstActiveX.Comboboxes Is Nothing Then wstActiveX.SetComboboxes
End Sub

The worksheet's class module

Option Explicit

Private pComboboxes As Collection

Private Sub Worksheet_Activate()
    If pComboboxes Is Nothing Then SetComboboxes
End Sub

Public Property Get Comboboxes()
    Set Comboboxes = pComboboxes
End Property

Public Sub SetComboboxes()
    
    Dim oleObj As OLEObject
    Dim clsCombobox As cCombobox
    
    Set pComboboxes = New Collection
    
    For Each oleObj In Me.OLEObjects
    
        If TypeOf oleObj.Object Is MSForms.Combobox Then
        
            Set clsCombobox = New cCombobox
            Set clsCombobox.Combobox = oleObj.Object
            pComboboxes.Add clsCombobox
        
        End If
        
    Next oleObj
    
End Sub

The cCombobox custom class module:

Option Explicit

Private WithEvents pCombobox As MSForms.Combobox


Private Sub pCombobox_Change()
    
    Dim strMsg As String
    
    strMsg = "You clicked on " & pCombobox.Name & vbNewLine & _
                "You chose " & pCombobox.List(pCombobox.ListIndex)
                
    MsgBox strMsg
    
End Sub


Public Property Set Combobox(ByVal Value As MSForms.Combobox)
    Set pCombobox = Value
End Property

For the Forms example, it's just this piece of code in a standard code module:

Option Explicit

Public Sub DropDown_Click()

    Dim drpCaller As DropDown
    Dim strMsg As String
    
    Set drpCaller = wstForms.DropDowns(Application.Caller)
    
    strMsg = "You clicked on " & drpCaller.Name & vbNewLine & _
                "You chose " & drpCaller.List(drpCaller.ListIndex)
                
    MsgBox strMsg
    
End Sub

or using public variables:

Class ('clsActiveXevents') module:

Public WithEvents mButtons As MSForms.CommandButton

Private Sub mButtons_Click()
 MsgBox "You clicked " & mButtons.Name
End Sub

Thisworkbook module:

Public col_Events As New Collection

Sub Workbook_Open()
 For Each ctl In Sheets(1).OLEObjects
  If LCase(TypeName(ctl.Object)) = "commandbutton" Then
   col_Events.Add New clsActiveXevents
   Set col_Events(col_Events.Count).mButtons = ctl.Object
  End If
 Next
End Sub

@snb
Your code works perfectly but I may be too newbie to understand it.
I have few simple questions for the Thisworkbook module :

ctl : I guess it is the control but why does't it need any declaration ?
Declaration is not needed if it is used in the for loop ? (it takes the OLEObjects type automatically ?)

col_Events.Add New clsActiveXevents
Set col_Events(col_Events.Count).mButtons = ctl.Object

I guess the first line links the object to the class, is it right ?
What does the second one ?

I have tried with combo boxes but when I use the change function, the message appears 4 times, I don't understand why.
By the way, is there a way to populate the combo box using the object ? I don't find "List" for objects

Thank you in advance.

Hello oro77,

Snb is likely asleep now. The variable ctl is automatically declared as a Variant type if there is no Dim statement in the procedure to declare its type. While most people would declare their variable types in the procedure, snb chose not to. It is simply a programming style choice.

The For Each loop requires the control variable (coincidence the variable is named ctl) must either be a Variant type or Object type. The control variable will be assigned the variable type present in the array being looped through. In this case the array is the collection of OLE objects on the worksheet.

@Leith Ross
Thank you very much for your precision. If you have time, could you check the XLS file I uploaded (I have edited my previous post)

edit : I retry with my XLS file and the problem sudenly disappears...

Now I am curious to know how to populate the combo box via the ctl object.

Hello oro77,

はい、もちろん。

Hello oro77,

This took a little longer than I thought. I have commented the code extensively so you can follow what is happening. I hope it is clear. Feel free to ask questions. The attachede workbook has all the changes made to it.

Class Module Code - clsActiveXevents

' Define the object type to create with its events
Private WithEvents mButtons As MSForms.ComboBox

' Hold the object assigned to this instance of mButtons
Private pObj As Object

' Display message when ComboBox cChange event occurs
Private Sub mButtons_Change()
    MsgBox "You clicked " & mButtons.Name & vbCrLf & "Value = " & mButtons.Value
End Sub

' Allow mButtons to be set to another object
Public Property Set ComboBox(ByVal Obj As MSForms.ComboBox)
    Set pObj = Obj
    Set mButtons = Obj
End Property

' Allow mButtons to return the object it is set to
Property Get ComboBox() As Object
  Set ComboBox = pObj
End Property

Module1 - InitComboBoxes

' Public Collection allows objects to accessible to the whole workbook and kept alive until workbook is closed
Public col_Events As Collection


' Set the ComboBoxes on Sheet1 to respond to the custom Change event
Sub InitComboboxes()

  Dim cbo As Object
  Dim ctl As Object
  
    Set col_Events = New Collection
   
    For Each ctl In Worksheets("Sheet1").OLEObjects
      If TypeName(ctl.Object) Like "ComboBox" Then
      ' Create the custom ComboBox object with events to respond to
        Set cbo = New clsActiveXevents
        
      ' Assign an existing ComboBox to the custom ComboBox object
        Set cbo.ComboBox = ctl.Object
        
      ' Save the custom ComboBox in the collection
        col_Events.Add cbo
      End If
    Next ctl
  
End Sub

Workbook_Open() Event

Sub Workbook_Open()
  Call InitComboboxes
End Sub

Worksheet_Activate()

' Restore the ComboBoxes if needed when the sheet becomes active
Private Sub Worksheet_Activate()
  If col_Events Is Nothing Then Call InitComboboxes
End Sub

I amended your posted file.
- not every event can be handled by a class module (don't ask me why)
- the combobox_click event is one of those.
- as you may notive in the file, the combobox_change even can
- I also introduced the .list method to populate the comboboxes.

Special thanks to Leith explaining what the code does (and his assumptions about my behaviour were correct).
As you may notice I try to keep the code as simple as possible.