The exact method depends on whether the button is from the Forms command bar
or the Controls command bar. Example code for both is shown below:
Sub FromFormsCommandBar()
Dim SH As Shape
Dim WS As Worksheet
Set WS = ActiveSheet
Set SH = WS.Shapes("Button1") '<<< CHANGE NAME
WS.Rows(SH.TopLeftCell.Row).Insert
End Sub
Sub FromControlsCommandBar()
Dim OLEObj As OLEObject
Dim WS As Worksheet
Set WS = ActiveSheet
Set OLEObj = WS.OLEObjects("CommandButton1") '<<< CHANGE NAME
WS.Rows(OLEObj.TopLeftCell.Row).Insert
End Sub
--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)
"Max" wrote in message
...
Hi,
I want to create a simple macro that inserts a row above the button one
clicks. Since the position of this button will change when adding/removing
rows, I can not give a reference where to insert the row, therefore I want
it
to be inserted relative to the position of the commandbutton. Can I get a
reference (rownumber) from such a button, such that I can base the
insertionpoint on this info?
Thanks a lot!