Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Application Level Global Variable
Hi,
I have another question: I create my customzation pull down menu. when my excel Tool.xls open, it will add pull down menu MyTool to the menu bar. when Tool.xls close, I will remove it from menu bar. I did this in the code below: Private Sub Workbook_BeforeClose(Cancel As Boolean) DeletePBRISMenu End Sub Private Sub Workbook_Open() CreatePBRISMenu End Sub Now my problem is, if there are multiple Tool.xls(it could be copied and renamed) opened, when first Tool.xls close, my pull down menu get removed. I am thinking to add a global variable (MenuCount) to count the number of opened tool.xls file. when a tool.xls opend, MenuCount++; a tool.xls closed, MenuCount--. when MenuCount = 0, remove my pull down menu. but it does not work. the reason I think is, my global variable is document level, not application level. Then how I can set a application level global variable? Or is there any other solution I should think about? Thank you in advance Wes |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Application Level Global Variable
I think you'd be better off to separate the code that creates the toolbar and
put it in a dedicated workbook. I'd even save it as an addin. Then the users can open whatever version that they need of tools.xls (or as many versions as they want) and just open the .xla once. By separating the code to a different workbook, it could make issuing updates to the code easier--if you have to change one routine, just fix *.xla and tell the users to dump their old version. If you keep the code in multiple tools.xls, then how will you ever catch up to all the copies? wesbird wrote: Hi, I have another question: I create my customzation pull down menu. when my excel Tool.xls open, it will add pull down menu MyTool to the menu bar. when Tool.xls close, I will remove it from menu bar. I did this in the code below: Private Sub Workbook_BeforeClose(Cancel As Boolean) DeletePBRISMenu End Sub Private Sub Workbook_Open() CreatePBRISMenu End Sub Now my problem is, if there are multiple Tool.xls(it could be copied and renamed) opened, when first Tool.xls close, my pull down menu get removed. I am thinking to add a global variable (MenuCount) to count the number of opened tool.xls file. when a tool.xls opend, MenuCount++; a tool.xls closed, MenuCount--. when MenuCount = 0, remove my pull down menu. but it does not work. the reason I think is, my global variable is document level, not application level. Then how I can set a application level global variable? Or is there any other solution I should think about? Thank you in advance Wes -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
why is it saying sheetcnt is "variable not defined" how to do a global variable to share over multiple functions in vba for excel? | Excel Worksheet Functions | |||
Code: Using Excel Application level Events | Excel Programming | |||
Global Variable -- Excel / VBA | Excel Discussion (Misc queries) | |||
How to Reference Global Variable for external application. | Excel Programming | |||
Excel Global Variable Setting | Excel Programming |