Hi to everybody,
If a create an activex button and I put the caption "Run macro", the properties would be like this:
(Name)=CommandButton1
Caption=Run macro
Then, somebody knows how to obtain the (Name) part using VBA code, I mean, to know if the button
name is CommandButton1 or CommandButton20?
Any help would be appreciated.
Please Login or Register to view this content.
Is this what you're after:
Regards,Please Login or Register to view this content.
Robert
____________________________________________
Please ensure you mark your thread as Solved once it is. Click here to see how
If this post helps, please don't forget to say thanks by clicking the star icon in the bottom left-hand corner of my post
Hello coreytroy,
Thank you for answer.
But actually I have several buttons and I want to know how they are named.
But you're using in your code "CommandButton1" that is the part I want to know, because the buttons could be named as
CommandButton2, CommandButton7, CommandButton15 and CommandButton20 etc.
I want to obtain the Name, not the Caption.
I hope make sense.
Thanks in advance.
------------------------------------------------------------
Hello Trebor,
Thank you, but I got Run-time error 13, type mismatch.
I don't want the Caption that is visible over the button. I want the Name
"CommanButtonX" of each of the activex buttons I have that is not visible.
Thanks again
Last edited by cgkmal; 02-27-2013 at 01:54 AM.
Because you stepped through or just ran the code, it needs to be assigned to and then run from the button.Thank you, but I got Run-time error 13, type mismatch.
application.caller doesn't work for activex controls. perhaps
Please Login or Register to view this content.
Josie
if at first you don't succeed try doing it the way your wife told you to
It worked for meapplication.caller doesn't work for activex controls
I'd like to see that-can you post a workbook?
Hello Joseph,
Thank you for your help!
I've tried your code putting the output in column A of sheet Menu, but only print correctly
the first button name (CommandButton18), but for the others only puts the Caption.
Please take a look in the attachement.
Thanks in advance.
The name and the caption of Option Button 2, Option Button 3 etc are the same.
If posting code please use code tags, see here.
Hello Norie,
If you see properties of each button, you'll see that each button has different
Name. The first is CommandButton18, the 2nd CommandButton1, the 3rd CommandButton2
and so on.
Best regards
Oops, I was looking in the wrong place.
I'm not sure you can access the names. usually the names would be the same as the containing shape but you have changed those (the code is not reading the caption-that just happens to be the same as the shape name). why do you need to access the name instead of the button itself?
Hello Joseph,
I need to get the CommandButtonX, because I need to assign
each button to a macro.
And when you want to assign a macro when you do click over this kind of buttons you need to
put the below code within sheet module where the button is located.
For first command button should be:
Because of this, I want to know the name of each button to generate the code for each buttonPlease Login or Register to view this content.
automatically an put it inside sheet module.
Regards
why do you need to add the code at runtime?
Why don't you just double click the buttons?
That'll generate the code you want.
I only want to generate de "Private Subs" code for each button, because all buttons need to execute the same
macro.
So each "Private Sub CommandButtonX" should go within sheet module, and since there are many buttons I don't want
to do it manually.
I hope make sense.
Thanks in advance.
really? there aren't that many-much simpler to copy and paste then edit the number
I know that could be easier do it manually even that I could have more than 100 buttons, but the issue is
that is needed to delete by macro the buttons, and depending the requeriments, create a number of new buttons,
and this task could be very often.
So, I would like to get automatically the association of those new buttons with the macro desired and put that
code within sheet module.
I hope make sense why I ask to do it by VBA code.
Thanks in advance.
See the attachment
if your code creates the buttons the button name will be the same as the oleobject name so the original code I posted will work
Hello snb,
Exactly! that code gives the name of the buttons!
I know, that is not part of the original question, but if it is possible, how can be generated the "Private Sub CommandButtonX" for each button
and take it inside Sheet module?
Thanks for all help.
snb's code uses the same method I already posted-run it on your workbook and see what results you obtain
Why not use forms controls buttons? You can loop through the sheets buttons collection and assign a macro as a string. Seems much easier
Hello again Joseph,Originally Posted by JosephP
I'm not sure why, but I posted before, only prints the button name only for the first button, for the others prints the caption as below only prints the buttonīs names for all buttons in Inmediate window, but not in worksheet.
This happens in the workbook I uploaded, but run it the same code in the book uploded by snb, works.
In worsheet prints this:
Please try it:Please Login or Register to view this content.
The code is:
Please Login or Register to view this content.Hello Kyle,
I'm using Activex buttons because are needed to use different colors and Form buttons only allowed me to use gray.
If it is possible, only your help to generate the code for each button that will go inside sheet module and put it inside sheet module.
Regards
Last edited by cgkmal; 02-27-2013 at 07:14 PM.
I already told you why. if you run my code on snb's workbook it produces the same results as his and if you run his code on your workbook it produces the same results as mine because the container shapes have been renamed (the names are the same as the captions-the code does not read the captions)
I understand better that part JosephP. Thank you!
Only If it is possible, your help to generate the code for each button that will go inside sheet module and how to
put it inside sheet module.
You have the idea of my goal now, and begin a new thread I'm not sure if it is good idea.
Well, many thanks for all help so far.
To Illustrate JosepfP's point see attached
John
If you have issues with Code I've provided, I appreciate your feedback.
In the event Code provided resolves your issue, please mark your Thread as SOLVED.
If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.
Thank you jaslake for clarificate even more with that example.
The last issue if somebody can help me.
Only If it is possible, your help to generate the code for each button that will go inside sheet module and how to
put it inside sheet module.
Thanks in advance.
Yes, you're absolutely right. I ran my code on a form control command buttonapplication.caller doesn't work for activex controls
I understand your goal but I think it's a really bad idea because if you're adding and removing buttons at runtime you'll have to clean up existing event code as you go. perhaps you could explain the overall purpose and why you aren't using a ribbon menu system for example?
I'm afraid your post does not comply with Rule 8 of our Forum RULES. Do not crosspost your question on multiple forums without including links here to the other threads on other forums.
Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.
Expect cross-posted questions without a link to be closed and a message will be posted by the moderator explaining why. We are here to help so help us to help you!
Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).
Hello Kyle,
As I explained you, I opened a question, somebody was helping me, in the solution he posted was missing a way
to know the name of the buttons. That was the question I did here. And I understand the point, I try to help
others too, not only ask.
Anyway, thank you.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks