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???

 

[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

 

[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
(e-mail address removed)
"Andy Warner" <[email protected]>
wrote in message
news:[email protected]...

 

[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"
in message
news:[email protected]...

 

[Andy Warner]

The .Button(application.caller) versions dont want to work - cant find
the button property??

the shapes version works a treat.

thanks a bunch.

andy