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!

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!

Works great, thanks!

But if I have multiple buttons, can I reduce the code for each button, such
that it retrieves its own name and calls the insert function with its own
name?
something like:
------------------------------------------------
sub ButtonX

name_selected_button = get name of ButtonX ' the button you just clicked
call insertrowabovebutton(name_selected_button)

end sub
-----------------------------------------------
sub insertrowabovebutton(name_selected_button)

Dim SH As Shape
Dim WS As Worksheet
Set WS = ActiveSheet
Set SH = WS.Shapes(name_selected_button)
WS.Rows(SH.TopLeftCell.Row).Insert

end sub
-----------------------------------------------



"Chip Pearson" wrote:

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!

You can assign the same macro to each button from the Forms toolbar:

Sub FromFormsCommandBar2()
Dim Btn As Button
with ActiveSheet
Set Btn = .Buttons(application.caller)
btn.topleftcell.entirerow.insert
end with
End Sub

Max wrote:

Works great, thanks!

But if I have multiple buttons, can I reduce the code for each button, such
that it retrieves its own name and calls the insert function with its own
name?
something like:
------------------------------------------------
sub ButtonX

name_selected_button = get name of ButtonX ' the button you just clicked
call insertrowabovebutton(name_selected_button)

end sub
-----------------------------------------------
sub insertrowabovebutton(name_selected_button)

Dim SH As Shape
Dim WS As Worksheet
Set WS = ActiveSheet
Set SH = WS.Shapes(name_selected_button)
WS.Rows(SH.TopLeftCell.Row).Insert

end sub
-----------------------------------------------

"Chip Pearson" wrote:

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!


--

Dave Peterson