Cell reference in Workbook_BeforeSave macro
 

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

Cell reference in Workbook_BeforeSave macro
 

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

Cell reference in Workbook_BeforeSave macro
 

Hi, Bob,

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

Well, that and, "Thanks, again!"

Bob

"Bob Phillips" wrote in message
...
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" wrote in message
...
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