How do I change a Worksheet_change event to a beforesave event?

[Guest]

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

 

[NickHK]

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

 

[Guest]

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

 

[Guest]

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

 

[Dave Peterson]

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.

 

[Guest]

That works beautifully!! Thanks a lot!

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.