Tools/Macro Dialog Changes

Microsoft Excel 97 no longer includes the ability to add a macro to the Tools menu via the Macro Options dialog. Correspondingly, the MacroOptions method cannot add a macro command to the Tools menu. Attempting to add the menu item will not generate a run-time error, but the menu item will not be added. The following code example illustrates the proper way to insert a menu item with Microsoft Excel 97:

Set NewItem = CommandBars("Worksheet Menu Bar").Controls("Tools") _
    .Controls.Add(Type:=msoControlButton, Before:=6)
With NewItem
    .Caption:= "Pop Message Box"
    .OnAction:="NewMacro"
End With

Sub NewMacro()
    MsgBox "My New Macro"
End Sub