Macro to run in same row as selected button
 

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).TopLeftCel l.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

Macro to run in same row as selected button
 

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).TopLeftCel l.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.

--
Regards,
Tom Ogilvy

"nejl" wrote in message
...
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).TopLeftCel l.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

Macro to run in same row as selected button
 

cheers Tom, thats a great help

--
Message posted from http://www.ExcelForum.com