Custom Diagloge Before Closing a Workbook *** Help Please ***

[amisagi]

Hi - I am trying to put in a custom dialogue & warning before an user closes the workbook.
The code is working fine in all basic / primary requirements with the exception of a small glitch.

When user tries to close the workbook, the code will give a warning to the user.
If user hits "No" code cancels the workbook close and brings them back to the sheet.
If user hits "Yes" code closes the workbook without saving.

Problem: The warning message comes up twice when user hits "no", i.e. the user has to hit "no" twice.

Any help would be appriciated! Also, if you can suggest a shorter method, most welcome!
Many thanks in advance

Below is my code. Please feel free to as for more details if required.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim Wtxt As String

'suppress alerts
Application.DisplayAlerts = False

'to allow closing without saving changes to workbook
ActiveWorkbook.Close savechanges:=False

'Warning text
Wtxt = "You are closing this file;" & vbCr & _
"All changes shall be removed and file reset to default state." & vbCr & _
& vbCr & "Are you sure?"

Inp = MsgBox(Wtxt, vbQuestion + vbYesNo, "Warning!")

If Inp = vbNo Then
Cancel = True
Application.DisplayAlerts = True
Exit Sub
Else
'functions to clean up the workbook and return it to default state
Call Cleaner
Call Show_Form
End If
End Sub

 

[AlphaFrog]

Change this which initiates the 2nd close...

[color=green]'to allow closing without saving changes to workbook[/color]
ActiveWorkbook.Close savechanges:=[color=darkblue]False[/color]

To this...

[color=green]'to allow closing without saving changes to workbook[/color]
ThisWorkbook.Saved = [color=darkblue]True[/color]

 

[amisagi]

Thanks Alpha...

Meanwhile, i have changed the code a bit and it is now behaving a little differently.
I realized the problem was with the "ActiveWorkbook.Close savechanges:=False" and got rid of it.

You see, i want the code to do something if user hits a "Yes". (the modified code with the "yes" actions have added below)
Now when user hits "no" it does not prompt twice ; however, if user hits "yes"...
1. It runs the actions of deleting the required worksheets, [Expected]
2. saves the file and [Expected]
3. then prompts the custom dialogue box again! [Unexpected]
4. And this time around no matter one hits "yes" or "no" workbook gets closed. [Unexpected]

Any idea why it would do that?

Why cant i do a Debug F8 on this sub?


Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim Wtxt As String
Dim Sh As Worksheet

'suppress alerts
Application.DisplayAlerts = False

'Warning text
Wtxt = "You are closing this file;" & vbCr & _
"All changes shall be removed and file reset to default state." & vbCr & vbCr & _
"Are you sure?"

Inp = MsgBox(Wtxt, vbQuestion + vbYesNo, "Warning!")

If Inp = vbNo Then
ThisWorkbook.Saved = True
Cancel = True
'Exit Sub
Else
For Each Sh In ActiveWorkbook.Worksheets
'delete all sheets except "ShButtons" - worksheet with input userform
If Sh.Name <> "ShButtons" Then Sh.Delete
Next
ThisWorkbook.Save
Me.Close
End If
Application.DisplayAlerts = True
End Sub

 

[AlphaFrog]

Remove this..

Me.Close

It's already going to close. No need to close it a 2nd time.

 

[amisagi]

Ah Ha!!!
Much Appricaited!!

Thank you Alpha!

 

[AlphaFrog]

You're welcome. Thanks for the feedback.