Add event handling for combobox created at runtime

[Richard Schollar]

I've been playing around with adding ComboBoxes at run time (eg in response to user actions on a sheet) and this I can easily manage. However, I was wondering how I can then get this newly created combobox itself to respond to user events eg user selecting a value, user clicking out of the combobox etc? Have I missed something simple?

Thanks for all replies!

Richard

 

[Andrew Poulsom]

Here is an example:

Sub Test()
    Dim oole As OLEObject
    Dim CodeMod As Object
    Dim Code As String
    Dim Line As Integer
    Set oole = ActiveSheet.OLEObjects.Add(ClassType:="Forms.ComboBox.1", Link:=False, _
        DisplayAsIcon:=False, Left:=231.75, Top:=54.75, Width:=69.75, Height _
        :=24)
    MsgBox TypeName(oole)
    With oole.Object
        .List = Array("a", "b", "c", "d")
    End With
    Set CodeMod = ThisWorkbook.VBProject.VBComponents(ActiveSheet.CodeName).CodeModule
    With CodeMod
        Line = .CreateEventProc("Change", oole.Name) + 1
        Code = "   MsgBox ""You chose "" & " & oole.Name & ".Value"
        .InsertLines Line, Code
    End With
End Sub

 

[Norie]

Richard

What type of comboboxes are you dealing with?

Perhaps you should look into using a class module to handle the events.

 

[Richard Schollar]

Thanks Andrew - exactly what I was after

Thanks Norie - using class modules is a very good idea, and one which I shall investigate.