Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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, |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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, |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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, |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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, |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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, _ ====================================== |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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, _ ====================================== |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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, |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro recorded... tabs & file names changed, macro hangs | Excel Worksheet Functions | |||
Macro not showing in Tools/Macro/Macros yet show up when I goto VBA editor | Excel Programming | |||
Need syntax for RUNning a Word macro with an argument, called from an Excel macro | Excel Programming | |||
how to count/sum by function/macro to get the number of record to do copy/paste in macro | Excel Programming | |||
Start Macro / Stop Macro / Restart Macro | Excel Programming |