Creating a Menu Command for Activating Add-Ins

In some cases, you may want to provide access to your add-in through a menu command. In this example, we’ll place a menu command for our new add-in on the Tools menu.

The following procedure is designed to build on example code presented in “Creating a Basic Add-In.”

To place a menu command for an add-in on the Tools menu

1. Make sure that “Microsoft Office 8.0 Object Library” is selected in the References dialog box. This allows you access to the Office command bar objects.
   
   
2. Add the following code to the General Declarations section of the class module:

   Public VBI As VBIDE.VBE
   ' VBI is assigned a pointer to the current IDE's
   ' VBA object which is later passed as a parameter
   ' to the OnConnection procedure. It's retained
   ' because you need it later for disconnecting the
   ' add-in. Other procedures may have a need for it
   ' as well.
   
   Private mcbMenuCommandBarCtrl As _
   Office.CommandBarControl
   ' This will be set to the new command bar control.
   
   Private WithEvents MenuHandler As CommandBarEvents
   ' This is the event handling procedure for 
   ' the click event of the new command bar control.
   

3. The following procedure is called when the add-in is connected in the Add-In Manager. It adds a new menu command to the Tools menu called “My New Add-In.” In the IDTExtensibility_OnConnection procedure, remove the MsgBox line and add the following:

   Private Sub IDTExtensibility_OnConnection _
   (ByVal VBInst As Object, _
   ByVal ConnectMode As VBIDE.vbext_ConnectMode, _
   ByVal AddInInst As VBIDE.AddIn, _
   custom() As Variant)
   
   ' Save the current instance of Visual Basic.
   Set VBI = VBInst
   ' Add a menu command to the Tools menu.
   Set mcbMenuCommandBarCtrl = _
   VBI.CommandBars("Tools").Controls.Add(before:=3)
   ' Place a separator bar before the new
   ' menu command.
   mcbMenuCommandBarCtrl.BeginGroup = True
   ' Set the title for the add-in.
   mcbMenuCommandBarCtrl.Caption = "My New Add-In"
   ' Copy an icon bitmap to the clipboard.
   Clipboard.SetData _
   LoadPicture("c:\windows\triangles.bmp")
   ' Copy the icon from the clipboard to the menu
   ' command's icon.
   mcbMenuCommandBarCtrl.PasteFace
   ' Connect the event handler to receive the
   ' events for the new command bar control.
   Set MenuHandler = _
   VBI.Events.CommandBarEvents _
   (mcbMenuCommandBarCtrl)
   ' Place a separator bar after the new
   ' menu command.
   VBI.CommandBars("Tools").Controls(4).BeginGroup _
   = True
   End Sub
   

4. The next procedure is called whenever the add-in is disconnected. The mcbMenuCommandBar.Delete line ensures that the menu command in the Tools menu is removed once the add-in is disconnected. Add the following lines to the IDTExtensibility_OnDisconnection procedure. (You can also remove the MsgBox line from the original code if you wish):

   Private Sub IDTExtensibility_OnDisconnection _
   (ByVal VBInst As Object, ByVal LoadMode As _
   Long, ByVal AddInInst As VBIDE.AddIn, custom() _
   As Variant)
   ' Delete the new menu command from the Tools 
   ' menu.
   mcbMenuCommandBarCtrl.Delete
   End Sub
   

5. The MenuHandler_Click event is called whenever you click the new menu command on the Tools menu. The optional message box function is here only to signal that the Click event was correctly intercepted. Add the following procedure to the add-in’s class module by using the dropdown to insert the event handler:

   Private Sub MenuHandler_Click(ByVal _
   CommandBarControl As Object, handled As Boolean, _
   CancelDefault As Boolean)
   MsgBox "You clicked the new menu command."
   End Sub
   

6. Save and then compile the project (as an ActiveX component).
   
   
7. Start a new project and activate the add-in through the Add-In Manager.
   
   
8. Look in the Tools menu. Notice that there is now a My New Add-In menu command.
   
   
9. Click the new menu command. You should get a message box confirming that you clicked the menu command.
   
   
10. Disconnect the add-in in the Add-In Manager. Notice that the menu command is removed from the Tools menu.

To summarize, you now have an add-in which:

• Adds a command to the Tools menu when connected to Visual Basic.
  
  
• Demonstrates that it handles an event when the menu command is clicked.
  
  
• Removes the command from the Tools menu when disconnected.