Knowing which button was clicked on UserForm?

[Riorin]

I have a user form in Excel that is filled at run time with variable number of CommandButtons named after values in spreadsheet.

So you end up with variable number of buttons named for example "Germany, England, Scotland etc.".

That works fine via Me.Controls.Add method!

NOW, I WANT TO KNOW WHICH BUTTON WAS CLICKED. HOW DO I DO THAT?

Thanks!

[Leith Ross]

Hello Riorin,

When say "user form" what are you referring to, a worksheet with controls on it or a VBA UserForm? If these controls are on a worksheet, are they Forms toolbar or Control Toolbox controls?

Sincerely,
Leith Ross

[Riorin]

When say "user form" what are you referring to,

I'm referring to VBA user form.

Created in Visual Basic Editor by New User Form with all belonging events and properties - I use UserForm_Initialise() to populate the form with dynamic command buttons named after countries currently in a spreadsheet list.

[dominicb]

Good morning Riorin

Use a class module. In the example attached I have populated the userform at design time, rather than run time as you have for the sake of simplicity, but the same principle applies.

HTH

DominicB

[Riorin]

Thanks dominicb!

Thanks a ton! Worked like a charm!

Can't really call it intuitive solution, would have never come up with that on my own!

It was much simpler in Access VBA where you have something like "Show me the control that triggered the event" in form's OnClick event and voila problem solved!