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