XL98: No Shortcut Keys Listed on Custom Menu Item

• Microsoft Excel 98 Macintosh Edition

SYMPTOMS

If you run a macro that adds a custom menu item to a menu, the macro is run without error, but the shortcut key combination is not added next to the menu item. Also, the shortcut key for the menu item does not function.

CAUSE

Your shortcut key combination may not be displayed next to your custom menu item, and the shortcut key combination may not work if the macro that creates the menu item was created in Microsoft Excel for the Macintosh, version 5.0.

The object model for toolbars, menus and menu items has changed to the CommandBar object model in Microsoft Excel 98 Macintosh Edition. With this change, the Shortcutkey argument for the Add method of the MenuItems collection is being ignored.

WORKAROUND

Microsoft provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support professionals can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific needs. If you have limited programming experience, you may want to contact the Microsoft fee-based consulting line at (800) 936-5200. For more information about the support options available from Microsoft, please see the following page on the World Wide Web:

http://www.microsoft.com/support/supportnet/overview/overview.asp

Creating and Running the Macro

1. Close and save any open workbooks, and then create a new workbook.



2. Start the Visual Basic Editor (press OPTION+F11).



3. On the Insert menu click module.



4. Enter the following in the module:

   
          Sub Test()
             'This macro is called by the "my item" menu item on the
             '"my menu" menu.
             MsgBox "your custom menu item"
          End Sub
   
          Sub Add_Menu()
   
             'This will add a menu item called "my menu" to the menu bar.
             Set mymenu = CommandBars("worksheet menu bar").Controls.Add _
             (Type:=msoControlPopup)
             mymenu.Caption = "my menu"
   
             'This will add a menu item called "my item" to the "my menu"
             'menu.
             Set myitem = mymenu.Controls.Add(Type:=msoControlButton)
             myitem.Caption = "my item"
             myitem.OnAction = "Test"
             myitem.ShortcutText = "Cmd+Option+k"
             myitem.Style = 2
   
          End Sub 




5. On the File menu, click "Close and Return to Microsoft Excel."



6. On the Tools menu, point to Macro, and then click Macros.



7. Select Test in the list of macros, and then click Options.



8. Type the letter "k" in the box for the Shortcut key. Click OK.



9. Select add_menu in the list of macros, and then click Run.
   
   The custom menu item is added to the menu bar.



10. Press COMMAND+OPTION+K.
    
    The Test macro is run, and a message box is displayed.



11. Click OK.

Removing the Custom Menu

1. Start the Visual Basic Editor (press OPTION+F11).



2. Type the following code on the module you created in the previous steps:

   
         Sub Remove_Menu()
   
            CommandBars("worksheet menu bar").Controls("my menu").Delete
   
         End Sub 




3. Run the Remove_Menu macro.

STATUS

Microsoft has confirmed this to be a problem in Microsoft Excel 98 Macintosh Edition.

MORE INFORMATION

The following macros successfully run in both Excel 5.0 and Excel 98. However, in Excel 98, the custom menu item does not have the shortcut key displayed next to it.

NOTE: In Excel 5.0, the custom menu item, Item1, can be activated by pressing COMMAND+K.

   Sub Add_Menu()

   Application.MenuBars(xlWorksheet).Menus.Add "Test"
   Set mymenu = MenuBars(xlWorksheet).Menus("Test").MenuItems
      mymenu.Add Caption:="Item1", OnAction:="Select_Item1", _
      ShortcutKey:="k"

   End Sub

   Sub Select_Item1()
      MsgBox "Item1"
   End Sub 

   Sub Remove_Test()
   Application.MenuBars(xlWorksheet).Menus("Test").Delete
   End Sub 

REFERENCES

For more information about command bars, from the Visual Basic Editor, click the Office Assistant, type commandbars, click Search, and then click to view "Using command bars."

NOTE: If the Assistant is hidden, click the Office Assistant button on the Standard toolbar. If the Assistant is not able to answer your query, please see the following article in the Microsoft Knowledge Base:

Q176476 OFF: Office Assistant Not Answering Visual Basic Questions

Additional query words: XL98

Keywords : kbdta xlvbahowto xlvbmigrate
Version : MACINTOSH:98
Platform : MACINTOSH
Issue type : kbbug