Thread: Class module with ActiveX ComboBox

If I load it from this forum (into the browser, or into Excel) no problem arises.
You can save the file first on your computer/ open Excel/ open the workbook.

code in the class-module

Public WithEvents mCombo As MSForms.ComboBox
Private Sub mCombo_Change()
 MsgBox "You changed " & mCombo.Name
End Sub

code in the Workbook-module

Public col_Events As New Collection
Sub Workbook_Open()
  For Each ctl In Sheets(1).OLEObjects
    If LCase(TypeName(ctl.Object)) = "combobox" Then
      ctl.Object.List = Array("A", "B", "C")
      col_Events.Add New clsActiveXevents
      Set col_Events(col_Events.Count).mCombo = ctl.Object
    End If
  Next
End Sub

Hi oro77

I only included the ActiveX code so you could see how it could be done; I still recommend (as per post #2) that you to use the Forms controls because they are more reliable and (clearly) simpler to implement! Sure, they're lightweight so there are some things you can't do with them that you can do with their ActiveX equivalents but, if you need something fancier than the Forms controls can do, then you should probably be using a userform, not a worksheet.


Btw, you'll notice that the ActiveX code I posted deliberately avoids the use of Public variables in the class modules: I declared them with Private scope and exposed them using properties ('getters', 'letters' and 'setters').



@snb

Originally Posted by snb

- not every event can be handled by a class module (don't ask me why)

Are you referring to AfterUpdate, BeforeUpdate, Enter and Exit?