Cell reference in Workbook_BeforeSave macro

[Bob]

I'm writing a macro to update a notification cell (B10) to indicate when the
worksheet has changed. The following code works:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim CurRow As Long: Dim CurColumn As Long
CurRow = Target.Row
CurColumn = Target.Column
Range("B10").Select
ActiveCell.FormulaR1C1 = "As of " & Format(Now(), "general date")
Cells(CurRow, CurColumn).Select
End Sub

After a cell is changed, this code updates the notification cell and then
returns to the cell just changed. This is a workSHEET function, so it fires
after each change to any cell on the sheet. What I'd really like to do is
update the notification only when the user saves the changes to the workbook
(Save, or Close/Save). I found the following workBOOK function that seems
appropriate:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)

If I paste in the code above, I get an "Object Required" error on the line
"CurRow = Target.Row", probably because "Target" is not defined in the Sub
header. If I take out the lines relating to CurRow and CurColumn, the macro
works - it updates the notification cell, but leaves that cell selected.
This may be OK, but is there a way I can return the user to the cell he or
she was in? (Let's say the user makes a change, saves, and wants to
continue working in the sheet.) How can I pass the cell or range to this
macro? Is there a different event I should be using?

Thanks,
Bob

 

[Bob Phillips]

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Dim CurCell As Range
Set CurCell = Activecell
Range("B10").Select
ActiveCell.FormulaR1C1 = "As of " & Format(Now(), "general date")
CurCell.Select
End Sub

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Bob]

Hi, Bob,

Thanks for the very quick reply. Alls I've got to say is, "Duuuhhh...".

Well, that and, "Thanks, again!"

Bob