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!

Selecting a row relative to the position of a button
 

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" wrote in message ups.com...
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!

Selecting a row relative to the position of a button
 

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" wrote in message
ups.com...
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!