Get Name of Macro Initiator
 

I have many graphical buttons on a sheet with different names/properties but
all initiate the same macro. I need the macro to get the name/properties of
the initiating button.

Is there a "who started me" value I can get?

Thanks,

Get Name of Macro Initiator
 

Assume you mean buttons from forms toolbar.
Sub Button_click()
Dim sName as String, btn as Button
sname = Application.Caller
set btn = Activesheet.Buttons(sname)
msgbox btn.Caption
End Sub

If you mean a picture that looks like a button, the approach would be
similar but you couldn't use the buttons collection

--
Regards,
Tom Ogilvy


"SpaceCamel" wrote:

I have many graphical buttons on a sheet with different names/properties but
all initiate the same macro. I need the macro to get the name/properties of
the initiating button.

Is there a "who started me" value I can get?

Thanks,

Get Name of Macro Initiator
 

Hi Space Camel,

Try sometging like:

'=============
Public Sub Tester()
MsgBox Application.Caller
'Your code
End Sub
'<<=============

---
Regards,
Norman


"SpaceCamel" wrote in message
...
I have many graphical buttons on a sheet with different names/properties
but
all initiate the same macro. I need the macro to get the name/properties
of
the initiating button.

Is there a "who started me" value I can get?

Thanks,

Get Name of Macro Initiator
 

Take a look at

Application.Caller

in XL/VBA Help

In article ,
SpaceCamel wrote:

I have many graphical buttons on a sheet with different names/properties but
all initiate the same macro. I need the macro to get the name/properties of
the initiating button.

Is there a "who started me" value I can get?

Get Name of Macro Initiator - Value of underlaying cell?
 

Just what I was looking For.

Thanks guys.
'--------------------------

One more thing...

I now need to get the value of the cell that the shape is over.
-----
The shapes were added with this code to put them over the correct cell.
With thiscell
With .Parent.Shapes.AddShape( _
Type:=msoShapeRoundedRectangle, _
Left:=.Left + 2, _
Top:=.Top, _

======================================

"Tom Ogilvy" wrote:

Assume you mean buttons from forms toolbar.
Sub Button_click()
Dim sName as String, btn as Button
sname = Application.Caller
set btn = Activesheet.Buttons(sname)
msgbox btn.Caption
End Sub

If you mean a picture that looks like a button, the approach would be
similar but you couldn't use the buttons collection

--
Regards,
Tom Ogilvy


"SpaceCamel" wrote:

I have many graphical buttons on a sheet with different names/properties but
all initiate the same macro. I need the macro to get the name/properties of
the initiating button.

Is there a "who started me" value I can get?

Thanks,

Get Name of Macro Initiator - Value of underlaying cell?
 

Hi Space Camel,

Try something like:


'=============
Public Sub Tester()
Dim Rng As Range
Dim SHP As Shape

Set SHP = ActiveSheet.Shapes(Application.Caller)

Set Rng = SHP.TopLeftCell
MsgBox Rng.Address(0, 0, External:=True)

End Sub
'<<=============


---
Regards,
Norman


"SpaceCamel" wrote in message
...
Just what I was looking For.

Thanks guys.
'--------------------------

One more thing...

I now need to get the value of the cell that the shape is over.
-----
The shapes were added with this code to put them over the correct cell.
With thiscell
With .Parent.Shapes.AddShape( _
Type:=msoShapeRoundedRectangle, _
Left:=.Left + 2, _
Top:=.Top, _

======================================

Get Name of Macro Initiator - Value of underlaying cell?
 

You're putting the shape over ThisCell (with a minor adjustment to the right
(.left + 2)).

I'm gonna bet that you're still over ThisCell.

So you could use
ThisCell.value
to get the value.

SpaceCamel wrote:

Just what I was looking For.

Thanks guys.
'--------------------------

One more thing...

I now need to get the value of the cell that the shape is over.
-----
The shapes were added with this code to put them over the correct cell.
With thiscell
With .Parent.Shapes.AddShape( _
Type:=msoShapeRoundedRectangle, _
Left:=.Left + 2, _
Top:=.Top, _

======================================

"Tom Ogilvy" wrote:

Assume you mean buttons from forms toolbar.
Sub Button_click()
Dim sName as String, btn as Button
sname = Application.Caller
set btn = Activesheet.Buttons(sname)
msgbox btn.Caption
End Sub

If you mean a picture that looks like a button, the approach would be
similar but you couldn't use the buttons collection

--
Regards,
Tom Ogilvy


"SpaceCamel" wrote:

I have many graphical buttons on a sheet with different names/properties but
all initiate the same macro. I need the macro to get the name/properties of
the initiating button.

Is there a "who started me" value I can get?

Thanks,



--

Dave Peterson

Get Name of Macro Initiator - Value of underlaying cell?
 

That did it!

Works great.

Thanks again.
-----------------------------------
"Norman Jones" wrote:

Hi Space Camel,

Try something like:


'=============
Public Sub Tester()
Dim Rng As Range
Dim SHP As Shape

Set SHP = ActiveSheet.Shapes(Application.Caller)

Set Rng = SHP.TopLeftCell
MsgBox Rng.Address(0, 0, External:=True)

End Sub
'<<=============


---
Regards,
Norman


"SpaceCamel" wrote in message
...
Just what I was looking For.

Thanks guys.
'--------------------------

One more thing...

I now need to get the value of the cell that the shape is over.
-----
The shapes were added with this code to put them over the correct cell.
With thiscell
With .Parent.Shapes.AddShape( _
Type:=msoShapeRoundedRectangle, _
Left:=.Left + 2, _
Top:=.Top, _

======================================

Get Name of Macro Initiator - Value of underlaying cell?
 

Consider naming your shapes with the address of the cell they're supposed to
be over.
Then you can just use:

Msgbox Activesheet.Range(Application.Caller).Value

Tim


"SpaceCamel" wrote in message
...
Just what I was looking For.

Thanks guys.
'--------------------------

One more thing...

I now need to get the value of the cell that the shape is over.
-----
The shapes were added with this code to put them over the correct cell.
With thiscell
With .Parent.Shapes.AddShape( _
Type:=msoShapeRoundedRectangle, _
Left:=.Left + 2, _
Top:=.Top, _

======================================

"Tom Ogilvy" wrote:

Assume you mean buttons from forms toolbar.
Sub Button_click()
Dim sName as String, btn as Button
sname = Application.Caller
set btn = Activesheet.Buttons(sname)
msgbox btn.Caption
End Sub

If you mean a picture that looks like a button, the approach would be
similar but you couldn't use the buttons collection

--
Regards,
Tom Ogilvy


"SpaceCamel" wrote:

I have many graphical buttons on a sheet with different
names/properties but
all initiate the same macro. I need the macro to get the
name/properties of
the initiating button.

Is there a "who started me" value I can get?

Thanks,