insert row above button (retrieve position of button)

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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)
 
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
 
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
 
Dave Peterson said:
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


Dave Peterson
Click to expand...


This has been very helpful and very close to what I need. Can this above code be amended to:

1. maintain the formulas and formatting of the 2 rows directly above the forms command button (because each line is filled with a different color) but not the contents?

2. add an InputBox to specify the # of rows to add?

It is important to note that there will be 12 buttons in total (corresponding with the months of the year). The spreadsheet is used to track expenses and customer visits which will vary greatly month to month, so sometimes the month has to be expanded to account for this.
 
Back
Top