Sub Workbook_BeforeClose

[Img]

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

 

[VoG]

Hello and welcome to MrExcel.

Try

ActiveWorkbook.Save
Application.DisplayAlerts = False
Application.EnableEvents = False
ActiveWorkbook.Close
Application.EnableEvents = True
Application.DisplayAlerts = True

 

[Andrew Poulsom]

The BeforeClose event will fire again when your code closes the workbook. Try:

ActiveWorkbook.Save
Application.DisplayAlerts = False
Application.EnableEvents = False
ActiveWorkbook.Close
Application.DisplayAlerts = True
Application.EnableEvents = True

 

[mrod79]

remove

ActiveWorkbook.Close

think that's the problem.

 

[Img]

Thank You!!

It took you 2 seconds but me all day!!
So I need to turn off the Enable event, ah..... )

Thank you again.