XL97: Using the MenuBars Method in Microsoft Excel 97

• Microsoft Excel 97 for Windows

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

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

Editor                    <None>          xlModule

In Microsoft Excel 97, if you run a macro that contains the following command:

   MenuBars(xlWorksheet4).Activate

To run the equivalent command in Microsoft Excel 97, use the following command:

   CommandBars("Worksheet Menu Bar").Visible = True

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

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

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