Excel macro/VBA

Hi Terry,

It sounds like it could be an interesting question, but I'm not sure what the question is. When you refer to clicking on an object to initiate a macro, is this a Button or Shape object, or are you really referring to clicking on a range object (cell)? If a cell, is the macro being initiated by the Worksheet_Change event? If a graphic object, are you referring to the cell under the upper left corner of the object? Or perhaps you are referring to a VBA function called by Excel within a cell?

Damon

Hi Damon:
I have a button in a cell and have assigned a macro to it that gets initialized via an object_click invocation. As I stated before the actual cell that is selected does not have to be the same as where the button that is clicked resides so when the macro gets invoked the activecell.row may not be pointing to the row that had the button in it. So the question is, is there a way to determine the row number of where the button resides versus the currently selected cell that activecell.row would provide. I would like the macro to make some decisions based on from what row or column it was invoked, otherwise I have to set some globabl variable and make it unique for each macro invocation and maintain it if rows or columns are added or deleted. Does that help clarify my objective. If there is an alternate way of doing it with other than a button that's fine also, I'm not married to the button idea but thought it would be a nice clean way of making choices in the macro and more or less self-maintaining.

Hi again Terry,

Yes, that clarification helps. Strictly speaking, a button cannot reside "in" a cell, but is on a separate "layer" above the cell. Nevertheless, it is easy to find out which cell the top left corner of the button is in via the button's TopLeftCell property. This property actually yields a range object, so if you want the row number of the cell it is in you would use something like:

Shapes("CommandButton1").TopLeftCell.Row

and a similar use of the Column property would yield the cell's column number.

Does this answer it?

Damon

Hi Damon:
Well the info regarding the shapes being on a separate layer helps but I guess I'm still at a loss. To invoke the macro what I have done is insert some clip art over a cell I guess if its a separate layer and then right click on the clip art and select assign macro which generates a normal _click type event macro sub. What I need to know now is what row was the clip art on that was clicked. So I guess the question is how does the macro find out the clip art's shape name or number in order to do the shapes(num or name).topleftcell.row? Is there something like ActiveShape.topleftcell.row?

Hi Terry,

Naming a Shape (graphic) object is just like naming a cell or range of cells. Just select the picture, then type the name in the Name Box that is just above cell A1. If it is the first picture on the sheet it will have the default name of Picture 1 before you rename it, and you could just use this name.

Regarding something like Activeshapes, shapes do not become activated like cells and worksheets do, but they can be selected, in which case the shape can just be referred to as "Selection" (i.e., row is Selection.TopLeftCell.Row). This is not useful in this case because the shape is assigned to a macro and therefore clicking on it does not select it but rather runs the macro.

Cheers.

Damon Hi Damon:

Hi Damon:
Thanks again for the clarification of shapes, documentation on shapes is pretty scarce if you know of any good sources please let me know, I've been using Excel 2000 Power Programming by Walkenbach and its pretty good. I am afraid I will not be able to accomlish what I started out to do which is to have the approximate location of the shape relative to a row and have the macro use that information to make decisions. I did notice in Walkenback's book a technique of having multiple buttons with one event handler by defining a Class Module and assign the desired button to this Class then when clicked they come to common macro code and then I could use your idea of TopLeftCell.Row to determine which button was pressed and I suppose TopLeftCell.Column to determine the column locatio of the button. I don't know if you could do something similar by defining a Class and then assign the Shapes to the Class. Anyway, thanks for your assistance and creative ideas. Terry Hi Terry, Naming a Shape (graphic) object is just like naming a cell or range of cells. Just select the picture, then type the name in the Name Box that is just above cell A1. If it is the first picture on the sheet it will have the default name of Picture 1 before you rename it, and you could just use this name. Regarding something like Activeshapes, shapes do not become activated like cells and worksheets do, but they can be selected, in which case the shape can just be referred to as "Selection" (i.e., row is Selection.TopLeftCell.Row). This is not useful in this case because the shape is assigned to a macro and therefore clicking on it does not select it but rather runs the macro. Cheers. : Hi Damon:

Hi again again Terry,

Actually, it is quite easy to have a group of shapes all call the same macro. Simply assign them to the same one (right-click -> Assign Macro). If instead of shapes you have ActiveX control CommandButtons, simply call the same macro from each of their Click event routines. There is no need to define a new class. In the common macro called by all the shapes you could put your reference to TopLeftRow, etc. If you want to know which shape the macro was called by, use the Caller method of the Application object, which yields the Name property of the calling shape.

Cheers.

Damon Hi Damon: