Closing a workbook...

[Jeff Harbin]

I've written some code in the Workbook_BeforeClose( ) routine. It basically
clears and resets values in a spreadsheet before closing and the last line
of the subroutine is a Workbook.Save command (or something like that) and it
saves the changes without prompting me if I want to save them.

Up until now it's working perfectly but I've been using the 'x' from the
upper right hand corner of the window. Now I'm trying to create a custom
menu with an Exit command to use that to close the workbook in place of the
Close window command, 'x'.

I've coded the routine as follows:

Sub mcrShutDown ( )

ThisWorkbook.Saved = True
Application.Workbook.Close

End Sub

There are 2 problems: 1) I'm getting prompted to save changes and 2) it's
not running the Workbook_BeforeClose routine so my program doesn't get
reset.

What am I missing? How come the Workbook_BeforeClose routine isn't running?

Jeff

 

[Tamsin]

Hello,

I'm not sure Application.Workbook.Close exists
its either Application.Quit or ThisWorkBook.Close
Also ThisWorkBook.Saved = True tells excel that the
workbook doesn't need saving.

Leave all your reseting and ThisWorkBook.Save in teh
BeforeClose event and just put ThisWorkBook.Close in the
Exit event.

It's also possible to disable the x in the corner.

HTH

 

[scrabtree]

A work around for your second problem would be to switch
the "clear" the worksheet code from the before close to
the Workbook_Open() routine.