my typo - to provide the user with an opportunity to remain, replace this:
abcResponse1 = MsgBox("Do you want to Save the file?", vbYesNo)
with this
abcResponse1 = MsgBox("Do you want to Save the file?", vbYesNoCancel)
hth
Geoff
Hi,
I am posting the code from Thisworkbook module along with comments
explaining what I am trying to do:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
'Line below tests the current saved state and throws up the message
box
If ThisWorkbook.Saved = False Then rspResponse1 = MsgBox("Do you want
to Save the file?", vbYesNo)
'Action not to save the workbook in case the response is vbno
If abcResponse1 = vbNo Then Me.Saved = True
'If response is vbyes and if another conditionis true, show another
message box for completing estimates
If abcResponse1 = vbYes Then If
Worksheets("History").Range("e65536").End(xlUp) <>
Worksheets("Summary").Range("d10") Then rspresponse2 =
MsgBox("Estimates Completed?", vbYesNo)
' If answer to the second message box is yes, take this action:
If abcresponse2 = vbYes Then UnProtectChangeSheet: rspEstHistory:
ProtectChangeSheet: Application.DisplayAlerts = False: If Me.Saved =
False Then Me.Save: Application.DisplayAlerts = True
'IF and answer to the second message box is no, take this action.
If abcresponse2 = vbNo Then Application.EnableEvents = False: Me.Save:
Application.EnableEvents = True
Application.DisplayAlerts = False
'This code from cpearson's site is used for hiding all sheets and
except one to ensure that macros are enabled when the workbook is
opened the next time
SaveStateAndHide
Me.Save
Application.DisplayAlerts = True
Application.EnableEvents = True
End Sub
My problem comes when the SaveStateAandHide Code runs. This code
changes the state of the Workbook.saved to false. So, when the Me.save
at the end runs, the workbook with all changes since the last save is
changed. The code: If abcResponse1 = vbNo Then Me.Saved = True becomes
nullified.
Is there another way to do this?
Thanks in advance, once again, for all the help.
Regards,
Raj