Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Protect "Copy" and "Save As" changes to workbook | Excel Discussion (Misc queries) | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
cannot use "Create List" and "Share Workbook" same time | Excel Discussion (Misc queries) | |||
How can I get "File Close" to prompt me to "Save Changes" ?? | Excel Programming | |||
Why doesn't "Workbook.Range("myrange").value" work? | Excel Programming |