XL97: Menu Added in Wrong Position on Menu Bar (158072)

SYMPTOMS

In Microsoft Excel 97, when you run a Visual Basic for Applications macro that adds a new menu to a menu bar, the new menu appears to the right of the Help menu, rather than to the left of it.

CAUSE

This will occur because, in Microsoft Excel 97, the Help menu does not have any special status. In earlier versions of Microsoft Excel, new menus cannot be created to the right of the Help menu.

This behavior is by design of Microsoft Excel.

WORKAROUND

If you need a custom menu to be created to the left of the Help menu, add the "Before:=" argument to the line of code that creates the new menu. For example:

   MenuBars(xlWorksheet).Menus.Add Caption:="Custom", Before:="Help"

You can use any valid menu name in the Before argument. The new menu will be created to the left of the specified menu name.

MORE INFORMATION

In earlier versions of Microsoft Excel, if you use a macro to create a custom menu and do not specify the menu bar position where the new menu should appear, the new menu always appears to the left of the Help menu. For example, if you execute this line of Visual Basic macro code:

   MenuBars(xlWorksheet).Menus.Add Caption:="Custom"

the new "Custom" menu will appear to the left of the Help menu.

This occurs because the Help menu has special status in earlier versions of Microsoft Excel: the Help menu is always the right-most menu on a menu bar.

In Microsoft Excel 97, the Help menu has no special status. Because of this, new menus are added to the right of the Help menu.

If your custom menu needs to appear to the left of the Help menu, use the workaround shown earlier in this article to create the custom menu.