On Jul 1, 7:27*pm, Geoff wrote:
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

"Geoff" wrote:
Hi
The problem with the last approach is if the user answers vbyes then the
msgbox will be presented a second time.
In addition - if a change has been made to the workbook then the Excel Save
dialog will also be presented.
As the post seems to be suggesting his own alternative to the Excel Save
dialog then one way is:
Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)

* * Dim abcResponse1 As Long
* * Dim cbsState As Boolean

* * '''initial property state
* * cbsState = Application.CalculateBeforeSave

* * abcResponse1 = MsgBox("Do you want to Save the file?", vbYesNo)

* * If abcResponse1 = vbYes Then
* * * * With Application
* * * * * * '''turn off to prevent "Save" dialog
* * * * * * .CalculateBeforeSave = False
* * * * * * '''save data
* * * * * * ActiveWorkbook.Save
* * * * * * '''restore to original
* * * * * * .CalculateBeforeSave = cbsState
* * * * * * '''save restored properties
* * * * * * ActiveWorkbook.Save
* * * * End With
* * ElseIf abcResponse1 = vbNo Then
* * * * ActiveWorkbook.Saved = True
* * Else
* * * * Cancel = True
* * End If

'''other stuff

End Sub

hth

Geoff

"Office_Novice" wrote:

Try this...
Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)
* Dim abcResponse1
* abcResponse1 = MsgBox("Do you want to Save the file?", vbYesNo)

* If abcResponse1 = vbYes Then
* * ActiveWorkbook.Close SaveChanges:=True, Filename:="MyWorkbookName.xls"
* * If abcResponse1 = vbNo Then
* * * ActiveWorkbook.Close SaveChanges:=False
* * End If
* End If
End Sub

"Raj" wrote:

Hi,

In the Before close event of the workbook I have the following code:

If ThisWorkbook.Saved = False Then abcresponse1 = MsgBox("Do you want
to Save the file?", vbYesNo)

What should be the code for abcresponse1 = vbNo so that all changes
made to the workbook since it was last saved are abandoned and the
workbook is saved in that state?

The workbook before close event has code further down the line that
has to be processed.

Thanks in advance for the help.

Regards,
Raj

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