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

Maria

There are "insert row" and "insert column" buttons available from
ToolsCustomizeCommandsInsert

Also "delete row" and "delete column" buttons available from
ToolsCustomizeCommandsEdit

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

On 30 Nov 2005 13:04:11 -0800, "mariat" wrote:

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

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).BottomRight Cell.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.

--
Regards,
Zack Barresse, aka firefytr, (GT = TFS FF Zack)
To email, remove the NO SPAM. Please keep correspondence to the board, as
to benefit others.


"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
Maria

There are "insert row" and "insert column" buttons available from
ToolsCustomizeCommandsInsert

Also "delete row" and "delete column" buttons available from
ToolsCustomizeCommandsEdit

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

On 30 Nov 2005 13:04:11 -0800, "mariat" wrote:

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

Thanks Zack

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

Looks good.


Gord

On Wed, 30 Nov 2005 16:30:43 -0800, "Zack Barresse"
wrote:

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).BottomRigh tCell.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.

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