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
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