Macro to run in same row as selected button

[nejl]

Hi, below I have posted my code which is designed to insert a copied row in
whichever row a button is when it is clicked. What am I doing wrong? it seems
to get as far as the 'Cells(MyRowToUse, 1).Select' line of code.

I am a beginer so suspect it may be something simple but help would be
appreciated.

N

Sub NEWRMO()
'
' NEWRMO Macro
' Macro recorded 02/09/2004 by charles
'

'
Dim BTN As Button
Dim MyRowToUse As Variant


MyRowToUse = ActiveSheet.Buttons(Application.Caller).TopLeftCell.Address
'.row, .column


MsgBox MyRowToUse



Sheets("Template").Select
Rows("11:11").EntireRow.Copy
Sheets("Risk Register").Select
Cells(MyRowToUse, 1).Select
ActiveCell.Offset(0, 0).Rows("1:1").EntireRow.Select
Selection.Insert Shift:=xlDown

End Sub

 

[Tom Ogilvy]

myrowtouse will have a value like B21


Sub NEWRMO()
'
' NEWRMO Macro
' Macro recorded 02/09/2004 by charles
'

'
Dim BTN As Button
Dim MyRowToUse As Variant
MyRowToUse = ActiveSheet.Buttons(Application.Caller).TopLeftCell.row
'.row, .column
MsgBox MyRowToUse
Sheets("Template").Rows("11:11").EntireRow.Copy
Sheets("Risk Register").Cells( _
MyRowToUse, 1).Insert Shift:=xlShiftDown
End Sub

This shifts you button down.

 

[nejlangton]

cheers Tom, thats a great help