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

Try this

If abcresponse1 = vbNo Then
ThisWorkbook.Saved = True
End If
'do other things


Mike

"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
>

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
>

On Jul 1, 6:36*pm, Mike H <Mi...@discussions.microsoft.com> wrote:
> Try this
>
> If abcresponse1 = vbNo Then
> ThisWorkbook.Saved = True
> End If
> 'do other things
>
> Mike
>
>
>
> "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- Hide quoted text -
>
> - Show quoted text -

Hi Mike,

Thanks for the response,

When we say vbno = Workbook.saved = True, we are telling Excel not to
prompt with "Do you want so save,etc" later.

My problem is this. The code later in the before close event hides all
the sheets in the workbook. Hence the Workbook.saved become false
again. Now I want the workbook with the hidden sheets saved. When I do
this, the data I do not want to be saved also gets saved.

So, what I am looking for is saving the workbook in the state it was
by abandoning all changes. The data is then in the earlier state. Now
when the worksheets are hidden, the saved workbook has the only the
sheets hidden, but the old unchanged data retained as it is.

So basically I am looking for some code which will save the workbook
without the changes when vbno = Yes rather than changing the state of
the Workbook.saved property, as this will soon get changed to false
again soon as described above.

Thanks, once again.

Regards,
Raj

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
> >

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
> > >

On Jul 1, 7:27*pm, Geoff <Ge...@discussions.microsoft.com> 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 youwant
> > > > 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

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 <Ge...@discussions.microsoft.com> 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
>

On Jul 1, 9:38*pm, Geoff <Ge...@discussions.microsoft.com> 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. *Becauseas 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 <Ge...@discussions.microsoft.com> 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 ExcelSave
> > > > 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

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 <Ge...@discussions.microsoft.com> 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 <Ge...@discussions.microsoft.com> 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
>