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.

 

[Sharad Naik]

Once you click on close the workbook and
excel dialogue box appears, all the code that is excecuted is AFTER
workbook_close (not before).
and then setting Wb.Saved=True does not have effect.

So seems like you need to write code in BeforeClose event too.

In Thisworkbook class in General, in addition to defining
giInSave, you can add another variable say giInClose as boolean.

Then is the beforeclose event add a single line
giInClose = True

In your BeforeSave event, just after Wb.Save 'save the worksheet
(workbook) line
add below code
If giInclose Then
giInClose = False
Exit Sub
Exit Sub

This should solve it I think, Try it.

Sharad

 

[Sharad Naik]

Just realized that, when you click on Close the workbook and
it asks "Do you want to Save Changes?", if you click on Yes, it is fine.
But if you click on No or Cancel then giInClose will never get to set
to False again.
Therefore BeforeClose event (and in this and previous post of mine,
I meant this event should be in the Add in mspApps_WorkbookBeforeClose)
as under:
(The code I have in mind that you cancel the normal close method and do same
as normal save method our own way)

Private Sub moApps_WorkbookBeforeSave(ByVal Wb As Workbook, Cancel As
Boolean)
Cancel = True
Select Case MsgBox("Do you want to save changes in " & Wb.Name & "?",
vbYesNoCancel)
Case vbNo
Wb.Saved = True
Wb.Close
Case vbYes
giInClose = True
Wb.Save
End Select
End Sub

i.e. you set giInClose = True only in case user clicks on "Yes"
If he clicks on cancel, then Cancel = True is already said in the
first line so no need to add Case vbCancel

Sharad

 

[Dave Peterson]

You got another reply to your other post.

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.