Cannot get Excel to Quit correctly

G

Geoff

The aim is to add a blank wsheet prior to closing a wbook and format it for a
splashscreen whenever the wbook is reopened. The code works ok as far as
adding the blank wsheet but there are now problems with quitting Excel.

When trying to quit the application with the wbook open, the code saves the
wbook as required but does not quit the application without a second click.

Quitting the application only works as expected if the wbook is closed
first. Putting Application.Quit into the BeforeClose event will of course
quit Excel but this isn't always ideal.

Can anyone suggest a remedy please?

In ThisWorkbook:
Private Sub Workbook_BeforeClose(Cancel As Boolean)

Sheets.Add After:=Sheets(Sheets.Count)

Sheets(Sheets.Count).Range("A100").Select 'prepare 'blank' sheet
ActiveWindow.DisplayGridlines = False
ActiveWindow.DisplayHeadings = False
ActiveWindow.DisplayOutline = False
ActiveWindow.DisplayZeros = False
ActiveWindow.DisplayHorizontalScrollBar = False
ActiveWindow.DisplayVerticalScrollBar = False
ActiveWindow.DisplayWorkbookTabs = False
ActiveWindow.ScrollColumn = 1
ActiveWindow.ScrollRow = 1

Application.DisplayAlerts = False
ActiveWorkbook.Close savechanges:=True
Application.DisplayAlerts = True

End Sub

T.I.A.

Geoff
 
J

Jim Rech

I haven't run your code but this:

ActiveWorkbook.Close

within the close event itself looks problematic. How about just saving the
workbook and letting the close you're already in do its job?
 
G

Geoff

Hi Jim
Just ActiveWorkbook.Save seems to suit requirements very well.

Many thanks for that.

Geoff
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top