Prompted to save even after setting wb.Saved=true

[JE]

I would like to avoid being prompted to save the workbook that I am closing
twice.

The problem is this...

I have an add-in that I created that must must execute some custom when any
workbook saves. So in my add-in I handle the
Application_WorkbookBeforeSave. The code looks like this:

--------------------------------------------------------
Private giInSave as boolean

Private Sub moApps_WorkbookBeforeSave(ByVal Wb As Workbook, ByVal SaveAsUI
As Boolean, Cancel As Boolean)
If Not Wb.IsAddin Then
'if already in save then quit this sub
If giInSave Then
Exit Sub
End If

'if workbook is not saved then
If Not Wb.Saved Then
If MsgBox("Would you like to save the formulas without saving the
calculated data for security purposes?", vbYesNo) = vbYes Then
giInSave = True
moApps.Calculate 'recalculate all cells in the app so that
Finance function returns 0 everywhere

Wb.Save 'save the worksheet

giInSave = False
moApps.Calculate 'recalculate the cells to put the numbers back
Wb.Saved = True 'mark the workbook as saved
Cancel = True 'do not execute the save command which normally
would follow this sub because it was done 4 lines up
End If
End If
End If
End Sub
--------------------------------------------------------

This code works perfectly when I save the workbook from toolbar or menus or
keyboard shortcut. It even works properly when I close the Excel
application. However, when I close the workbook...the
Application_WorkbookBeforeClose event is raised (I do not do much in there)
and immediately after the End Sub, I am prompted to Save (I guess this is
from the actual Close event, and if I say yes, then the above event is
raised (WorkbookBeforeSave), which is good. It runs through, but then
immediately after the End Sub I guess the Close event is called again, and I
am reprompted to save!?!?!?!? Even though I just finished setting the
wb.saved=true!?!?!?!?!?

Any ideas?

Thanks.

 

[Dave Peterson]

How about adding another boolean value so you can check to see what happened in
the beforesave routine?



In the ThisWorkbook module (just for completeness):

Dim myBeforeSave As myBfSave
Private Sub Workbook_Open()
Set myBeforeSave = New myBfSave
Set myBeforeSave.moApps = Application
End Sub

In the myFBSave class module:

Option Explicit
Public WithEvents moApps As Application
Private giInSave As Boolean
Dim CancelClose As Boolean

Private Sub moApps_WorkbookBeforeClose(ByVal Wb As Workbook, Cancel As Boolean)
Dim MyCancel As Boolean
Dim curState As Boolean
curState = Wb.Saved
MsgBox "hi"
If curState Then
'do nothing
Else
Call moApps_WorkbookBeforeSave(Wb, False, False)
If CancelClose = True Then
Cancel = True
Wb.Saved = curState
Else
Cancel = False
End If
End If
End Sub

Private Sub moApps_WorkbookBeforeSave(ByVal Wb As Workbook, _
ByVal SaveAsUI As Boolean, Cancel As Boolean)

Dim Resp As Long

If Not Wb.IsAddin Then
'if already in save then quit this sub
If giInSave Then
Exit Sub
End If

'if workbook is not saved then
If Not Wb.Saved Then
Resp = MsgBox("Would you like to save the formulas without" & _
" saving the calculated data for security purposes?", _
vbYesNoCancel)
Select Case Resp
Case Is = vbCancel
CancelClose = True
Case Is = vbYes
giInSave = True
moApps.Calculate
Wb.Save
giInSave = False
moApps.Calculate
Wb.Saved = True
Cancel = True
CancelClose = False
End Select
End If
End If
End Sub

I also changed the yes/no question to yes/no/cancel--just in case they changed
their mind about closing.