Creating Userform Controls on the fly.

Herakles

Board Regular
Joined
Jul 5, 2020
Messages
73
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
4,318
Are you saving the workbook after the creation of the combo boxes ?

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

diddi

Well-known Member
Joined
May 20, 2004
Messages
3,337
Office Version
  1. 2010
Platform
  1. Windows
the event handling for dynamic controls must be done in a class module. the control properties work fine, but not events
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,925
Office Version
  1. 365
Platform
  1. Windows
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..

VBA Code:
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!
 
Last edited:

diddi

Well-known Member
Joined
May 20, 2004
Messages
3,337
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,925
Office Version
  1. 365
Platform
  1. Windows
@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

Board Regular
Joined
Jul 5, 2020
Messages
73
Office Version
  1. 365
Platform
  1. Windows
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,151,825
Messages
5,766,661
Members
425,367
Latest member
Boboka

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top