XL97: Problems When Disabling/Enabling Menus

Last reviewed: March 18, 1998
Article ID: Q157754
The information in this article applies to:
  • Microsoft Excel 97 for Windows

SYMPTOMS

In Microsoft Excel 97, if you run a Visual Basic for Applications macro that attempts to disable an entire menu (the File menu, for example), the menu is not disabled and you do not receive any error messages. The same macro works correctly in Microsoft Excel versions 5.0 and 7.0.

The same applies if a Visual Basic for Applications macro attempts to enable an entire menu.

CAUSE

This will occur if your macro uses code similar to the following to disable or enable an entire menu, respectively:

   Application.MenuBars(xlWorksheet).Menus("File").Enabled = False

    -or-

   Application.MenuBars(xlWorksheet).Menus("File").Enabled = True

WORKAROUND

If a Visual Basic for Applications macro in Microsoft Excel 97 must disable or enable an entire menu, use code similar to the following in your macro, respectively:

   CommandBars("File").Enabled = False

    -or-

   CommandBars("File").Enabled = True

STATUS

This behavior is by design of Microsoft Excel 97.

MORE INFORMATION

In Microsoft Excel versions 5.0 and 7.0, menus are contained in menubars. For example, the File menu is contained by the "xlWorksheet" menubar. Because of this, the command to disable a particular menu requires that you refer not only to the menu, but also to the menubar that contains it. For example:

   Application.MenuBars(xlWorksheet).Menus("File").Enabled = False

NOTE: Adding "Application." to the beginning of the line of code is usually not required, but it is included here in order to provide a complete code example.

In Microsoft Excel 97, menus are not contained by menubars. Instead, they are independent entities belonging to the CommandBars collection. So, you don't have to refer to any other object when trying to disable a particular menu:

   CommandBars("File").Enabled = False

This command will work correctly in Microsoft Excel 97.

Because of the change in program design, Microsoft Excel 97 cannot properly execute the first command shown above. However, you will not receive an error message when the command is executed: Microsoft Excel 97 will merely ignore the command and continue macro execution.

Below are two macro examples that demonstrate the difference in behavior between Microsoft Excel 97 and earlier versions of Microsoft Excel. The first macro will disable the File menu in Microsoft Excel versions 5.0 and 7.0, but will do nothing in Microsoft Excel 97. The second macro will disable the File menu in Microsoft Excel 97. To redisplay the File menu in any version of Microsoft Excel, set the Enabled property in either macro to True and then rerun the macro.

   Sub WorksInExcel5And7()
       Application.MenuBars(xlWorksheet).Menus("File").Enabled = False
   End Sub

   Sub WorksInExcel97()
       CommandBars("File").Enabled = False
   End Sub


Additional query words: XL97 8.00
Keywords : kbprg kbdta kbmigrate kbdtacode xlvbainfo xlui
Version : WINDOWS:97
Platform : WINDOWS
Issue type : kbprb


THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: March 18, 1998
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.