Trapping an attempt to use a custom menu

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I've managed to create a custom menu on the menu toolbar that opens and
closes with a particular workbook. It works just fine. But, I am able to
operate the menu from any open workbook and when a menu item is selected I
get an error...which I should since the menu is intended to only work with
the workbook that created it. How do I prevent the user from trying to open
the menu from outside the intended workbook. I am guessing that need to
get the identification of the workbook and compare it to the identification
of active workbook, e.g. the Activeworkbook.Name property. I'm a little over
my head on this one. Can anyone advise me?

TIA

Steve H
 
Hi Steve

The workbook with the code is ThisWorkbook. The active workbook is ActiveWorkbook. So, a
thing like, say
If ThisWorkbook.Name <> ActiveWorkbook.Name then Exit Sub
should catch most unwanted calls.
However, you application would probably be more elegant by hiding/howing the menu in the
Workbook_Activate / Deactivate events. Then there's no menu except where it makes sense.
 
Harald,

That worked great. I inserted the line before the Call statement in each Sub
called by the .OnAction in the menu selections. Now the menu opens but
closes on any selection.

Thanks

Steve
 
Glad to hear that Steve. Cheers.

Best wishes Harald
Followup to newsgroup only please.
 
Back
Top