insert row above button (retrieve position of button)
 

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!

insert row above button (retrieve position of button)
 

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!

insert row above button (retrieve position of button)
 

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!

insert row above button (retrieve position of button)
 

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