Hi there
This piece of code runs correctly
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim XCount As Integer
XCount = Application.Sheets.Count
Revised = Format(Now(), "mmm dd, yyyy")
Dim i As Integer
Dim Str As String
i = 1
Str = Sheets(i).Name
Dim Msg, Style, Title, Response
Msg = "Is this a revision from the previous version? If Yes, Last Revised Date will be changed to today's date." ' Define message.
Style = vbYesNo
Title = "Last Revision" ' Define title.
Response = MsgBox(Msg, Style, Title)
If Response = vbYes Then ' User chose Yes.
Sheets(Str).Range("L8").Value = Revised
MsgBox ("The Last Revised Date is now changed to " & Revised)
Else
MsgBox ("GoodBye")
End If
If XCount > 1 Then
For i = i To XCount
Str = Sheets(i).Name
Sheets(Str).Range("L8").Value = Revised
Next i
End If
End Sub
However, the user gets a dialog box to ask whether to save the changes.
which obviously we want to if the revision date is inserted.
But the annoying thing is if I insert before the End Sub line
ActiveWorkbook.Save
Application.DisplayAlerts = False
ActiveWorkbook.Close
Application.DisplayAlerts = True
It runs the Message Box again
"Is this a revision from the previous version? If Yes, Last Revised Date will be changed to today's date."
I have been working all day to get rid of this second time popping up of the Message Box but with no success.
Can you please help me??
Gratefully
This piece of code runs correctly
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim XCount As Integer
XCount = Application.Sheets.Count
Revised = Format(Now(), "mmm dd, yyyy")
Dim i As Integer
Dim Str As String
i = 1
Str = Sheets(i).Name
Dim Msg, Style, Title, Response
Msg = "Is this a revision from the previous version? If Yes, Last Revised Date will be changed to today's date." ' Define message.
Style = vbYesNo
Title = "Last Revision" ' Define title.
Response = MsgBox(Msg, Style, Title)
If Response = vbYes Then ' User chose Yes.
Sheets(Str).Range("L8").Value = Revised
MsgBox ("The Last Revised Date is now changed to " & Revised)
Else
MsgBox ("GoodBye")
End If
If XCount > 1 Then
For i = i To XCount
Str = Sheets(i).Name
Sheets(Str).Range("L8").Value = Revised
Next i
End If
End Sub
However, the user gets a dialog box to ask whether to save the changes.
which obviously we want to if the revision date is inserted.
But the annoying thing is if I insert before the End Sub line
ActiveWorkbook.Save
Application.DisplayAlerts = False
ActiveWorkbook.Close
Application.DisplayAlerts = True
It runs the Message Box again
"Is this a revision from the previous version? If Yes, Last Revised Date will be changed to today's date."
I have been working all day to get rid of this second time popping up of the Message Box but with no success.
Can you please help me??
Gratefully