Before Close on "This Workbook"
 

Bruce,

You could try this in your workbook.before_close event:

Dim WBCount As Integer
WBCount = Application.Workbooks.Count
If WorkbookIsOpen("Personal.xls") Then _
WBCount = WBCount - 1
If WBCount 1 Then
' user has some other workbook open
' don't do anything
Else
Call DeleteMenu
End If

John

Bruce Roberson wrote:

I am using a version of J Walkenbach's menumaker.xls in
many of my spreadsheets. Each menu is similar in the
various workbooksbut but each workbook has its only Menu
Sheet with differences depending on the needs in that
particular workbook.

In the case where I have 2 or more workbooks open at the
same time, where this menu name "Revenue Menu" is in the
Menu sheet, I need to alter the before close method to
figure out if there is a still a spreadsheet open that has
called this createmenu in the Workbook_Open methods.

For example, workbook A is open, and I open Workbook B
which also has a "Revenue Menu" in its menu sheet page.
For some reason, I don't recall seeing two versions of
this menu up in the case where 2 or more workbooks come
open. But when I close workbook A in this case, it deletes
the "Revenue Menu" from the menubar. Then if I still need
the menu for Workbook B, then I have to go back to the
Menu Sheet page of that Workbook and click the button to
re-do the Menu.

Is there some code in the Workbook_Code method that might
alleviate this problem?

Private Sub Workbook_Open()
Call CreateMenu
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Call DeleteMenu
End Sub

Before Close on "This Workbook"
 

Where you are referring to personal.xls, is that the
workbook I'm closing? Or are you saying I have to know and
name what workbook is still open in my VBA code?

That of course would not work if I had to name the
workbook that was remaining open.


-----Original Message-----
Bruce,

You could try this in your workbook.before_close event:

Dim WBCount As Integer
WBCount = Application.Workbooks.Count
If WorkbookIsOpen("Personal.xls") Then _
WBCount = WBCount - 1
If WBCount 1 Then
' user has some other workbook open
' don't do anything
Else
Call DeleteMenu
End If

John

Bruce Roberson wrote:

I am using a version of J Walkenbach's menumaker.xls in
many of my spreadsheets. Each menu is similar in the
various workbooksbut but each workbook has its only Menu
Sheet with differences depending on the needs in that
particular workbook.

In the case where I have 2 or more workbooks open at the
same time, where this menu name "Revenue Menu" is in the
Menu sheet, I need to alter the before close method to
figure out if there is a still a spreadsheet open that
has
called this createmenu in the Workbook_Open methods.

For example, workbook A is open, and I open Workbook B
which also has a "Revenue Menu" in its menu sheet page.
For some reason, I don't recall seeing two versions of
this menu up in the case where 2 or more workbooks come
open. But when I close workbook A in this case, it
deletes
the "Revenue Menu" from the menubar. Then if I still
need
the menu for Workbook B, then I have to go back to the
Menu Sheet page of that Workbook and click the button to
re-do the Menu.

Is there some code in the Workbook_Code method that
might
alleviate this problem?

Private Sub Workbook_Open()
Call CreateMenu
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Call DeleteMenu
End Sub

.