Creating Userform Controls on the fly.

[Herakles]

Hi

I'm adding Combobox controls to a Userform upon Activation.

The Change event code is already in place but it does not work with any of the Comboboxes created.

If I create a new Combobox manually and double click on it to access the VBA editor then the link between the control and the code is formed at it works fine.

I thought that just the existance of the Change event code would suffice.

Is there something that I have not done.


Thanks

 

[Logit]

Are you saving the workbook after the creation of the combo boxes ?

You may also need to unload the form then reload it.

 

[diddi]

the event handling for dynamic controls must be done in a class module. the control properties work fine, but not events

 

[Domenic]

If you want to handle multiple controls, you can use a class module, as @diddi has already mentioned.

If you want to handle a single control, you can manually create one, hide it, and then make it visible at runtime when desired.

If for some reason you still want to create the control at runtime, here's an example...

**EDITED**

As @diddi has pointed out, a new combobox will be created each time the userform is activated. So, in order to prevent this from happening, I have amended the code so that the combobox is created within the initialize event instead..

Option Explicit

Dim WithEvents cb As ComboBox

Private Sub cb_Change()
    MsgBox "Hello world!"
End Sub

Private Sub UserForm_Initialize()

    'create new combobox
    Set cb = Me.Controls.Add("Forms.ComboBox.1")

    'set properties
    With cb
        .Left = 10
        .Top = 10
        'etc
        '
        '
        '
    End With

End Sub

You'll notice that the variable cb is declared at the module level using the keyword WithEvents, so that it can respond to events. Then the newly created combobox is assigned to cb. Lastly, the change event is named appropriately.

Hope this helps!

 

[diddi]

as you say domenic, why bother for a just couple of controls. easier to hide them.

@Domenic Would your code end up making a whole heap of comboboxes on the form if you swap windows while it is in use?

 

[Domenic]

@diddi Yes, that's an excellent point. If the userform is simply hidden instead of unloaded, then another combobox would be created when the userform is activated. So yeah, you could inadvertently end up with quite a few comboboxes.

Cheers!

 

[Herakles]

Thanks fo all your responses.

I take on the point about NOT adding the controls from the Activation event.

I'll look at using some of the ideas suggested.