Multiple combo boxes and event handler class

[John W]

I'm trying to create control Toolbox combo boxes and set up a class to handle events from them. The combo boxes are being created
okay, but the event handler isn't triggered when I click on them.

Sheet1 code:

Option Explicit

Const numComboBoxes = 3
Dim ComboBoxes(numComboBoxes - 1) As New clsComboBox

Public Sub Create_ComboBoxes()

Dim objCB As OLEObject
Dim i As Integer
Dim arrData() As Variant

arrData = Array("AAA", "BBB", "CCC")

'Delete existing combo boxes

For Each objCB In ActiveSheet.OLEObjects
If TypeName(objCB.Object) = "ComboBox" Then
objCB.Delete
End If
Next

For i = 1 To numComboBoxes

ActiveSheet.Cells(1, i * 2 - 1).Select

'Create a Control Toolbox (ActiveX) combo box

Set objCB = Application.ActiveSheet.OLEObjects.Add( _
ClassType:="Forms.ComboBox.1", _
Left:=ActiveCell.Left, Top:=ActiveCell.Top, Width:=50, Height:=20)

objCB.Object.List = arrData
objCB.Name = "myComboBox" & CStr(i)

'Add combo box to array of class event handlers

Set ComboBoxes(i - 1).clsComboBox = objCB.Object

Next

End Sub


Sub Setup_Event_Handlers()

Dim objCB As OLEObject
Dim i As Integer

i = 0
For Each objCB In ActiveSheet.OLEObjects
If TypeOf objCB.Object Is MSForms.ComboBox Then
Set ComboBoxes(i).clsComboBox = objCB.Object
i = i + 1
End If
Next

End Sub


Private Sub CommandButton1_Click()
Create_ComboBoxes
End Sub


Private Sub CommandButton2_Click()
Setup_Event_Handlers
End Sub

==============

Class module called clsComboBox:

Option Explicit

Public WithEvents clsComboBox As MSForms.ComboBox

Private Sub clsComboBox_Change()
MsgBox "Change event " & clsComboBox.Name & " " & clsComboBox.Value
End Sub

==========

Sheet1 contains 2 command buttons (CommandButton1 and CommandButton2).

Clicking CommandButton1 creates the combo boxes and should also create the event handlers, but it doesn't. I have to click
CommandButton2 to set up the event handlers separately and then everything works as expected.

Changing CommandButton1_Click to:

Private Sub CommandButton1_Click()
Create_ComboBoxes
Setup_Event_Handlers
End Sub

Doesn't make any difference. Why is this? How can I set up the class handler for the combo boxes without having to click a
button?

BTW this is Excel 2003.

Thanks.

 

[John W]

I'm trying to create control Toolbox combo boxes and set up a class to handle events from them. The combo boxes are being
created okay, but the event handler isn't triggered when I click on them.

Sheet1 code:

Option Explicit

Const numComboBoxes = 3
Dim ComboBoxes(numComboBoxes - 1) As New clsComboBox

Public Sub Create_ComboBoxes()

Dim objCB As OLEObject
Dim i As Integer
Dim arrData() As Variant

arrData = Array("AAA", "BBB", "CCC")

'Delete existing combo boxes

For Each objCB In ActiveSheet.OLEObjects
If TypeName(objCB.Object) = "ComboBox" Then
objCB.Delete
End If
Next

For i = 1 To numComboBoxes

ActiveSheet.Cells(1, i * 2 - 1).Select

'Create a Control Toolbox (ActiveX) combo box

Set objCB = Application.ActiveSheet.OLEObjects.Add( _
ClassType:="Forms.ComboBox.1", _
Left:=ActiveCell.Left, Top:=ActiveCell.Top, Width:=50, Height:=20)

objCB.Object.List = arrData
objCB.Name = "myComboBox" & CStr(i)

'Add combo box to array of class event handlers

Set ComboBoxes(i - 1).clsComboBox = objCB.Object

Next

End Sub


Sub Setup_Event_Handlers()

Dim objCB As OLEObject
Dim i As Integer

i = 0
For Each objCB In ActiveSheet.OLEObjects
If TypeOf objCB.Object Is MSForms.ComboBox Then
Set ComboBoxes(i).clsComboBox = objCB.Object
i = i + 1
End If
Next

End Sub


Private Sub CommandButton1_Click()
Create_ComboBoxes
End Sub


Private Sub CommandButton2_Click()
Setup_Event_Handlers
End Sub

==============

Class module called clsComboBox:

Option Explicit

Public WithEvents clsComboBox As MSForms.ComboBox

Private Sub clsComboBox_Change()
MsgBox "Change event " & clsComboBox.Name & " " & clsComboBox.Value
End Sub

==========

Sheet1 contains 2 command buttons (CommandButton1 and CommandButton2).

Clicking CommandButton1 creates the combo boxes and should also create the event handlers, but it doesn't. I have to click
CommandButton2 to set up the event handlers separately and then everything works as expected.

Changing CommandButton1_Click to:

Private Sub CommandButton1_Click()
Create_ComboBoxes
Setup_Event_Handlers
End Sub

Doesn't make any difference. Why is this? How can I set up the class handler for the combo boxes without having to click a
button?

BTW this is Excel 2003.

Thanks.

In the ThisWorksheet module, I tried:

Private Sub Workbook_Open()
Sheet1.Create_ComboBoxes
Sheet1.Setup_Event_Handlers
End Sub

but that didn't work.

It seems that clicking the Sheet2 tab and then Sheet1 tab and trapping this via Sheet1's Worksheet_Activate event is one way that
works:

Private Sub Worksheet_Activate()
Setup_Event_Handlers
End Sub


However, I want to set up the generic event handlers for the combo boxes programmatically, not have to click command buttons or
worksheet tabs.

I'd appreciate any help or ideas.

cheers