The MenuBars and ToolBars familiar to users of previous versions of Microsoft Excel have been upgraded to CommandBars, an object common throughout all the Office 97 applications. CommandBars let you include both menus and buttons on the same bar. While we have sought to ensure that this new feature does not break existing code, it will impact your development going forward. Specifically, ToolBars and MenuBars are both modified using the CommandBar object model.
Microsoft Excel 97 allows you to store custom menu bars and toolbars with the workspace or with the workbook. When the user quits Microsoft Excel 97, the toolbars in the workspace are automatically saved in the file Username8.xlb (where Username is the current user's logon name). Alternatively, if the user isn't logged on, the file name is Excel8.xlb. The toolbars saved in a workbook are stored in the workbook file.
A number of menu bars have been eliminated from Microsoft Excel 97 to make Microsoft Excel more consistent with the rest of Office. If your code refers to MenuBars or MenuItems that no longer exist, Microsoft Excel will provide a non-error return value so your code will run without generating errors. Note that this is an instance where you will not be alerted by an error that Microsoft Excel 97 does not support the desired functionality. Also, be aware that the changes that you've made to the old menu bars will of course not appear.
The eliminated MenuBars are:
The Microsoft Excel 95 Menu Editor let you control the menus in Microsoft Excel without writing code by using a dialog to change the settings. When the workbook with the edited menu structure is loaded, the menu changes take effect. When the workbook is closed, the menu changes are reset. In Microsoft Excel 97, CommandBar customization through the user interface is at an application-level setting.
One of the most useful compatibility features of Microsoft Excel 97 is the ability to save Menu Editor changes into files that are saved in Microsoft Excel 97 format. In other words, while the Menu Editor does not exist in Microsoft Excel 97, the edits that have been built with the Menu Editor will be honored in Microsoft Excel 97. Remember that changes made to the CommandBar during a session with a file with Menu Editor customization will be lost when the file is closed.
There is, however, no backward compatibility for CommandBars. CommandBar changes are not saved when Microsoft Excel 97 saves a file in Microsoft Excel 95 format. This is consistent with CommandBar edits being at the application level.
Microsoft Excel 97 will continue to run your Visual Basic code that edits toolbars. Routines that modify Microsoft Excel 95 toolbars will continue to work. Be aware, though, that Microsoft Excel 97 introduces new toolbars, and that these toolbars will not be affected by code written for Microsoft Excel 95. For example, the following routine hides all toolbars in Microsoft Excel 95:
Sub HideAllToolbars()
Dim i
With Application
For i = 1 To Application.Toolbars.Count
Application.Toolbars(i).Visible = False
Next i
End With
End Sub
In Microsoft Excel 97, this routine will hide all of the toolbars that were available in Microsoft Excel 95, but will not affect the toolbars that are new to Microsoft Excel 97, nor will it affect Microsoft Excel 97 custom toolbars. The following routine makes all of the Microsoft Excel 97 command bars not visible:
Sub Excel97RemoveCommandBars()
Dim oBar as CommandBar
With Application
For Each oBar in .CommandBars
.CommandBars(oBar.Name).Visible = False
Next oBar
End With
End Sub
The ShortcutMenus method of the Application object will still function as it did in Microsoft Excel 95; however, this method will be hidden, and its use is no longer encouraged.
You now access shortcut menus by their CommandBar name via the CommandBars accessor. The shortcut name will not enumerate out of the CommandBars collection, although it is there. Supply the shortcut name rather than a constant. For example:
Application.ShortCutMenus(xlAxis).Delete
Should become:
Application.ShortCutMenus("Chart Axis").Delete
In Microsoft Excel 97, the Help menu does not default to the last menu on the CommandBar. In prior versions of Microsoft Excel, omitting the menu-placement argument meant that the custom menu was added directly to the left of the Help menu. In Microsoft Excel 97, this code will result in the custom menu being placed at the far right of the CommandBar.
To make sure your custom menu appears directly to the left of the Help menu:
Dim nHelpIndex as integer
Dim cmdbr as commandbar
Set cmdbr =Application.CommandBars("Worksheet Menu Bar")
nHelpIndex= cmdbr.Controls("help").Index
cmdbr.Controls.Add Type:=msoControlPopup, _
Before:= nHelpIndex