how to identify the control that ran a macro?
 

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

how to identify the control that ran a macro?
 

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

how to identify the control that ran a macro?
 

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

how to identify the control that ran a macro?
 

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

"Andy Warner"
wrote in message
...

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

how to identify the control that ran a macro?
 

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"
wrote
in message
...

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

how to identify the control that ran a macro?
 

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

the shapes version works a treat.

thanks a bunch.

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