Adding a menu item to the Tools menu

A common task is to add a new item to the Tools menu (or any other menu, for that matter). The best approach is to write your own VBA code to do so. Or you can simply use the code that follows. No charge.

The two macros are stored in the code window for the ThisWorkbook object. The procedures are event handlers that respond to the Workbook Open event and the Workbook BeforeClose event. They include some simple error-handling code to handle the case in which there is no Tools menu (or if it has a different name).

Const MenuItemName As String = "Run Budget Macro" Const MenuItemMacro As String = "UpdateBudget"

Private Sub Workbook_Open() ' Adds a menu item to the Tools menu Dim NewItem As CommandBarControl Dim Msg As String

' Delete the existing item just in case On Error Resume Next Application.CommandBars(l). _

Controls("Tools").Controls(MenuItemName).Delete

Set up error trapping On Error GoTo NoCanDo

Create the new menu item

Set NewItem = Application.CommandBars(l). _ Controls("Tools").Controls.Add

Specify the Caption and OnAction properties NewItem.Caption = MenuItemName NewItem.OnAction = MenuItemMacro

Add a separator bar before the menu item NewItem.BeginGroup = True Exit Sub

' Error handler NoCanDo:

Msg = "An error occurred." & vbNewLine

Msg = Msg & "Attempting to add an item to the Tools menu." MsgBox Msg, vbCritical End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean) ' Delete the new menu item On Error Resume Next Application.CommandBars(l). _ Controls("Tools").Controls(MenuItemName).Delete End Sub

The example adds a new menu item (Run Budget Macro) to the Tools menu when the workbook is opened. This menu item, when clicked, runs a macro named UpdateBudget. When the workbook is closed, the menu item is removed from the Tools menu.

To use this code in your own application, enter the code into the code window for the ThisWorkbook object; then change the values for the MenultemName and MenultemMacro constants.

This example is available on this book's Web site.

0 0

Post a comment