How to dim a menu when not workbook is open

[Néstor]

Hello,

My name is Néstor and I am making my first steps in VBA (although I am not
new in programming, I know C and C++).

I am working with Excel, and I have created a add-in that launches a form
when is selected from a menu option (menu that I have also created myself).
I would like that menu to be enable only when there is a workbook
open/active (any workbook) and to become dimmed if the user close all the
workbooks. This is what happens for instance with the option "Cells..." in
the menu "Insert" when there is not any worbook open.

Could someone help me with it? Thanks in advance.

Regards,

Néstor

 

[Tushar Mehta]

You would have to create a master add-in that installs/opens (or is
already installed/open) whenever any of your other add-ins are
installed.

This master add-in would have application level event procedures that
track what is going on in the XL environment. In response they would
hide/show enable/disable various menus/menu items.

The only issue with this is whether or not XL generates an event for
every kind of user-initiated / code-initiated change to the XL
environment that you are interested in. If all you want to do is check
if a window is active, I suspect -- but am not sure -- that
Window_Activate and _Deactivate should be enough.

As an alternative approach...I suspect it would be easier to enhance
the subroutine that responds to the user selecting a menu item. This
enhancement would check if the active workbook/sheet/whatever is the
right kind for your sub to operate on.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Néstor]

Thanks Tushar

Néstor


"Tushar Mehta" <[email protected]> escribió en el
mensaje You would have to create a master add-in that installs/opens (or is
already installed/open) whenever any of your other add-ins are
installed.

This master add-in would have application level event procedures that
track what is going on in the XL environment. In response they would
hide/show enable/disable various menus/menu items.

The only issue with this is whether or not XL generates an event for
every kind of user-initiated / code-initiated change to the XL
environment that you are interested in. If all you want to do is check
if a window is active, I suspect -- but am not sure -- that
Window_Activate and _Deactivate should be enough.

As an alternative approach...I suspect it would be easier to enhance
the subroutine that responds to the user selecting a menu item. This
enhancement would check if the active workbook/sheet/whatever is the
right kind for your sub to operate on.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions