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

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?

--
Jim
"Geoff" wrote in message
...
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

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

Many thanks for that.

Geoff



"Jim Rech" wrote:

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?

--
Jim
"Geoff" wrote in message
...
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