Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multiple combo boxes and event handler class
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multiple combo boxes and event handler class
"John W" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
multiple combo boxes | Excel Discussion (Misc queries) | |||
Initializing Multiple Combo Boxes | Excel Programming | |||
design one event handler for multiple textboxes | Excel Programming | |||
multiple combo boxes | Excel Discussion (Misc queries) | |||
Linking multiple combo boxes | Excel Programming |