About Us

Tueanker

That works beautifully!! Thanks a lot!

"Dave Peterson" wrote:

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

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Need help with BeforeSave event	Chuck M	Excel Programming	4	March 6th 07 02:15 PM
BeforeSave event	Carl Bowman	Excel Discussion (Misc queries)	4	February 6th 05 12:28 PM
BeforeSave event	j23	Excel Programming	0	April 6th 04 11:15 AM
BeforeSave workbook event	Cindy	Excel Programming	15	February 10th 04 04:28 PM
auto-filter change not triggering worksheet_change event	mark	Excel Programming	1	September 19th 03 03:01 PM

Menu

About Us