To complete the thread this should work:

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Dim abcResponse1 As Long
Dim abcResponse2 As Long

If Me.Saved = False Then abcResponse1 = MsgBox("Do you want to Save the
file? ", vbYesNoCancel)

If abcResponse1 = vbNo Then
SaveStateAndHide
Me.Saved = True
ElseIf abcResponse1 = vbYes Then
If Worksheets("History2").Cells(Rows.Count, "E").End(xlUp) <
Worksheets("Summary").Range("d10") Then
abcResponse2 = MsgBox("Estimates Completed? ", vbYesNoCancel)
If abcResponse2 = vbYes Then
UnProtectChangeSheet
rspEstHistory
ProtectChangeSheet
End If
End If
SaveStateAndHide
Me.Save
End If

If abcResponse1 = vbCancel Or abcResponse2 = vbCancel Then Cancel = True

End Sub

hth

Geoff

"Geoff" wrote:

Raj
I appreciate I do not know the scenario in which you are working with this
wbook but I do not understand why:
1. you wish to close the wbook without giving the user a chance to remain
2. you want to Save and close the wbook regardless of the users response to
question 2 about the Estimates

I have sent an email for you to consider.

Geoff

"Geoff" wrote:

Thanks for the link. I will take a look but it will be later before I can
get back.

Geoff

"Raj" wrote:

On Jul 1, 9:38 pm, Geoff wrote:
Raj
Can you provide a link to the site, I have looked but cannot find anything
to reference SaveStateAndHide
In the meantime refer to responses to your earlier posts on similar topics.
1. If you place a Save event inside the Workbook_BeforeClose it will run a
second time from a vbyes response.
2. Your latest code includes a Me.Save statement. The Save event will cause
a wbook recalculation if any changes have been made. This will result in the
Excel Save dialog which will appear whether or not you have set DisplayAlerts
to False or not. The only way I'm aware of preventing this is to set
"CalculateBeforeSave" to off. But this a "persistent" setting as well as an
Application wide setting. This explains why the code reads the user options
setting for it but sets it to off immediately before the Save. Because as I
have said it is a "persistent" setting then you have to reset it to the user
prefernce AND save a second time.
Please try to adapt my suggestion and I will have a look at the link if you
can provide that for me.

hth

Geoff

"Raj" wrote:
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

Geoff,

Thanks for your response.The url:
http://www.cpearson.com/excel/EnableMacros.aspx

A little about the problem. This workbook contains data that is
reviewed. The reviewer may make some changes but may not want to
retain the changes. When the reviewer closes the workbook, he is asked
whether he wants to save. If he says no, the workbook should not be
saved. If he says Yes, then some more processing is done (like
recordng the task completion in a worksheet, etc) . In both cases (Yes
and No) , the SaveStateandHide Macro (url above) is used to hide all
unhidden sheets except a sheet that warns the user to open the
workbook with macros enabled. There is another macro in the Workbook
open event (same url above) that unhides sheets except the warning
sheet when the Workbook is opened with macros enabled. The problem I
am facing is in abandoning the changes made by the reviewer as well as
hiding the sheets at closing of the workbook.

Any alternate solution/workaround for achieving this is also welcome.

Thanks in advance.

Regards,
Raj