insert row above button (retrieve position of button)

[Max]

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!

 

[Chip Pearson]

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]

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

 

[Dave Peterson]

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