This goes under the ThisWorkbook module:

Option Explicit
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim rng As Range
Dim c As Range

'be specific about the worksheet
With Me.Worksheets("Sheet999")
Set rng = .Range("J4", .Cells(.Rows.Count, "J").End(xlUp))
End With

On Error GoTo ErrHandler:

Application.EnableEvents = False
For Each c In rng
Select Case LCase(c.Value)
Case Is = LCase("e11 - Document ready")
c.Offset(1997, 27).Value = Date
c.Offset(1997, 27).NumberFormat = "dd-mmm-yy"
c.Offset(1997, 27).EntireColumn.AutoFit

Case Is = LCase("e10 - Approval ongoing")
c.Offset(1997, 26).Value = Date
c.Offset(1997, 26).NumberFormat = "dd-mmm-yy"
c.Offset(1997, 26).EntireColumn.AutoFit

End Select
Next c

ErrHandler:
Application.EnableEvents = True

End Sub

Are you positive that you want to use .offset(1997, x)???? That looks kind of
weird to me.

And I started at J4 and then through to the bottom of column J. You may not
want that.

Tueanker wrote:

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

--

Dave Peterson