Extract ActiveX Form Name

Hi all,

I saw a similar problem solved here, but rather than re-opening a two years old thread I thought of making a new one. So here it is: I have a bunch of ActiveX buttons doing whatever they'r doing in Excel 2007 What I want to do is, within the sub that gets called by say ButtonX, extract the name of that particular button. So if I press ButtonX stuff will happen (stuff that it's not of interest for this problem) and then cell A1 will display the string ButtonX.

I tried using Application.Caller property that was mentioned in the thread I found here, but that doesn't work with my buttons, I get Error 2023 (#REF!) when I try to use that.

Thanks in advance!

Thanks for the reply, shg. I tried your suggestion but I get a "Method or data member not found" error. I guess it should be smth like this though.. When I used Me.Name instead I did get the name of the active worksheet, but when used with ActiveForm I can't get it to work.

You can click the icon for a hyperlink and link the thread in question. Otherwise, posting your question as you did is fine.

I am not sure that we are on the same page. Controls on the Control Toolbox toolbar are ActiveX controls. The method that shg gave would then work.

However, it your buttons are from the Forms toolbar then it would not be an ActiveX control. Application.Caller gives a form's button name. e.g.

Please Login or Register  to view this content.

Thanks Ken for the reply, but still no success. The buttons are for sure ActiveX Controls and not Form Controls. I did create a Form button and test your code and it does work. However, when I use your code for the ActiveX I get #REF! error, and I already said what I get when I use shg's code.

Is it possible that my Excel 2007 doesn't have all components installed and this is what's causing the issue? My version says Microsoft Office Excel 2007 (12.0.4518.1014) MSO (12.0.6020.5000).

I guess I'll try to find a workaround with a little hardcoding. Not the best way, but if I can't get it to work..

anyways, thanks a lot again!

Not sure what you are trying to do. On occassion I write a Sub to take the name of an oleobject or shape and use a Select Case to execute code based on a control type or control name.

e.g.

Please Login or Register  to view this content.

Ok, so here's what I'm trying to do: I have 169 buttons on a worksheet (I know..), each of them calling the same sub, just with a diff set of parameters. These 169 buttons are divided into 3 sets - one having backcolor green, one backcolor red and the third one backcolor blue. When I press one of the buttons, no matter what its default color is, I want it to change to a fourth color, say yellow. So far, so good. But here's the problem - say I press button1 and change its back color from whatever its default is to yellow. Then press Button2. What I want to do is both change Button2's backcolor to yellow AND revert Button1's backcolor to its default, so that it's visible what button was the last one pressed. I know I can use a workaround to display the last button pressed, like display it's name in a visible cell or alike, but I wanted to do it this way. So I thought of doing it like this - if I press Button2 for example I'm checking a cell to see what was the last button pressed before Button2 and revert it's color to the default one, then change Button2's color to yellow, then do some code, then update the cell I mentioned above with the new button's ID, in this case it would be Button2, so that when I press ButtonX I'll first revert Button2's color to its default. So that's why I need the name of the button within the sub the button itself is calling, to use it and store it in that cell. I know I can do it hardcoded - like instead of having Range("A1")=Me.ActiveControl.Name I could just use Range("A1")="Button1", but I was trying to avoid hardcoding it, since there's 169 of them

Anyways, I think I'll go with the hardcode, not that big of a deal after all.

Thanks again for all the replys!

Cheers