Solved: Can't suppress "Do you want to save..."

[losinj]

Hi, this is my 1st post. I've checked several previous threads but am still at a loss to understand my problem.

I'm getting repeated Excel "Do you want to save the changes..." propmpts when closing the workbook, although the following (stripped down) code works ok when saving:


Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
'
' Code to check conditions
'
If Not Conditions_Met Then
If MsgBox("... Do you still wish to save?", vbYesNo) = vbNo Then
Cancel = True
Exit Sub
End If
End If
'
' Code for saving
'
End Sub

When closing I get the required "Do you want to save..." prompt as expected - no problem. If I click "Yes" the BeforeSave event fires and my code above runs. If I then select "No" to abort the save, I'd like the save to be cancelled and the workbook closed. Instead I get another "Do you want to save..." prompt.

I tried putting
Me.Saved = True
before the Exit Sub but it made no difference. (It would, however, cause Excel to assume changes had been saved before closing, if a previous save was aborted. I don't really want this.)

Do I need to set a variable in the BeforeClose sub and somehow use that in the BeforeSave sub?

I'm using Excel 2003 SP2.

Thanks for any help.

[Slyboots]

Workbooks("MyWorkbook.xls").Close SaveChanges:=False

or

Workbooks("MyWorkbook.xls").Close False

S

[losinj]

Thanks, S.

I don't want to close unconditionally, as I could be saving and not closing, so I ended up doing this in Workbook_BeforeSave (some of the extra code is courtesy of Ken Puls, w w w dot excelguru dot ca, included hopefully for clarity):


Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
'
' Code to check conditions
'
If Not Conditions_Met Then
If MsgBox("... Do you still wish to save?", vbYesNo) = vbNo Then
If bClosing Then ThisWorkbook.Close SaveChanges:=False
Cancel = True
Exit Sub
End If
End If
'Turn off screen flashing
With Application
.EnableEvents = False
.ScreenUpdating = False
End With
'Record active worksheet
Set wsActive = ActiveSheet
'Save workbook directly or prompt for SaveAs filename
If SaveAsUI = True Then
vFilename = Application.GetSaveAsFilename( _
fileFilter:="Excel Files (*.xls), *.xls")
If CStr(vFilename) = "False" Then
bSaved = False
Else
'Save the workbook using the supplied filename
Call HideAllSheets
ThisWorkbook.SaveAs vFilename
Application.RecentFiles.Add vFilename
Call ShowAllSheets
bSaved = True
End If
Else
'Save the workbook
Call HideAllSheets
ThisWorkbook.Save
Call ShowAllSheets
bSaved = True
End If
'Restore file to where user was
wsActive.Activate
'Restore screen updates
With Application
.ScreenUpdating = True
.EnableEvents = True
End With

'Set application states appropriately
If bClosing Then
ThisWorkbook.Close SaveChanges:=False
Else
If bSaved Then ThisWorkbook.Saved = True
Cancel = True
End If
End Sub

and this in Workbook_BeforeClose:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
'
bClosing = True
'
'

(bClosing is defined at the top of ThisWorkBook module).
The only niggle is that closing the workbook within the BeforeSave sub appears to cause Excel to report a problem and that it needs to close. Not a great problem in itself, but now looks messy. (This appears to be a known bug in MS Office, but as all updates are provided centrally by the company I can't just go and get the update from Microsoft.) Any ideas on whether that can be avoided by some code changes?

Thanks

[GTO]

Are you wishing to, in essence, create an 'after_save' event, so that you can keep certain sheets hidden, in the event that the user opens the wb w/macros disabled, they will remain hidden?

[losinj]

Yes, GTO. Much of "my" code is to make sure that only a single "Please enable macros" sheet is visible if the user does not enable macros. That was already working fine when saving normally, but then I realised it was getting into a loop and prompting repeatedley to save any changes when closing the workbook. It seemed to be down to my code in the BeforeSave sub which requests additional confirmation before saving changes if certain conditions are not met, and I couldn't figure out why.

I've now got two occurrences ofThisWorkbook.Close SaveChanges:=Falseto close the workbook if the Workbook_BeforeClose event is fired first, but this now appears to cause the aforementioned crash in Excel :banghead:

[GTO]

Can you wait until tomorrow? It is close to 5:00 AM here in Arizona,USA, and I should hit the rack.

[losinj]

Of course, GTO, no probs. Have a good kip. Ah, you're asleep ;-)

[CreganTur]

What you want is:
ActiveWorkbook.Saved = True

this sets the save flag to True so that Excel believes that your workbook is not dirty. Therefore, the Save As window won't launch.

Just make sure that this is the last line of code VBA encounters before the workbook closes! If you use the above, and then make a change to the workbook you'llbe prompted to save again because the workbook is dirty again.

[losinj]

Is that the same as havingMe.Saved = Truein the workbook's private module? If so it didn't seem to stop the prompt the last time I tried it. I'll have another go.

[CreganTur]

Nope, it's not the same thing at all.

'Me' is just a shorthand keyword for referring to the object that code is behind- pretty much you only encounter it when working with UserForms.

ActiveWorkbook is a keyword that refers to the currently active workbook that the code is in.

[malik641]

Is that the same as havingMe.Saved = Truein the workbook's private module? If so it didn't seem to stop the prompt the last time I tried it. I'll have another go. Yes it is, but only if it is in the ThisWorkbook module. Not just any module.

CreganTur is correct about it being a shorthand for where the code is behind and it is only applicable to Class modules which include: ThisWorkbook, Worksheet, Userform modules, and (obviously) Class Modules.

EDIT: I believe I should have said "Sheet" modules (to include Worksheets and Charts)

[CreganTur]

Yes it is, but only if it is in the ThisWorkbook module. Not just any module.

CreganTur is correct about it being a shorthand for where the code is behind and it is only applicable to Class modules which include: ThisWorkbook, Worksheet, Userform modules, and (obviously) Class Modules.

EDIT: I believe I should have said "Sheet" modules (to include Worksheets and Charts)

Thanks for the clarification- I'm more familiar with Access so whenver I see 'Me' I automatically think Form :razz:

[malik641]

Sure thing. I don't think many people know that class modules included Sheets and the ThisWorkbook module. It's not that important, but an interesting fact (for the nerds..like me :))

[GTO]

losinj - Please check your PMs when you get a chance.

Thx - Mark

[losinj]

Many thanks for the tips, guys. Not had a chance to look at this today, had to do the normal, boring work. Will report back.

[losinj]

Sorry, I still can't fathom this.

Watching these bits of the code as I step through, the first sub to be entered when clicking 'X' is Workbook_BeforeClose. When that ends a prompt to save changes appears, and I click "Yes".
Workbook_BeforeSave now runs, and just as it ends I can see that 'ThisWorkbook.Saved' and 'Cancel' are definitely both 'True'.
Why, then, is the very next thing another prompt to save changes? When BeforeSave ends the workbook should close naturally, should it not? I tried substituting 'Me.Saved' and 'ActiveWorkBook.Saved' but no difference. Please tell me I'm missing something - apart from my marbles.

Dim bClosing As Boolean

Private Sub Workbook_BeforeClose(Cancel As Boolean)
bClosing = True
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
'
Dim bSaved As Boolean
'
'
'
If bClosing Or bSaved Then
ThisWorkbook.Saved = True
End If
Cancel = True
End Sub

(For the moment I've removed any statements which forcibly close the workbook from within BeforeSave, as these seem to cause the crash I was getting previously.)

[CreganTur]

You need to use:
ActiveWorkbook.Saved = True

Not:
ThisWorkbook.Saved = True

[GTO]

Hi Colin,

See attached. While originally developed a couple of years ago, I updated w/handling for SaveAs after your advisement as to your source and reviewing that.

Anyways - ours already had BeforeClose events handled, which you referenced as problematic. (I duplicated and observed the same loop, as to the repeated "do you want to save...?")

Also handled are a couple of issues we had run into, such as that when a "manual" save is executed (that is, the Save icon is pressed, or Ctrl+s) it would work fine, but if the Save was executed programatically (that is, ThisWorkbook.Save or similar is run), then the file would not actually save!

This and a couple of errors encountered occassionally are addressed and commented on in the example.

Hope this helps! I dread saying this, but so far, I haven't found a way the end user could easily get around it :-)

Mark

[losinj]

Well, Mark, that did the trick. Very smooth - I hadn't thought to handle saves from within BeforeClose, but that seems the thing to do under the circumstances.

Many thanks :thumb

[GTO]

You are very welcome and glad to help. :beerchug: