Combobox event listener

Question

I'm trying to use the solution given to this issue : Programmatically create event listener in VBA in my particular case :

I programmatically create comboboxes. What I would like to do is to programmatically create a single textbox with a precise label or several texboxes with as many labels next to the generated comboboxes depending on their values.

Here is the code I use to programmatically create those comboboxes :

Set listBoxB1 = Frame1.Controls.Add("Forms.ComboBox.1")

With listBoxB1
       .Name = "list" & i
       .Height = 15
       .Width = 100
       .Left = 70
       .Top = 10 * i * 3
       .AddItem "NUM"
       .AddItem "LIST"
End With

So I want that when the user choses the value LIST, a single textbox is displayed next to the combobox with a label and when he or she uses the value NUM, 6 textboxes are created next to the combobox and aligned with it horizontally.

I actually want that the display changes automatically when the user changes the value of the combobox, it would be helpful if you can give some indications about that.

Can you please tell me how I can adapt the solution in the link above to my case, because I'm very new to VBA and don't exactly know how to do that, I have tried to implement the code given but failed.

`Private Sub listBoxB1_Click()` or `Private Sub listBoxB1_Change()` — Raystafarian, Jul 01 '15 at 12:11
@Raystafarian That sort of thing won't work for controls that are created dynamically. — John Coleman, Jul 01 '15 at 13:27
@john I guess it depends on how "dynamic" they are.. If they are created on the fly, they could be in sequence, or specifically named, or whatever. Sure the events won't be created dynamically, but they should still work. — Raystafarian, Jul 01 '15 at 13:32
@Raystafarian Try it. If the control doesn't exist at design time then the event handler won't fire even if you define it as name_click() where name is the name you assign to the control when you add it. — John Coleman, Jul 01 '15 at 13:37

score 0 · Answer 1 · edited May 23 '17 at 11:43

It really is somewhat involved. This question discusses it: Assign on-click VBA function to a dynamically created button on Excel Userform . The accepted answer contains a trick that I have used many, many times. Create all the controls you will ever need ahead of time (there is only a finite amount of real estate on a form so in practice this will be a reasonably small number) and make them visible (or hide them) dynamically as you need them by controlling the .visible property. It is even possible to have 1 control sit on top of another in the design view with only 1 visible at run time. The visible one at run time will receive the events.

Combobox event listener

1 Answers1