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

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