Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
BeforeClose event help | Excel Programming | |||
BeforeClose Event | Excel Programming | |||
How to get out of BeforeClose event without closing? | Excel Programming | |||
ThisWorkBook BeforeClose Event | Excel Programming | |||
Cancelling the BeforeClose Event | Excel Programming |