Excel 2003 on Windows XP.
I'm creating Control Toolbox combo boxes programmatically on Sheet1 (not on a userform) and also trying to 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 an item in one of the combo boxes.
Sheet1 contains 2 command buttons (CommandButton1 and CommandButton2).
Clicking CommandButton1 creates the combo boxes and should also assign the generic event handler to each combo box, but it doesn't. I have to click CommandButton2 to assign 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.
The only thing that works is clicking CommandButton2.
Another way is by using the following Worksheet_Activate subroutine in Sheet1, however this requires another sheet tab to be clicked and then the Sheet1 tab clicked to trigger it.
Private Sub Worksheet_Activate()
Setup_Event_Handlers
End Sub
I want to set up the generic event handlers for the combo boxes programmatically, without having to click CommandButton2 or worksheet tabs. Any ideas?
Thanks for any help!
I'm creating Control Toolbox combo boxes programmatically on Sheet1 (not on a userform) and also trying to 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 an item in one of the combo boxes.
Sheet1 contains 2 command buttons (CommandButton1 and CommandButton2).
Clicking CommandButton1 creates the combo boxes and should also assign the generic event handler to each combo box, but it doesn't. I have to click CommandButton2 to assign the event handlers separately and then everything works as expected.
Code:
'===== 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
Code:
'===== 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
Changing CommandButton1_Click to:
Private Sub CommandButton1_Click()
Create_ComboBoxes
Setup_Event_Handlers
End Sub
Doesn't make any difference.
The only thing that works is clicking CommandButton2.
Another way is by using the following Worksheet_Activate subroutine in Sheet1, however this requires another sheet tab to be clicked and then the Sheet1 tab clicked to trigger it.
Private Sub Worksheet_Activate()
Setup_Event_Handlers
End Sub
I want to set up the generic event handlers for the combo boxes programmatically, without having to click CommandButton2 or worksheet tabs. Any ideas?
Thanks for any help!