how to identify the control that ran a macro?

[Andy Warner]

Hi all...

Windows XP
Excel 2000 (9)

I have a set of 10 control buttons on a worksheet, named "Button 1" thru "Button 10" all with different text (which happens to be the sheet name of various sheets in the workbook)

I assign the same macro to them all.

I want to know WHICH button called the macro, and then goto a different worksheet depending on the .TEXT of the button that ran the macro

I can do
myString = [Button 1].Text

if I know its Button 1 that ran the macro.

I am hoping there is something like

myString = ActiveButton.Text

The question is.... how can I tell which button ran the macro, and thus the .Text of that button??

Ive searched for all things to do with controls/buttons/macros in several booka dn online - i guess I don't know what exactly to look for!

any help appreciated.
Andy

[Andy Warner]

I've now found

application.caller

which tells me which button ran the macro.

I want the .text of that button. I was hoping:

myString = Application.Caller.Text

or

myButton="["+Application.Caller+"]"
myString = myButton.Text

would work, but they dont. any further???

[Don Guillett]

This might get you started.

Sub Rectangle1_Click()
'MsgBox Application.Caller
With ActiveSheet.Shapes(Application.Caller)
MsgBox .TextFrame.Characters.Text
End With
End Sub

--
Don Guillett
SalesAid Software
dguillett1@austin.rr.com
"Andy Warner" <Andy.Warner.26kbba_1145535603.2153@excelforum-nospam.com>
wrote in message
news:Andy.Warner.26kbba_1145535603.2153@excelforum-nospam.com...
>
> Hi all...
>
> Windows XP
> Excel 2000 (9)
>
> I have a set of 10 control buttons on a worksheet, named "Button 1"
> thru "Button 10" all with different text (which happens to be the sheet
> name of various sheets in the workbook)
>
> I assign the same macro to them all.
>
> I want to know WHICH button called the macro, and then goto a different
> worksheet depending on the .TEXT of the button that ran the macro
>
> I can do
> myString = [Button 1].Text
>
> if I know its Button 1 that ran the macro.
>
> I am hoping there is something like
>
> myString = ActiveButton.Text
>
> The question is.... how can I tell which button ran the macro, and thus
> the .Text of that button??
>
> Ive searched for all things to do with controls/buttons/macros in
> several booka dn online - i guess I don't know what exactly to look
> for!
>
> any help appreciated.
> Andy
>
>
> --
> Andy Warner
> ------------------------------------------------------------------------
> Andy Warner's Profile:
> http://www.excelforum.com/member.php...o&userid=31363
> View this thread: http://www.excelforum.com/showthread...hreadid=534515
>

[DM Unseen]

Something like this should work

Sub Button_Click

Dim wb as workbook
Dim sht as worksheet

set sht = activesheet
set wb = activeworkbook

wb.worksheets(sht.Buttons(Application.Caller).Text).Activate

End Sub

DM Unseen

[Chip Pearson]

Try

Dim BT As Excel.Button
Set BT = ActiveSheet.Buttons(Application.Caller)
MsgBox BT.Text



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Andy Warner"
<Andy.Warner.26kc8n_1145536807.495@excelforum-nospam.com> wrote
in message
news:Andy.Warner.26kc8n_1145536807.495@excelforum-nospam.com...
>
> I've now found
>
> application.caller
>
> which tells me which button ran the macro.
>
> I want the .text of that button. I was hoping:
>
> myString = Application.Caller.Text
>
> or
>
> myButton="["+Application.Caller+"]"
> myString = myButton.Text
>
> would work, but they dont. any further???
>
>
> --
> Andy Warner
> ------------------------------------------------------------------------
> Andy Warner's Profile:
> http://www.excelforum.com/member.php...o&userid=31363
> View this thread:
> http://www.excelforum.com/showthread...hreadid=534515
>

[Andy Warner]

These all work, thanks folks.
I am gradually improving my grasp of VBA thanks to people like you who are happy to spread your knowledge.

best wishes

andy