Creating combo boxes and assigning generic event handler

[John_w]

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.

'===== 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

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!

 

[erik.van.geit]

Hi, John_w
Welcome to the Board!!!!!


I had to dig deeply in the archives to find something similar. See if this can clarify the problem. Please take your time to compare the problems and for some trial and error.
http://www.mrexcel.com/board2/viewtopic.php?t=169025

kind regards,
Erik

 

[John_w]

Success!

Hi Erik,

Thanks for your reply. The problem in that thread is slightly different. I'm using the same technique as in create_combobox2() to create and fill the combo boxes in one subroutine, however it gave me the idea of using Application.OnTime to call a subroutine which assigns the event handler class to the combo boxes, and it all works!

Here is the modified Sheet1 code:

Option Explicit


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" And InStr(objCB.Name, ComboBoxPrefix) = 1 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 = ComboBoxPrefix & CStr(i)
    
    'Clear and assign event handler class for this combo box
        
    Set ComboBoxes(i - 1).clsComboBox = Nothing
    Application.OnTime Now + TimeSerial(0, 0, 1), _
        "'Set_Class_This_ComboBox """ & objCB.Name & """," & i - 1 & "'"

Next

'Or comment out previous 2 statements and assign event handler class for all the combo boxes in one go
'Note: if Create_ComboBoxes is called from Workbook_Open, a 2 seconds delay (rather than 1)
'seems to be necessary for the combo boxes to be named properly

'Application.OnTime Now + TimeSerial(0, 0, 1), "'Set_Class_All_ComboBoxes'"

End Sub

I added a new module, Module1, containing global constants and variables and the subroutines called by Application.OnTime:

Option Explicit

'Globals

Public Const ComboBoxPrefix As String = "MyComboBox"
Public Const NumComboBoxes As Integer = 3

Public ComboBoxes(NumComboBoxes - 1) As New clsComboBox


Public Sub Set_Class_This_ComboBox(sComboBoxName As String, iComboBoxNumber As Integer)

Dim objCB As OLEObject

Set objCB = ActiveSheet.OLEObjects(sComboBoxName)
Set ComboBoxes(iComboBoxNumber).clsComboBox = objCB.Object
    
End Sub


Public Sub Set_Class_All_ComboBoxes()

Dim objCB As OLEObject
Dim i As Integer

i = 0
For Each objCB In ActiveSheet.OLEObjects
    If TypeName(objCB.Object) = "ComboBox" And InStr(objCB.Name, ComboBoxPrefix) = 1 Then
        Set ComboBoxes(i).clsComboBox = objCB.Object
        i = i + 1
    End If
Next
    
End Sub

 

[Richard Schollar]

Hi John

You'll have to excuse my laziness as I haven't fully read your code, but the question title sounds exactly like something I asked in the forum a short while ago, and Andrew Poulsom helped me out. Here it is:

http://www.mrexcel.com/board2/viewtopic.php?t=293042

I hope it can help you too

 

[Legacy 98055]

Yeah. Interesting. They - Microsoft - never did get around to allowing you to add activeX objects without losing state. The reason being is that you are actually going in and out of design mode. Hence the need to use app ontime. Note that any variables you may have floating around will lose their assignments when you run this. The workaound is to store them in a container that does not suffer from state loss such as a workbook name or cell.

 

[John_w]

Richard,

Thanks for the link. I had awful problems getting Andrew Poulsom's code to run without error. I eventually got it to work as follows: Excel Tools menu - Macros - Security - Trusted Publishers tab - tick 'Trust access to Visual Basic Project'.

His technique is different to mine in that it creates a specific named event subroutine for each combo box. The first run of Test() creates ComboBox1_Change() and a second run would create ComboBox2_Change(), etc.

My technique uses a class module and the Change event for all combo boxes whose name starts with 'MyComboBox' is handled by a single subroutine, clsComboBox_Change().

cheers,

 

[John_w]

Note that any variables you may have floating around will lose their assignments when you run this. The workaound is to store them in a container that does not suffer from state loss such as a workbook name or cell.

Is that the reason why, if I change Sheet1 code to assign the class to all combo boxes in one Application.OnTime call as follows:

Option Explicit


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" And InStr(objCB.Name, ComboBoxPrefix) = 1 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 = ComboBoxPrefix & CStr(i)
    
Next

Application.OnTime Now + TimeSerial(0, 0, 1), "'Set_Class_All_ComboBoxes'"

End Sub

and call it from ThisWorkbook via Workbook_Open:

Option Explicit

Private Sub Workbook_Open()
    Sheet1.Create_ComboBoxes
End Sub

one of the combo boxes (as displayed by clsComboBox_Change()) is sometimes named 'ComboBox2' instead of 'MyComboBox2'? Hence my comment about making the time delay 2 seconds rather than 1, which seemed to resolve this issue. Despite the name being wrong, the Change event is still correctly triggered.

Edit - this issue of the combo boxes not being correctly named has also occurred occasionally with the previous Create_ComboBoxes().

 

[John_w]

NB - I've discovered that the class event handler is unassigned if you click Design Mode on the Control Toolbox toolbar. Is there any way it can be reinstated automatically?

cheers