Creating combo boxes and assigning generic event handler
Results 1 to 8 of 8

Thread: Creating combo boxes and assigning generic event handler
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Oct 2007
    Posts
    5,807
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    2 Thread(s)

    Default Creating combo boxes and assigning generic event handler

    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!

  2. #2
    MrExcel MVP erik.van.geit's Avatar
    Join Date
    Feb 2003
    Location
    Belgium 3272 Testelt
    Posts
    17,832
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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
    I love Jesus

    email Erik

    founder of DRAFT

    my free Addins
    Table-It download & info
    Formula Translator 04

  3. #3
    Board Regular
    Join Date
    Oct 2007
    Posts
    5,807
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    2 Thread(s)

    Default 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

  4. #4
    MrExcel MVP
    Moderator
    Inactive
    Richard Schollar's Avatar
    Join Date
    Apr 2005
    Location
    UK
    Posts
    23,703
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default

    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
    Richard Schollar

    Using xl2013

  5. #5
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  6. #6
    Board Regular
    Join Date
    Oct 2007
    Posts
    5,807
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    2 Thread(s)

    Default

    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,

  7. #7
    Board Regular
    Join Date
    Oct 2007
    Posts
    5,807
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    2 Thread(s)

    Default

    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().

  8. #8
    Board Regular
    Join Date
    Oct 2007
    Posts
    5,807
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    2 Thread(s)

    Default

    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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •