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

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