Detecting Event on ComboBoxes Added at Runtime on Excel

Question

I have a problem with my VBA script in Excel. What I do is basically creating buttons that when pressed will create a set of two extra comboboxes in one of the sheet. This button can be pressed continuously to add more comboboxes.

These newly created comboboxes will behave like this:

1. Created 2 Combobox
2. Combobox1 will load some list in Control sheet
3. Whenever an item in Combobox1 is selected, Combobox2 will load list of items to be added to Combobox2

The code for adding the button is like this

 Sub Add_Criteria()

       Dim controlNum As Integer
       Dim name1 As String
       Dim name2 As String
       Dim oOle1 As OLEObject
       Dim oOle2 As OLEObject
       Dim uniqueString As String

       Dim cb1 As Object

        controlNum = Sheets("Controls").Range("A16").Value

        'adding Control
        Set oOle1 = Sheets("System").OLEObjects _
            .Add(ClassType:="Forms.ComboBox.1", Left:=10, _
            Top:=75 + (controlNum * 20), Width:=100, Height:=18)
        Set oOle2 = Sheets("System").OLEObjects _
            .Add(ClassType:="Forms.ComboBox.1", Left:=120, _
            Top:=75 + (controlNum * 20), Width:=100, Height:=18)

        'adding properties
        oOle1.Name = "Criteria" & controlNum * 2 - 1
        oOle2.Name = "Criteria" & controlNum * 2

        'adding control var
        Sheets("Controls").Range("A16").Value = controlNum + 1

        With oOle1.Object
           .List = Sheets("Controls").Range("A5:A13").Value
        End With

  End Sub

The question is, I cannot detect events on it. I need to change the value shown on the second combobox created when value in combobox1 changed. I tried to use below reference and I still can't. Can anyone guide me on how to do this

Reference (Been on this Problem for days):

http://www.dbforums.com/microsoft-excel/1641165-detecting-click-event-dynamically-created-controls.html (This is for userform, I don't know why I can't replicate this in Sheet)

Answer 1

Open a fresh Workbook and rename sheets 1 and 2 "System" and "Controls", respectively. Open the VBA Editor and paste YOUR code code above into a general module. Run your code. Return to Excel. (Alt+F11) Right-click on the System sheet tab and select View Code. Paste the following in the module:

Sub FillCombo()
  With Sheets("System").Criteria299 'Change the name of the control as needed.
    .AddItem 1
    .AddItem 2
  End With
End Sub

Private Sub Criteria299_Change()
'Example of triggering the Change Event using Select Case
  With Sheets("System")
    Select Case .Criteria299.Value
        Case 1
           .Criteria300 = "Dog"   'Change the name of the control as needed.
        Case 2
          .Criteria300 = "Cat"
    End Select
 End With
End Sub

Look at Project Explorer and you'll see that the code is in the System worksheet module, not in a general module.

Any event procedures for controls added to a worksheet must be in stored in that worksheet's module.

The FillCombo sub can be put in a general module as long as you refer to the sheet name that has the control as shown.

Answer 2

You can add Events programmatically. The code below adds an event for each combobox

This reference from Pearson Programming The VBA Editor may also be useful.

Sub Add_Criteria()

    Dim controlNum As Integer
    Dim name1 As String
    Dim name2 As String
    Dim oOle1 As OLEObject
    Dim oOle2 As OLEObject
    Dim uniqueString As String
    Dim cb1 As Object
    Dim strCode As String
    Dim vbProj As Object
    Dim vbCodeMod As Object

    Set vbProj = ActiveWorkbook.VBProject
    Set vbCodeMod = vbProj.vbcomponents(ActiveSheet.CodeName).codemodule

    controlNum = Sheets("Controls").Range("A16").Value

    'adding Control
    Set oOle1 = Sheets("System").OLEObjects.Add(ClassType:="Forms.ComboBox.1", Left:=10, Top:=75 + (controlNum * 20), Width:=100, Height:=18)
    Set oOle2 = Sheets("System").OLEObjects.Add(ClassType:="Forms.ComboBox.1", Left:=120, Top:=75 + (controlNum * 20), Width:=100, Height:=18)


    vbCodeMod.AddFromString AddEvent(oOle1.Name)
    vbCodeMod.AddFromString AddEvent(oOle2.Name)


    'adding properties
    oOle1.Name = "Criteria" & controlNum * 2 - 1
    oOle2.Name = "Criteria" & controlNum * 2

    'adding control var
    Sheets("Controls").Range("A16").Value = controlNum + 1

    With oOle1.Object
        .List = Sheets("Controls").Range("A5:A13").Value
    End With

End Sub

Function AddEvent(strIn As String) As String
    AddEvent = "Private Sub " & strIn & "_Click()" & Chr(10) & _
              "MsgBox ""Event Added""" & Chr(10) & _
              "End Sub"
End Function