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