+ Reply to Thread
Results 1 to 17 of 17

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

  1. #1
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2016 (preparing to update to 2021)
    Posts
    7,543

    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/
    ---
    Hans
    "IT" Always crosses your path!
    May the (vba) code be with you... if it isn't; start debugging!
    If you like my answer, Click the * below to say thank-you

  2. #2
    Banned User!
    Join Date
    02-06-2020
    Location
    Iowa City, IA, USA
    MS-Off Ver
    2016 - 365 / 2007
    Posts
    2,014

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

    Since you have not gotten a reply yet Oz grid maybe I will say something here. I don't know what a tag is because I've never heard of that for some reason over 25 years. However is tag a property of a form control? If it is you can simply loop through all of your buttons in the controls collection and return the property by writing control.Tag

    Furthermore I don't really think you need any object orientation or class custom class oriented code to do what you were trying to do before this. Haunted plantation is more in gear geared toward huge applications. But it can be used for small ones too but personally I don't think so in your case.

    Also be very weary of using code off of the internet especially VBA code. a lot of it that is found for free on various websites have been posted on those sites for many many years even decades and some of it is probably wrong and even seriously outdated. I'm just saying...

  3. #3
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2016
    Posts
    3,899

    Re: Excel Userform VBA to get the TAG value of a Clicked 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.
    Attached Files Attached Files
    Last edited by Greg M; 08-17-2020 at 06:46 PM. Reason: P. S. added

  4. #4
    Banned User!
    Join Date
    02-06-2020
    Location
    Iowa City, IA, USA
    MS-Off Ver
    2016 - 365 / 2007
    Posts
    2,014

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

    That's wonderful Greg. Kudos to you :-)

  5. #5
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2016
    Posts
    3,899

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

    Hi there vba_php,

    Many thanks for your comment.

    I've been using Excel for close to thirty-five years and I'm still learning new things about it!

    I've noticed several of your posts in recent days - keep up the good work!

    Regards,

    Greg M

  6. #6
    Banned User!
    Join Date
    02-06-2020
    Location
    Iowa City, IA, USA
    MS-Off Ver
    2016 - 365 / 2007
    Posts
    2,014

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

    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.
    Last edited by vba_php; 08-18-2020 at 02:42 AM. Reason: not a great ender on the post. not really positive.

  7. #7
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2016 (preparing to update to 2021)
    Posts
    7,543

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

    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

  8. #8
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2016 (preparing to update to 2021)
    Posts
    7,543

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

    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 ?

  9. #9
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,098

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

    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.
    Attached Files Attached Files
    Cheers
    Andy
    www.andypope.info

  10. #10
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2016
    Posts
    3,899

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

    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!
    Last edited by Greg M; 08-18-2020 at 04:43 AM. Reason: P. S. added

  11. #11
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2016 (preparing to update to 2021)
    Posts
    7,543

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

    @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.
    Attached Files Attached Files

  12. #12
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2016
    Posts
    3,899

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

    Hi again Keeballah,

    I have something ready to send you, but I need to add a few explanatory notes - should be able to post later today.

    Regards,

    Greg M

  13. #13
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2016
    Posts
    3,899

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

    Hi again Keeballah,

    Take a look at the attached workbook and see what you think.

    I've encapsulated the code as much as possible. This approach ensures that code in a standard CodeModule "knows" nothing about the layout, controls etc. of a UserForm. Similarly (although it doesn't apply in this example), the code in a UserForm Class CodeModule "knows" nothing about workbooks, worksheets, ranges etc.

    Note that the ONLY parameter passed to the UserForm is the number of button rows to be created. Note also that the UserForm does not use an initialisation routine - it waits until it has been "told" how many button rows should be created and creates them only at that stage.

    Also, a reference to the clicked button is passed "all the way back" to the routine which displays the UserForm. At that stage the value of the Tag (or indeed any other) property of the clicked button can be retrieved.


    The workbook 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 values may be altered to suit your own button layout requirements.


    The "ButtonClicked" (Get) property of the UserForm allows it to communicate with the routine which displays the UserForm.

    The "ButtonClicked" (Let) property of the UserForm allows the CommandButton Class Module to communicate with the UserForm.




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

    Regards,

    Greg M
    Attached Files Attached Files

  14. #14
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2016 (preparing to update to 2021)
    Posts
    7,543

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

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

  15. #15
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2016 (preparing to update to 2021)
    Posts
    7,543

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

    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.

  16. #16
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2016
    Posts
    3,899

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

    Hi Hans,

    Many thanks for your feedback - glad I was able to help.

    Feel free to shout if you want further information or suggestions.

    Regards,

    Greg M

  17. #17
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2016 (preparing to update to 2021)
    Posts
    7,543

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

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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. In need of help with Excel VBA userform with 3 combo boxes and a commandbutton
    By RAR1313 in forum Excel Programming / VBA / Macros
    Replies: 18
    Last Post: 02-04-2019, 03:21 AM
  2. [SOLVED] Userform Launch - show userform when any cell in range on any worksheet is double clicked
    By Armitage2k in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 09-27-2016, 03:38 AM
  3. Timer Stop when other CommandButton Clicked
    By iqballud in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-15-2015, 09:23 AM
  4. Determine if Commandbutton is clicked
    By puuts in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-06-2014, 03:10 PM
  5. How Userform Commandbutton actually Save data into excel sheet?
    By z-eighty2 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-21-2014, 11:55 AM
  6. Replies: 4
    Last Post: 11-28-2012, 06:09 PM
  7. Commandbutton in userform to execute code based on a previous commandbutton choice?
    By michaeljoeyeager in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-20-2012, 03:28 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1