Last Saving Date

[Gustavo Strabeli]

Hi!
I have inserted the following code to have Excel showing me the last date
document was saved:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Sheets("Sheet1").Range("A1").Value = Format(Now(), "mmmm dd, yyyy - hh:mm")
End Sub

The point is: I have 2 sheets on the same workbook.
If I make some change on Sheet2, last saved date on Sheet1 is also changed.
Does anyone know how to correct that?

Thanks beforehand,
Gustavo.

 

[Ron de Bruin]

Try this in the Thisworkbook module

Public S1 As Boolean
Public S2 As Boolean

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Sh.Name = "Sheet1" Then S1 = True
If Sh.Name = "Sheet2" Then S2 = True
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If S1 = True Then
Sheets("Sheet1").Range("A1").Value = Format(Now(), "mmmm dd, yyyy - hh:mm")
S1 = False
End If
If S2 = True Then
Sheets("Sheet2").Range("A1").Value = Format(Now(), "mmmm dd, yyyy - hh:mm")
S2 = False
End If
End Sub

 

[Gustavo Strabeli]

Thanks, Ron!
However, when saving, nothing appears in cell A1 on both sheets. Why is that
happening?

Tks.
Gustavo.


Try this in the Thisworkbook module

Public S1 As Boolean
Public S2 As Boolean

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Sh.Name = "Sheet1" Then S1 = True
If Sh.Name = "Sheet2" Then S2 = True
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
If S1 = True Then
Sheets("Sheet1").Range("A1").Value = Format(Now(), "mmmm dd, yyyy -
hh:mm")
S1 = False
End If
If S2 = True Then
Sheets("Sheet2").Range("A1").Value = Format(Now(), "mmmm dd, yyyy -
hh:mm")
S2 = False
End If
End Sub

 

[Ron de Bruin]

Hi

Have you copy this also in the thisworkbook module

Public S1 As Boolean
Public S2 As Boolean

And are your sheet names Sheet1 and Sheet2

 

[Gustavo Strabeli]

Ron,
Please forget it! It's working perfectly.
Thanks ever so much for helping.

Regards,
Gustavo.


Thanks, Ron!
However, when saving, nothing appears in cell A1 on both sheets. Why is that
happening?

Tks.
Gustavo.


Try this in the Thisworkbook module

Public S1 As Boolean
Public S2 As Boolean

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Sh.Name = "Sheet1" Then S1 = True
If Sh.Name = "Sheet2" Then S2 = True
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
If S1 = True Then
Sheets("Sheet1").Range("A1").Value = Format(Now(), "mmmm dd, yyyy -
hh:mm")
S1 = False
End If
If S2 = True Then
Sheets("Sheet2").Range("A1").Value = Format(Now(), "mmmm dd, yyyy -
hh:mm")
S2 = False
End If
End Sub

 

[Gustavo Strabeli]

No, I didin't. And my sheets' names are really Sheet1 and Sheet2.
The I didn't realize is that the "last saving date" only appears when
something is effectively changed.
Now it's perfect.

Thanks again.
Gustavo.


Hi

Have you copy this also in the thisworkbook module

Public S1 As Boolean
Public S2 As Boolean

And are your sheet names Sheet1 and Sheet2