Creating combo boxes and assigning generic event handler

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,363
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.

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!
 

Some videos you may like

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,363
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:

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:

Code:
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
 

Tom Schreiner

Well-known Member
Joined
Mar 18, 2002
Messages
6,867

ADVERTISEMENT

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

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,363
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

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,363
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:

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)
    
Next

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

End Sub
and call it from ThisWorkbook via Workbook_Open:
Code:
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

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,363
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
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,316
Messages
5,510,554
Members
408,798
Latest member
jitu20feb

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top