BeforeClose Event
 

Hi,

I have an Excel 2003 template that I need some help with. When the user
opens the template generating an xls file based on the template, I have code
that disables all builtin commandbars and creates a custom one with just a
couple of buttons. On the BeforeClose event I reinstate the builtin commbars
and the custom bar is disabled. Here's the problem. When the user clicks
the application close button, they get the "Do you want to save
changes......" message. The builtin menu gets enabled at that time and if
the user cancels the message, could potentially change the information in the
header/footer through Page Setup. I've tried a few things such as
Application.DisplayAlerts = false (didn't work - still prompts).

When I added ActiveWorkbook.Close SaveChanges:=False, it worked but causes
the user has to click the Application close button 2 times to close Excel.
And, when you re-open Excel afterward the formular bar doesn't appear. See
my code below.

Thanks for your help
LeAnn

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim oCB As CommandBar

On Error Resume Next

ActiveWorkbook.Close SaveChanges:=False

For Each oCB In Application.CommandBars
oCB.Enabled = True
Next oCB

Application.DisplayFormulaBar = True

End Sub

BeforeClose Event
 

I figured out my own solution. I had to set some boolean variables to detect
what actions the user has taken and allow or disallow other actions.

"LeAnn" wrote:

Hi,

I have an Excel 2003 template that I need some help with. When the user
opens the template generating an xls file based on the template, I have code
that disables all builtin commandbars and creates a custom one with just a
couple of buttons. On the BeforeClose event I reinstate the builtin commbars
and the custom bar is disabled. Here's the problem. When the user clicks
the application close button, they get the "Do you want to save
changes......" message. The builtin menu gets enabled at that time and if
the user cancels the message, could potentially change the information in the
header/footer through Page Setup. I've tried a few things such as
Application.DisplayAlerts = false (didn't work - still prompts).

When I added ActiveWorkbook.Close SaveChanges:=False, it worked but causes
the user has to click the Application close button 2 times to close Excel.
And, when you re-open Excel afterward the formular bar doesn't appear. See
my code below.

Thanks for your help
LeAnn

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim oCB As CommandBar

On Error Resume Next

ActiveWorkbook.Close SaveChanges:=False

For Each oCB In Application.CommandBars
oCB.Enabled = True
Next oCB

Application.DisplayFormulaBar = True

End Sub