How to get userform's selected object name/value/caption without handler

[Kalithro]

My google skills seem to be failing me.

I have a userform with 504 command buttons. I don't want a click handler/event for each one. I am able to click and select these objects. Is there a way to get a userform's selected or active object's name/value/caption?

Is there an easy way to dynamically add a click handler to each command button?

[Greg M]

Hi there,

Take a look at the attached workbook and see if it gets you moving in the right direction.

The code works by creating an instance of a common FormButton Class for every CommandButton on the UserForm, and then combining all of those instances in a Collection. The sample UserForm contains only 12 CommandButtons, but could just as easily contain 504 without any code modification being required.

The result of this approach is that clicking on any CommandButton calls a common routine (PerformAction) which receives the source CommandButton as an argument. The PerformAction routine can then take actions based on the (e.g.) Name or Caption of the source CommandButton.

Hope this helps - please let me know how you get on.

Regards,

Greg M

[Norie]

504 command buttons?

[Kalithro]

Excel'ent

Thank you.

Well, for some odd reason with this my userform when clicking command buttons seems to randomly crash Excel.

One instance of Excel it works... another it crashes Excel.

It seems to like working when I open Excel then open the VBA editor then run the macro. If the VBE has not been opened once yet, it crashes Excel. What is up with that?

[mikerickson]

My approach is to pass the Click event back to the user form rather than through another module.

In a class module, clsCommonButton:

Please Login or Register  to view this content.

In the user form's code module.
Note that the command button that is clicked is NOT the ActiveCommonButton. ActiveCommonButton is a clsCommonButton object.
The CommandButton object that was clicked is ActiveCommonButton.CommandButton. (See the ActiveCommonButton_Click event)

Please Login or Register  to view this content.

[Greg M]

Hi again,

Many thanks for your feedback and also for the Reputation increase

It's hard to say exactly what's causing the problem at your end, although I have come across other cases where code run via the VBA Editor works correctly, while the same code run via "native" Excel fails.

As Norie pointed out, 504 CommandButtons on a UserForm seems rather a lot, and maintaining, positioning and "caption-ing" them must be a bit of a nightmare! If such a large number of CommandButtons is actually required it would probably be better to create them at run-time rather than at design-time. If you can post a copy of your UserForm here I can take a look at it to see what might be involved in doing this.

Such a large number (504) of User inputs suggests that each button is used to retrieve a specific piece of information (e.g. date, User name, invoice number etc.) rather than to perform one of 504 different actions - if this is the case, groups of Combo- or ListBoxes might provide a better approach.

Anyway, see what you think of the above comments, and think about posting your UserForm here.



Hi mikerickson: I like your approach of having the Class pass the "source button" information back to the UserForm, but would suggest that this should be extended to include passing the "source button" information back to the routine which created the UserForm, and having that routine take actions based on whichever CommandButton was pressed. I'd welcome your comments on this.

P. S. On further thought, I suppose it depends very much on whether the required action is being performed on the UserForm's own objects, or on objects located outside the UserForm



Best regards to you both,

Greg M

[LJMetzger]

Hi,

My approach is very similar to Greg M's approach. See the attached file which contains static CommandButtons and Dynamic Command Buttons (created at runtime).

Instructions for creating a Class Event Handler:
a. To prevent a compile error, in the VBA Editor add the following library reference:
Tools -> References -> Microsoft Forms 2.0 Object Library (Excel 2003 version)

b. Create Class ClassCommandButton
The following line is required at the top of the Class:
Public WithEvents CommandButtonGroup As MSForms.CommandButton
There are several built in events that can be selected.

c. Put the following line at the top of an ordinary Code module:
Public myCommandButtons() As New ClassCommandButton

d. Put the following calls in UserForm_Initialize:
(1) - Call CreateUserForm1DynamicCommandButtons 'To CREATE Dynamic (created at runtime) controls
(2) - Call GenerateUserForm1CommandButtonControlArray 'To assign CommandButtons to the CommandButtonGroup
Controls in the CommandButtonGroup will have events handled by ClassCommandButton
NOTE: If a UserForm Event exists, it will still be activated before the Class Event.

In Class Module ClassCommandButton:

Please Login or Register  to view this content.

In the UserForm1 code module:

Please Login or Register  to view this content.

In an ordinary code module such as Module1:

Please Login or Register  to view this content.

Lewis

[Kalithro]

I solved the crashing by showing the VBA editor and hiding it.

With "mikerickson" version I don't have the crashes.

I will try "LJMetzger" version when I get a chance to look over it in detail.

Here is what I was working on (I am messing around for experience and realize several better ways of doing this, also I can't have or use add-in packs):

Of course just like last time, soon as I post this online and download it... the crashes start again. A few posts back I removed it (not this time)... sigh. Just open VBE first.

Calendar.xlsm

[mikerickson]

...
Hi mikerickson: I like your approach of having the Class pass the "source button" information back to the UserForm, but would suggest that this should be extended to include passing the "source button" information back to the routine which created the UserForm, and having that routine take actions based on whichever CommandButton was pressed. ...

The Change event is passed back through the ActiveCommonButton (a member of clsCommonButton).
The CommandButton which caused the event to fire is the ActiveCommonButton.CommandButton.
That information is passed to the user form via the ActiveCommonButton variable rather than as an argument of the custom event.

If you do want to pass it explicitly, you could use

Please Login or Register  to view this content.

With code in the user form

Please Login or Register  to view this content.

The ActiveCommonButton_Click event in the user form shows both ways of addressing the clicked command button.

[Greg M]

Hi mikerickson,

Many thanks for taking the trouble to reply to my request. Very interesting and useful comments.



Hi Kalithro,

When I heard of the large number of CommandButtons involved, I suspected that your application might be something like a Calendar Form

The attached workbook shows a version of a similar form which I created some time ago - it uses far fewer CommandButtons, so you might be interested in taking a look at it.

Best regards,

Greg M

[Kalithro]

Thanks everyone. Thanks "LJMetzger" for another great example.

Greg M- Thanks for all the help. I will look deeper into the calendar you sent. I think I will end up doing everything dynamically though (I had started this way with command buttons but didn't know how to add the handlers). The calendar you sent has a lot of nice idea for me to look into. Though some bugs need worked out.

"mikerickson" again, thanks for the help and another great way to dynamically handle command button events.

[Greg M]

Hi again,

Many thanks for your feedback - very pleased to have been able to help.

Regarding

Though some bugs need worked out.

I and several others have been using this form for a long time without any known issues, so PLEASE let me know if you discover something that doesn't work as it should.

Best regards,

Greg M

[pike]

Bit late to the party but
a possible alternative solution

Userform

Please Login or Register  to view this content.

Class clsFrmCtls

Please Login or Register  to view this content.