Cell reference in Workbook_BeforeSave macro

B

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
 
B

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)
 
B

Bob

Hi, Bob,

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

Well, that and, "Thanks, again!"

Bob
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top