Inhibiting cross spreadsheet custom-menu functionality.

[windsurferLA]

Inhibiting cross spreadsheet custom-menu functionality.

Workbook "A" installs custom menu items when it is opened, and removes
them when closing. If another workbook "B" is opened while "A" is also
open, the custom menu items appear in workbook "B." Inadvertently
selecting a custom menu item intended for workbook "A" from workbook "B"
leads to a "run time error" which would confuse an uneducated user.

How might I best prevent the macros designed for Workbook "A," Worksheet
"A-A" from running other than when Worksheet "A-A" is active? I expect
that I could place a test at the beginning of every macro that would
stop the process if ActiveSheet.value <> "Worksheet A" where "Worksheet
A" is the name of the worksheet for which the macros are designed.
However, I have nearly 200 macros, and I'm not anxious to have to
manually insert the code at the top of each of those 200 macros.

I possibly could do a global substitution , substituting ( ) + macro
code + ( ) for the ( ) that appears at the head of each macro. Can
anyone suggest a more elegant approach.

 

[George Nicholson]

There is a Workbook_Deactivate event where you might be able to
deactivate/disable your custom menu. Then use Workbook_Activate event to
re-activate/enable your custom menu.

The idea being your menu wouldn't be available unless a specific workbook is
active.

I've never tried this, so I don't know for sure whether it is as simple as
that. Sure sounds good in theory though, eh?

HTH,

 

[windsurferLA]

Interesting idea… At first I thought it could be an answer, and then
realized that there are problems unless I become far more sophisticated
and possibly find a solution using “.NET Framework” programming.

The web page
http://msdn2.microsoft.com/en-us/library/17sde2xt(VS.80).aspx talks of
event handlers. It appears that by using “.NET framework,” you can
sense various events and trigger event handlers. My problem is that I’d
like to stay away from having .NET Framework running on my machine
because of certain security issues.

In addition, there is also the issue of how to sense that the sheet is
hidden. AutoClose senses when a workbook is closed, but I don’t know of
a similar macro that senses when a worksheet is hidden. The sense tool
must be embedded in the workbook with the custom menus as one does not
know in advance what alternative worksheet might be opened.

Even though it did not pan out, thanks for the suggestion. It
introduced me to the concept of event handlers.

 

[Guest]

George,

I confirm that you're quite correct.

I prefer to programmatically make custom toolbars at wb open and at the same
time make them Temporary (whick deletes them when Excel.exe is closed as
opposed to just the wb). I use the Deactivate event to make them invisible
and the Activate event to make them visible again. I havn't been doing this
for too long but so far I've found it works extremely well.

Regards,
Greg

 

[windsurferLA]

Thanks for the hint..

The site
http://www.ozgrid.com/News/ExcelCustomNumberFormatsAutoRunMacros.htm

indicates that even in the old Excel97 that I use, there are the event
indicators

Workbook_Open, Workbook_BeforeClose, and possibly others

I need to explore to see if these functions are implemented in my
version of Excel
Worksheet_Open
Worksheet_BeforeClose

I have also found using Google a page that talks about using the
Worksheet open / before close event indicators to hide menus.

 

[windsurferLA]

Thanks for the hint..

The site
http://www.ozgrid.com/News/ExcelCustomNumberFormatsAutoRunMacros.htm

indicates that even in the old Excel97 that I use, there are the event
indicators

Workbook_Open, Workbook_BeforeClose, and possibly others

I need to explore to see if these functions are implemented in my
version of Excel
Worksheet_Open
Worksheet_BeforeClose

I have also found using Google a page that talks about using the
Worksheet open / before close event indicators to hide menus.