Identifying the location of a Form Button

[Zaephod]

Hello,

I am looking for some code which will return the location of the form button which is being clicked on to activate the macro. I would like to avoid using an active.x button if at all possible.

In this way I can use the same macro on a column of buttons to trigger an action based on adjacent cell contents.

e.g.

Name Button
Mr a Button1
Mr b Button1
Mr c Button1

The aim is to use the button on an index tab and to capture the name next to the button as a sort string for data on a separate tab.
I am running Excel 2010 on Windows Xp.

Thanks for any tips.

James

 

[Firefly2012]

Hi James

You can use Application.Caller to return the name of the calling shape (as a string) and use that to determine the topleft cell of the calling shape eg:

MsgBox ActiveSheet.Shapes(Application.Caller).TopLeftCell.Address

 

[Zaephod]

Hi, thanks for the quick reply.

Since I will have about 50 of these buttons per sheet I wanted to, if possible, avoid using active x controls. Do you know it there is a way of locating a bog standard form button?

Cheers,
James

 

[RoryA]

Firefly's code is for Forms buttons, not ActiveX.

 

[Zaephod]

Thanks Rorya for the clarification and thanks Firefly for the solution!