Saving the Workbook

[eggpap]

In a Workbook_BeforeClose event I have some data saving tasks and th
ThisWorkbook.Save statement. Since I have deleted many Excel built-i
menus, to close the wb the user must click on the Excel window Clos
button or to select the item "Save and Exit" on a custom menu.

This is my problem:
when the user clicks the close button the data saving tasks are rightl
completed and he gets the confirmation message where he must click OK t
close and exit from Excel.

The Save and Exit menu item, instead, calls the simple standard modul
subroutine:

Sub SaveAndExit
ThisWorkbook.Close
end sub

that triggers the Workbook_BeforeClose event that executes the dat
saving tasks plus the ThisWorkbook.Save statement and displays th
confirmation message, -but then shows also the Excel request o
confirmation to save the workbook-. I've debugged and seen that the w
save is correctly excuted and the workbook.saved property correctly se
to true.

Many thanks,
Emilian

 

[Ariel Dugan]

Hi,

Have you tried turning alerts off?

I believe you can just put this line in the Workbook_BeforeClose event
handler

application.EnableAlerts = false

Thanks
Ariel

 

[eggpap]

Hi,
Have you tried turning alerts off?

Thanks Ariel,

yes I have, but it's the same.

Emilian

 

[Ariel Dugan]

Hi,

Its actually application.displayalerts that I was thinking of.

I would suggest that you alter the SaveAndExit sub as follows. I tested it
this way and it works. Note, you may need to move the functionality from
your Before_Close event into a regular module, and call it from both the
SaveAndExit and from the Before_Close event.

Sub SaveAndExit()
Application.DisplayAlerts = False

''Call your other functionality that you want exectued before saving...

ThisWorkbook.Save
ThisWorkbook.Close
End Sub

 

[eggpap]

Hi,

Its actually application.displayalerts that I was thinking of.

I would suggest that you alter the SaveAndExit sub as follows.
tested it
this way and it works. Note, you may need to move the functionalit
from
your Before_Close event into a regular module, and call it from bot
the
SaveAndExit and from the Before_Close event.

Sub SaveAndExit()
Application.DisplayAlerts = False

''Call your other functionality that you want exectued befor
saving...

ThisWorkbook.Save
ThisWorkbook.Close
End Sub

I've registered the following behaviour:
by the SaveAndExit menu command the alert is no more displayed, but th
wb don't closes (i.e. the Excel window don't closes). Moreover thi
action corrupts some cell format (from numeric to date) (see pictur
attached).

By the Excel window CloseButton nothing of this happens.

Thanks, Emilian

+-------------------------------------------------------------------
|Filename: FormatCorrupt.jpg
|Download: http://www.thecodecage.com/forumz/attachment.php?attachmentid=70
+-------------------------------------------------------------------