Selecting a row relative to the position of a button

I'm brand new to macros, so I apologize if this is a particularly dumb
question. I'm copying a hidden row and trying to insert it just below
a button on a spreadsheet. The location of the button will be
variable, so I need the row to be inserted relative to the position of
the button. Here's what I have so far:

Sub Button64_Click()
'
'
ActiveSheet.Unprotect Password:="test"
Rows("9").EntireRow.Hidden = False
Rows("9:9").Select
Selection.Copy
Rows("22:22").Select
Selection.Insert Shift:=xlDown
Rows("9").EntireRow.Hidden = True
ActiveSheet.Protect Password:="test"
End Sub

Instead of 'Rows("22:22").Select', I need to select the row immediately
after the button.

I'm sure there's a much more elegant way to do this, so any suggestions
are very welcome!

Thanks so much!

HH,

The ".TopLeftCell" property is what you are looking for.
The Button should be from the Control Toolbox.
The code should be in the sheet module behind the sheet.
Assumes the button is the height of two rows, so to go
below it the insertion is offset by two rows.
Me refers to the sheet.

Jim Cone
San Francisco, USA
'--------------------------
Private Sub Button64_Click()
Application.ScreenUpdating = False
Me.Unprotect Password:="test"
Rows(9).EntireRow.Hidden = False
Rows(9).Copy
Me.Button64.TopLeftCell.EntireRow.Offset(2, 0).Insert shift:=xlDown
Me.Button64.TopLeftCell.Offset(2, 0).Select
Rows(9).EntireRow.Hidden = True
Me.Protect Password:="test"
Application.ScreenUpdating = True
End Sub
'-------------------------------


"HHall" <[email protected]> wrote in message news:[email protected]...
I'm brand new to macros, so I apologize if this is a particularly dumb
question. I'm copying a hidden row and trying to insert it just below
a button on a spreadsheet. The location of the button will be
variable, so I need the row to be inserted relative to the position of
the button. Here's what I have so far:
Sub Button64_Click()'
ActiveSheet.Unprotect Password:="test"
Rows("9").EntireRow.Hidden = False
Rows("9:9").Select
Selection.Copy
Rows("22:22").Select
Selection.Insert Shift:=xlDown
Rows("9").EntireRow.Hidden = True
ActiveSheet.Protect Password:="test"
End Sub

Instead of 'Rows("22:22").Select', I need to select the row immediately
after the button.
I'm sure there's a much more elegant way to do this, so any suggestions
are very welcome!
Thanks so much!

This should work with a button from the Forms tool bar:
Sub Button1_Click()
Dim btnRow As Long

btnRow = Sheet1.Shapes("button 1").BottomRightCell.Row
ActiveSheet.Unprotect Password:="test"
With Rows(9).EntireRow
.Hidden = False
.Copy
End With
Rows(btnRow).Insert Shift:=xlDown
Rows(9).EntireRow.Hidden = True
ActiveSheet.Protect Password:="test"
End Sub

Mike F
"HHall" <[email protected]> wrote in message
news:[email protected]...
> I'm brand new to macros, so I apologize if this is a particularly dumb
> question. I'm copying a hidden row and trying to insert it just below
> a button on a spreadsheet. The location of the button will be
> variable, so I need the row to be inserted relative to the position of
> the button. Here's what I have so far:
>
> Sub Button64_Click()
> '
> '
> ActiveSheet.Unprotect Password:="test"
> Rows("9").EntireRow.Hidden = False
> Rows("9:9").Select
> Selection.Copy
> Rows("22:22").Select
> Selection.Insert Shift:=xlDown
> Rows("9").EntireRow.Hidden = True
> ActiveSheet.Protect Password:="test"
> End Sub
>
> Instead of 'Rows("22:22").Select', I need to select the row immediately
> after the button.
>
> I'm sure there's a much more elegant way to do this, so any suggestions
> are very welcome!
>
> Thanks so much!
>