XL97: Problems When Disabling/Enabling Menus (157754)
The information in this article applies to:
- Microsoft Excel 97 for Windows
This article was previously published under Q157754 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
Application.MenuBars(xlWorksheet).Menus("File").Enabled = True
This behavior is by design of Microsoft Excel.
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
CommandBars("File").Enabled = True
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
Modification Type: | Minor | Last Reviewed: | 10/11/2006 |
---|
Keywords: | kbdtacode kbmigrate kbprb kbProgramming KB157754 |
---|
|