Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbook_BeforeClose
To All,
I have the following working code: Private Sub Workbook_BeforeClose(Cancel As Boolean) On Error Resume Next '''''''''''''''''''''''''''''''''''''''' ' CleanUp CUSTOM MENU's: '''''''''''''''''''''''''''''''''''''''' Application.CommandBars("NAVIGATE").Delete Application.CommandBars("OVERTIME").Delete End Sub My problem is ... when I open (2 or more) workbooks that contain the code, the first one I close deletes the commanbars. Which leaves the other open workbooks unable to use the commandbars ... How could I modify this code to: 1) Check if another open workbook(s) exists and then 2) If exists, does it need the CommandBars? Note: If it helps ... A common thread of all the workbooks that require the Commandbars is: a Worksheet with the name "SHIFTS" is present. My thoughts on this is possibly a loop to check ALL worksheets ... and if "SHIFTS" exists 2 or more times then skip the delete since 2 workbooks must be open? Any thoughts on how to solve this problem? Thanks in advance ... Jim Pellechi |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbook_BeforeClose
How about separating your code into an Addin (and remove it from each of the
other workbooks). Then those commandbars will stay available as long as excel is open. And you may find it easier to update the code whenever something changes. JimP wrote: To All, I have the following working code: Private Sub Workbook_BeforeClose(Cancel As Boolean) On Error Resume Next '''''''''''''''''''''''''''''''''''''''' ' CleanUp CUSTOM MENU's: '''''''''''''''''''''''''''''''''''''''' Application.CommandBars("NAVIGATE").Delete Application.CommandBars("OVERTIME").Delete End Sub My problem is ... when I open (2 or more) workbooks that contain the code, the first one I close deletes the commanbars. Which leaves the other open workbooks unable to use the commandbars ... How could I modify this code to: 1) Check if another open workbook(s) exists and then 2) If exists, does it need the CommandBars? Note: If it helps ... A common thread of all the workbooks that require the Commandbars is: a Worksheet with the name "SHIFTS" is present. My thoughts on this is possibly a loop to check ALL worksheets ... and if "SHIFTS" exists 2 or more times then skip the delete since 2 workbooks must be open? Any thoughts on how to solve this problem? Thanks in advance ... Jim Pellechi -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Workbook_BeforeClose | Excel Discussion (Misc queries) | |||
Workbook_BeforeClose Question | Excel Worksheet Functions | |||
Workbook_BeforeClose problem | Excel Programming | |||
Workbook_BeforeClose Event | Excel Programming |