PDA

View Full Version : can a macro determine button that invoked it?



OlderDaze
12-07-2008, 08:50 PM
I have a macro that performs a sort on a table given the column number to sort on. I assume there is some way that the macro can determine which button invoked the macro. That is what I'm looking for. "Who called me?"

I'm using a simple autoshape as the button. I want to drop the same button at the header to each column and have the macro determine which cell the bottom was in.

If I can get that much information I can also have ascending and descending buttons invoke the same macro and set the appropriate parameter in the sort.

malik641
12-07-2008, 09:12 PM
Hello,

For shape objects, you can use Application.Caller to get the name of the object that called the macro.

And welcome to VBAX!

OlderDaze
12-07-2008, 09:41 PM
I thought it would be something simple. Thanks.
:hi:

malik641
12-07-2008, 09:43 PM
Sure thing! That's one of the only simple thing you'll find with Shapes. I had a lot of frustration working with those things in the past!

OlderDaze
12-07-2008, 11:13 PM
Joseph, I tried a few things with application.caller in the sub that was assigned to a shape. But didnt get what I wanted.
Application.caller by itself returns "Microsoft Excel". Nice but not what I am looking for. I have many copies of the same shape dropped in many different cells. Im trying to get the specific shape object so I can cell that it is tied to.

malik641
12-07-2008, 11:33 PM
Hey,

I'm not sure what's wrong. I just tried it to double-check and here's what I did:

Created a sub in a new module called clicked()
Created an AutoShape (which is different from a shape created from the form toolbox or an OLE shape). Then right-clicked the shape and selected "Assign Macro" then I selected the clicked() macro.

The click() macro looks like:
Public Sub clicked()
MsgBox Application.Caller & " was clicked!"
End Sub


And when I click the AutoShape (rectangle) the code comes back with a message box saying "Rectangle 1 was clicked!".

Maybe you aren't using autoshapes? Could you post the workbook?

I'm starting to remember all the headaches shapes used to bring me..lol don't worry, we'll figure it out, though :)

OlderDaze
12-09-2008, 12:22 AM
Well it must have been too late last night. Tonight I worked up a test sheet with a bunch of copies of a shape that had been assigned to a macro. The assignment copied with each instance. My macro did a quick scan of the shapes collection found the one named the same as application.caller and then was able to get the BottomRightCorner (row and column). I used other shapes and was able to identify the shapes of the button clicked. This gives me exactly what I want in my table sorting macro. Each column header can contain a ascending and descending shapes and I only need one macro to do all of them.

Thanks for staying with me Joseph.
Robin