Excel Userform VBA to get the TAG value of a Clicked CommandButton

Hi,

I have a simple Userform with 14 command buttons. Depending what routine and conditions 2 or more Command Buttons will become visible.

I found the class code to that returns the pressed button's Caption and that works, but I need to get the clicked Button's TAG.

I do not want to to add 14 Sub CommandButtonx_Click routines, I just need one common routine that will run one macro that will return the TAG value of the button clicked and the rest of the code.

Please Login or Register  to view this content.

Something similar but to Return the TAG

Thanks in advance

I also posted this on the ozgrid forum: https://www.ozgrid.com/forum/index.p...commandbutton/

Hi there,

Take a look at the attached workbook and see if it does what you need. It uses the following code in a standard VBA CodeModule:

Please Login or Register  to view this content.

The following code in the VBA CodeModule for the UserForm:

Please Login or Register  to view this content.

and the following code in the VBA CodeModule for the CommandButton Class:

Please Login or Register  to view this content.

The highlighted value may be altered to suit your requirements.


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

Regards,

Greg M


P. S. The above routines were written to demonstrate that the Tag property can be both passed to and retrieved from the CommandButton Class. As written, the routine will assign the SAME tag to every CommandButton on the UserForm (which is hardly what you need! ), so you should devise a method for assigning unique Tags to CommandButtons where this is required. It is not impossible that certain CommandButtons should have identical Tag properties in certain situations.

Well it's not really good work Greg, I just post for fun and I like to see people learn who are just starting out and running businesses that actually makes sense instead of people in California writing algorithmic nonsense that's nothing but that's doing nothing but confusing consumers and making people move out of the state LOL.

Thank you for your kind words as well and you've got 10 years on me in terms of excel knowledge. I think we are all continuously learning. I think that will be the case until the cows come home.

Hi Greg, great code. Like you I started long ago, 1995 first macro steps and later when VBA came along ... nothing to stop me, and yes, at 71 I'm still lerning every day.
Thanks for this, I just couldn't figure it out for the TAG but this did it the text for "My Descriptive Tag" is ctl.Tag since I assing a TAG to each of the visible buttons on loading the form and the caption is just a description of the choice for that button.
Thanks a lot, great code, something to add to my VBA library

Greg M; question, how do I pass the result as parameter instead of the message box?
I would like to have a similar option like ActiveControl.Tag ?

This uses a collection, for storage, of a class object to capture the click. Along with a single class to listener and raise the click event.

userform code

Please Login or Register  to view this content.

Class code for CButton

Please Login or Register  to view this content.

class code for Listener

Please Login or Register  to view this content.

Hi Keeballah,

Thanks for your feedback and your comments.

To help me to answer your question in the most efficient way, can you give me some more information about what you're doing with the CommandButtons?

I fully understand why you're using a collection of classes in order to remove the need to write individual (Click etc.) routines for each of the CommandButtons.

Are the CommandButtons themselves being generated from VBA code? If so, their Name and Tag properties will probably have a format similar to "Text prefix_NumericSuffix", and these can be assigned either by the code which creates the CommandButtons or by code within the Class module.

If the CommandButtons are being generated manually, you can probably use more meaningful Name and Tag properties (e.g. "btnStart", btnFinish", btnClose" etc.)

If you want you can combine the above two approaches - e.g. you can "throw" several CommandButtons onto the UserForm. These controls will be assigned default Name properties and NullString Tag properties, but you can then use the first approach to assign "Text prefix_NumericSuffix" values to the Name and Tag properties.


Sorry to answer your question with a question of my own, but you can see why some more information could be useful for me.

Regards,

Greg M


P. S. Sorry, I forgot to say many thanks for the Reputation increase - much appreciated!

@Andy Pope, thanks will take that with me too
@Grem M: here's a sample file with my implementation

Please Login or Register  to view this content.

Hi Greg,
I’m ‘on the road’ so will download and read later and let you know.
Thanks for thinking along
Cheers
Hans

Yep, that's exactly the way it works, instead of the message box I pass the click result to another macro as parameter where the corresponding routine is called and that macro does what it's supposed to do.

Will do, great help, implemented it and it works like a charm.