Hi

From my understanding the error is thrown due to unspecific reference to
Target
This would work in a worksheet event, but in a workbook event you need to
qualify which worksheet the Target refers to

HTH
Steve

"Tueanker" wrote:

Hi Nick

Thanks for the response. I did as you instructed, but now I get an error
424. When I click debug it refers to the 'If Target.Count 1 Then Exit Sub'
line. Why is that? As a Change event, it worked fine.

Thanks again
KR,
Tue

"NickHK" wrote:

You cannot make up make events and expect Excel to raise them. To make sure
you have the correct signature, let the VBA IDE generate them for you.
Firstly, that event is not raised by the Worksheet, but by the Workbook. So
open the "thisWorkbook" module listed in your project.
From the top-left combo box, select "WorkBook", then from the top-right,
select "BeforeSave".

NickHK

"Tueanker" wrote in message
...
Hello

I have a worksheet with a vba time stamp code (as below). The problem is
that I want the time to be stamped not when the cell changes as it is now,
but when the changes are saved. Is there any way I can do this?

I tried changing the name of the event to Private Sub
Worksheet_BeforeSave(ByVal Target As Range), but it didn't work.

Any help would be greatly appreciated!

My current vba code is:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Dim c As Range

For Each c In ActiveSheet.Range("J4:J1999")
If c = "e11 - Document ready" Then
If Target.Count 1 Then Exit Sub
On Error GoTo ErrHandler
Set rng = Range("J4:J1999")
If Not Intersect(Target, rng) Is Nothing Then
Application.EnableEvents = False
Target.Offset(1997, 27).Value = Date
Target.Offset(1997, 27).NumberFormat = "dd-mmm-yy"
Target.Offset(1997, 27).EntireColumn.AutoFit
End If
End If

If c = "e10 - Approval ongoing" Then
If Target.Count 1 Then Exit Sub
On Error GoTo ErrHandler
Set rng = Range("J4:J1999")
If Not Intersect(Target, rng) Is Nothing Then
Application.EnableEvents = False
Target.Offset(1997, 26).Value = Date
Target.Offset(1997, 26).NumberFormat = "dd-mmm-yy"
Target.Offset(1997, 26).EntireColumn.AutoFit
End If
End If

'etc

ErrHandler:
Application.EnableEvents = True

Next c

End Sub