Article ID: 156880
Article Last Modified on 10/11/2006
APPLIES TO
- Microsoft Excel 97 Standard Edition
This article was previously published under Q156880
SUMMARY
In Microsoft Excel versions 5.0 and 7.0, you can use the MenuBars method in a Visual Basic for Applications macro to display different menu bars in Microsoft Excel. In Microsoft Excel 97, the MenuBars method is replaced by the CommandBars method.
However, to provide backward compatibility, Microsoft Excel 97 still supports the MenuBars method. Note that the MenuBars method works differently in Microsoft Excel 97 than it does in earlier versions of Microsoft Excel.
This article explains how to use the MenuBars method in Microsoft Excel 97.
MORE INFORMATION
Microsoft Excel 97
In Microsoft Excel 97, menu bars are replaced with command bars. Note that some Microsoft Excel 5.0 and 7.0 menu bars do not have an equivalent command bar. These earlier menu bars have been removed for simplicity and to provide a greater level of consistency with the other Microsoft Office 97 programs. For this reason, if you use the MenuBars method to display a menu bar, a different command bar than the one you specify may appear.
To display a command bar in Microsoft Excel 97, you can use the following MenuBars command in a macro:
MenuBars(<xlConstant>).Activate
where <xlConstant> is a variable.
The following table lists the Microsoft Excel 97 command bar equivalents for the Microsoft Excel version 5.0 and 7.0 menu bars, and the corresponding values.
Value in Value in 97 command bar name 97 Version Earlier Versions ---------------------------------------------------------- Worksheet Menu Bar xlWorksheet xlWorksheet4 Chart Menu Bar xlChart xlChart4 Worksheet Menu Bar xlWorksheet xlNoDocuments Worksheet Menu Bar xlWorksheet xlInfo Worksheet Menu Bar xlWorksheet xlWorksheetShort Chart Menu Bar xlChart xlChartShort Worksheet Menu Bar xlWorksheet xlWorksheet Chart Menu Bar xlChart xlChart Menu in Visual Basic Editor <None> xlModule
Example:
In Microsoft Excel 97, if you run a macro that contains the following command:
MenuBars(xlWorksheet4).Activate
Microsoft Excel 97 displays the "Worksheet Menu Bar" command bar, because the xlWorksheet4 value translates to "Worksheet Menu Bar" in Microsoft Excel 97.
To run the equivalent command in Microsoft Excel 97, use the following command:
CommandBars("Worksheet Menu Bar").Visible = True
NOTE: The CommandBars command does not use the value listed in the table. Instead, the command uses the name of the command bar that you want to display.
The Visual Basic Module Menu Bar:
The "Visual Basic Module" (xlModule) menu bar is not available in Microsoft Excel 97; there is no equivalent command bar. Instead, you must display the Visual Basic Editor. To do this, use the following command:
Application.VBE.MainWindow.Visible = True
Microsoft Excel 5.0 and 7.0
In Microsoft Excel versions 5.0 and 7.0, there are nine built-in menu bars that you can display by using the following MenuBars command in a macro:
MenuBars(<xlConstant>).Activate
where <xlConstant> is a variable.
The following table lists the built-in menu bars and the corresponding values that you use with the MenuBars command.
Menu bar Value ---------------------------------------------------------- Worksheet (Microsoft Excel 4.0) xlWorksheet4 Chart (Microsoft Excel 4.0) xlChart4 No Documents Open xlNoDocuments Info xlInfo Worksheet (Microsoft Excel 4.0, short) xlWorksheetShort Chart (Microsoft Excel 4.0, short) xlChartShort Worksheet xlWorksheet Chart xlChart Visual Basic Module xlModule
Example:
To display the Microsoft Excel 4.0 Worksheet MenuBar in Microsoft Excel 5.0 or 7.0, use the following command:
MenuBars(xlWorksheet4).Activate
REFERENCES
For more information about using the MenuBars method in Microsoft Excel 97, activate a Visual Basic module, type the following text
menubars
select the word, and press F1 to display the "Hidden Properties and Methods" help topic.
Additional query words: XL97 8.0
Keywords: kbinterop kbprb KB156880