Macro Button Question

[mariat]

Heya All,

I am trying to insert and delete rows using macros. Ive assigned the
Macro to a button, but would like to be able to insert (and delete)
exactly where the button is on the worksheet, no matter where the
activecell is..

So, I guess my question is, is it possible to get the cell reference
behind a graphic button?

Or, even better, assign a macro to a cell?

ta,
Maria

 

[Gord Dibben]

Maria

There are "insert row" and "insert column" buttons available from
Tools>Customize>Commands>Insert

Also "delete row" and "delete column" buttons available from
Tools>Customize>Commands>Edit

Back to your questions...........

How would the button move around the sheet even if you can find out its
position?

AFAIK you can only return the position of the button, not any cell reference
because a button floats and is not attached to a cell.

To have a macro run from a cell you could have Worksheet_BeforeDoubleClick
event code in your worksheet.


Gord Dibben Excel MVP

 

[Zack Barresse]

mariat,

If you are wanting to delete where the button is residing, wouldn't that
delete the button too? Doesn't make much sense to me.. but I have been know
to be slow to catch on..


Gorb,

Not sure why you can't return any cell reference. If we're using an object
from the Shapes collection, we can make use of the TopLeftCell property and
the Application.Caller...

Sub ShowMe()
MsgBox ActiveSheet.Shapes(Application.Caller).TopLeftCell.Address
End Sub

We can even make use of the BottomRightCell property if it spans more than
one cell to get the entire range that it occupies...

Sub ShowMeMore()
Dim strTop As String
Dim strBottom As String
strTop = ActiveSheet.Shapes(Application.Caller).TopLeftCell.Address
strBottom =
ActiveSheet.Shapes(Application.Caller).BottomRightCell.Address
Range(Range(strTop), Range(strBottom)).Select
MsgBox Range(Range(strTop), Range(strBottom)).Address & " selected!"
End Sub

The Shapes collection may not reside "inside" of a cell along with values,
but it does have an association with it which we can use. It is in this
sense that I consider a shapes button 'attached' to a cell.

 

[Gord Dibben]

Thanks Zack

I'll copy this to module and experiment with it.

Looks good.


Gord

 

[mariat]

Hey,

There is an Insert and a Delete button on each row.. does that make
sense - i will also try what you posted too.. thanks..

ta,
maria