How to: Use VBA to Add a Custom Command to the Ribbon

  • Articolo

Applies to: Office 2010 | Project 2010 | Project Server 2010 | SharePoint Server 2010

This article shows how to programmatically add a custom tab and a command to the Fluent user interface (the ribbon), by using VBA in Microsoft Project 2010. The custom button calls a macro in the project that toggles the Task Name cell color of manually scheduled tasks. The VBA code can be used for a specific project or added to the global project template to modify the ribbon for all projects that are created or opened on the local computer.

Project 2010 uses the Fluent user interface with a ribbon, as do the other Microsoft Office 2010 applications. You can manually add tabs, groups, and buttons to the ribbon on the Customize Ribbon tab of the Project Options dialog box. To open Project Options, click Options in the Backstage view.

Because Project 2010 has a binary file format, to programmatically modify the ribbon, you must use the SetCustomUI method in VBA. You can also use the object model of the Primary Interop Assembly (PIA) for development of managed code add-ins for Project 2010. The path of the PIA is C:\Program Files (x86)\Microsoft Visual Studio 10.0\Visual Studio Tools for Office\PIA\Office14\Microsoft.Office.Interop.MSProject.dll.

Tip

Although you can use the SetCustomUI method in the PIA to develop managed code add-ins, Project 2010 also supports the Ribbon Designer in Microsoft Visual Studio 2010, which is an easier way to create custom tabs, groups, and other ribbon controls. For an example that does the same job as the VBA macro in this article, see How to: Use Managed Code to Add a Custom Command to the Ribbon.

For more information about the SetCustomUI method, see VBA Help in Project 2010. For more information about XML commands for modifying the ribbon, see the Office Fluent User Interface Developer Center.

Procedure 1. To create a project and macro for testing

  1. Create a project for testing the custom ribbon. For example, create a project named Toggle Cell Color for Manual Tasks.

  2. Open the Visual Basic Editor (VBE), expand the VBAProject node in the Project pane, and then double-click ThisProject to open the code page.

  3. Create a macro to use for the test. This example uses the ToggleManualTasksColor macro, but you could use another macro.

        Option Explicit
        
        Sub ToggleManualTasksColor()
            Dim tsks As Tasks
            Dim t As Task
            Dim rgbColor As Long
            
            Set tsks = ActiveProject.Tasks
            
            For Each t In tsks
                If (Not t Is Nothing) And (Not t.Summary) Then
                    SelectTaskField Row:=t.ID, Column:="Name", rowrelative:=False
                    rgbColor = ActiveCell.CellColorEx
                
                    If t.Manual Then
                        ' Check whether the manual task color is white.
                        If rgbColor = &HFFFFFF Then
                            Font32Ex CellColor:=&HF0D9C6  ' Change the background to light blue.
                        Else
                            Font32Ex CellColor:=&HFFFFFF  ' Change the background to white.
                        End If
                    Else
                        ' The task is automatically scheduled, so change the background to white.
                        Font32Ex CellColor:=&HFFFFFF
                    End If
                End If
            Next t
        End Sub

The ToggleManualTasksColor macro uses the new Font32Ex method in Project 2010, instead of the FontEx method, so that colors can be expressed in hexadecimal RGB values.


> [!NOTE]
> <P>Hexadecimal colors in Project 2010 are expressed in the order B-G-R. For example, &amp;HFF0000 is pure blue. You can use the decimal equivalent, for example 15784390 is the same light blue value as &amp;HF0D9C6, but it is easier to determine the color when using hexadecimal values.</P>
  1. Add some automatically scheduled tasks and some manually scheduled tasks to the project, and then run the macro to ensure that it works.

Procedure 2 shows how to modify the ribbon to run the example macro.

Procedure 2. To create the macro that modifies the ribbon

  1. Create the XML data for the ribbonXml argument in the SetCustomUI method. An XML editor helps to create valid XML for complex ribbon changes. For example, the following XML was formatted in Microsoft Visual Studio.

    <mso:customUI xmlns:mso="http:="//schemas.microsoft.com/office/2009/07/customui">
  <mso:ribbon>
    <mso:qat/>
    <mso:tabs>
      <mso:tab id="highlightTab" label="Highlight=" insertBeforeQ="mso:TabFormat">
        <mso:group id="testGroup" label="Test" autoScale="true=">
          <mso:button id="highlightManualTasks" label="Toggle Manual Task Color"
                      imageMso="DiagramTargetInsertClassic=" onAction="ToggleManualTasksColor="/>
        </mso:group>
      </mso:tab>
    </mso:tabs>
  </mso:ribbon>
</mso:customUI>

    When it is applied to the Project 2010 ribbon, the XML data does the following:

    • The mso:qat element is empty, so it has no effect on the Quick Access Toolbar.

    • The mso:tab element creates a tab named Highlight and inserts it before the Format tab on the ribbon. The id attribute can be an arbitrary unique alphanumeric value, with no spaces. The id attribute can be used by other actions to refer to a specific tab.

    • The mso:group element creates a group named Test on the Highlight tab.

    • The mso:button element creates a button named Toggle Manual Task Color that uses the image named DiagramTargetInsertClassic in the Microsoft Office icon collection. When the button is clicked, the OnAction attribute specifies that it runs the ToggleManualTasksColor macro.

      Tip

      To use an image, you can do one of the following:

      • On the Customize Ribbon tab of the Project Options dialog box, add a command to one of the existing groups on the ribbon, rename the command and set the icon, and then export the customization as the ProjectCustomizations.exportedUI file. To easily see the XML structure, copy the file contents to an XML file in Visual Studio, and then format the whole file (press Ctrl-E, and then press D). The image name is the value of the imageMso attribute of the command that you added.

      • Download the 2007 Office System Add-In: Icons Gallery, and then open the gallery in Microsoft Excel.

      • To see the icons and icon names available in Microsoft Office 2010 applications, download the Office 2010 Add-In: Icons Gallery.

      • Create an icon, and use the file name and path.

  2. Create a macro that runs the SetCustomUI method. In the following AddHighlightRibbon macro, each line of XML is copied to a line of the ribbonXml variable, and then each quotation mark character within the XML line is doubled so that the ribbonXml string contains the quotation mark character. To help readability, the XML formatting is maintained as much as is practical.

        Private Sub AddHighlightRibbon()
            Dim ribbonXml As String
            
            ribbonXml = "<mso:customUI xmlns:mso=""https://schemas.microsoft.com/office/2009/07/customui"">"
            ribbonXml = ribbonXml + "  <mso:ribbon>"
            ribbonXml = ribbonXml + "    <mso:qat/>"
            ribbonXml = ribbonXml + "    <mso:tabs>"
            ribbonXml = ribbonXml + "      <mso:tab id=""highlightTab"" label=""Highlight"" insertBeforeQ=""mso:TabFormat"">"
            ribbonXml = ribbonXml + "        <mso:group id=""testGroup"" label=""Test"" autoScale=""true"">"
            ribbonXml = ribbonXml + "          <mso:button id=""highlightManualTasks"" label=""Toggle Manual Task Color"" "
            ribbonXml = ribbonXml + "imageMso=""DiagramTargetInsertClassic"" onAction=""ToggleManualTasksColor""/>"
            ribbonXml = ribbonXml + "        </mso:group>"
            ribbonXml = ribbonXml + "      </mso:tab>"
            ribbonXml = ribbonXml + "    </mso:tabs>"
            ribbonXml = ribbonXml + "  </mso:ribbon>"
            ribbonXml = ribbonXml + "</mso:customUI>"
            
            ActiveProject.SetCustomUI (ribbonXml)
        End Sub

  1. Run the AddHighlightRibbon macro, to test whether it works.

    Tip

    To remove a ribbon customization, create a macro that runs SetCustomUI where the parameter contains an empty mso:ribbon element, such as the following: ribbonXml = "<mso:customUI xmlns:mso=""https://schemas.microsoft.com/office/2009/07/customui"">" & "<mso:ribbon></mso:ribbon></mso:customUI>"

Procedure 3 shows how to run the AddHighlightRibbon macro when you open the test project. To run the macro for all projects on the local computer, see Procedure 4.

Procedure 3. To modify the ribbon when you open the project

  1. Add a Project_Open event handler, such as the following:
        Private Sub Project_Open(ByVal pj As Project)
            AddHighlightRibbon
        End Sub

  1. Save and close the test project.

  2. Open the test project again.

When you open the test project, the Project_Open macro runs the AddHighlightRibbon macro, which adds the Highlight tab to the ribbon. Clicking Toggle Manual Task Color on the Highlight tab changes the background color of manually scheduled tasks (Figure 1).

Figure 1. Using the custom ribbon command

Using the custom ribbon command

In Procedure 3, the Highlight tab is added only when you open the specific test project that includes the macro code. Procedure 4 shows how to use the macro in the global project template so that it modifies the ribbon for all projects that are created or opened on the local computer.

Procedure 4. To modify the ribbon for all local projects

  1. Open the VBE, expand the ProjectGlobal node in the Project pane, and then double-click ThisProject (Global.MPT) to open the code page for the global project template.

  2. Copy all of the code in the example to the ThisProject (Global.MPT) code page.

  3. Change the event handler from the Project_Open event to the Project_Activate event, as follows:

        Private Sub Project_Activate(ByVal pj As MSProject.Project)
            AddHighlightRibbon
        End Sub

  1. Save the current project, and then exit Project 2010.

  2. Start Project 2010, which creates a new empty project.

When a new project is created, or when a project is opened, the Project_Activate event handler in Global.MPT runs the AddHighlightRibbon macro, as in Procedure 3.

If you leave the Project_Open event handler and the related macros in the test project, when you open the test project, the Project_Open event handler runs first, and then the Project_Activate event handler in Global.MPT runs. You can easily find the order of events by adding a message box statement in the event handlers, such as the following example:

    Private Sub Project_Open(ByVal pj As Project)
        If (Not pj Is Nothing) Then
            MsgBox "Opening project: " & pj.Name
        End If
        
        AddHighlightRibbon
    End Sub

When you copy an event handler from a specific project to Global.MPT, it is good practice to remove the event handler from the original project.

Note

If an event handler calls a second macro that makes a different ribbon modification with the SetCustomUI method, the second call overrides the first. To make both ribbon modifications, you must combine the XML data in the ribbonXml parameter into one SetCustomUI call.

Example

The following code shows the complete VBA example for the test project, using the Project_Open event handler.

    Option Explicit
    
    Sub ToggleManualTasksColor()
        Dim tsks As Tasks
        Dim t As Task
        Dim rgbColor As Long
        
        Set tsks = ActiveProject.Tasks
        
        For Each t In tsks
            If (Not t Is Nothing) And (Not t.Summary) Then
                SelectTaskField Row:=t.ID, Column:="Name", rowrelative:=False
                rgbColor = ActiveCell.CellColorEx
            
                If t.Manual Then
                    ' Check whether the manual task color is white.
                    If rgbColor = &HFFFFFF Then
                        Font32Ex CellColor:=&HF0D9C6  ' Change the background to light blue.
                    Else
                        Font32Ex CellColor:=&HFFFFFF  ' Change the background to white.
                    End If
                Else
                    ' The task is automatically scheduled, so change the background to white.
                    Font32Ex CellColor:=&HFFFFFF
                End If
            End If
        Next t
    End Sub
    
    Private Sub Project_Open(ByVal pj As Project)
        AddHighlightRibbon
    End Sub
    
    
    Private Sub AddHighlightRibbon()
        Dim ribbonXml As String
        
        ribbonXml = "<mso:customUI xmlns:mso=""https://schemas.microsoft.com/office/2009/07/customui"">"
        ribbonXml = ribbonXml + "  <mso:ribbon>"
        ribbonXml = ribbonXml + "    <mso:qat/>"
        ribbonXml = ribbonXml + "    <mso:tabs>"
        ribbonXml = ribbonXml + "      <mso:tab id=""highlightTab"" label=""Highlight"" insertBeforeQ=""mso:TabFormat"">"
        ribbonXml = ribbonXml + "        <mso:group id=""testGroup"" label=""Test"" autoScale=""true"">"
        ribbonXml = ribbonXml + "          <mso:button id=""highlightManualTasks"" label=""Toggle Manual Task Color"" "
        ribbonXml = ribbonXml + "imageMso=""DiagramTargetInsertClassic"" onAction=""ToggleManualTasksColor""/>"
        ribbonXml = ribbonXml + "        </mso:group>"
        ribbonXml = ribbonXml + "      </mso:tab>"
        ribbonXml = ribbonXml + "    </mso:tabs>"
        ribbonXml = ribbonXml + "  </mso:ribbon>"
        ribbonXml = ribbonXml + "</mso:customUI>"
        
        ActiveProject.SetCustomUI (ribbonXml)
    End Sub

Security

To set macro security, click Options in the Backstage view (on the File tab on the ribbon) and then do the following:

  1. In the Project Options dialog box, click the Trust Center tab, and then click Trust Center Settings.

  2. In the Trust Center dialog box, click the Macro Settings tab.

  3. To be able to test unsigned macros, click Disable all macros with notification, click OK, and then click OK again.

When you try to run an unsigned macro, Project shows the Microsoft Project Security Notice dialog box. Click Enable Macros to run the macros in the project.

Tip

When you digitally sign a macro project, you can open the project without seeing the Microsoft Project Security Notice dialog box. The Digitally sign your macro project article applies to all Microsoft Office 2010 products that use VBA, including Project 2010.

See Also

Tasks

How to: Use Managed Code to Add a Custom Command to the Ribbon

Reference

Tables of VBA Object Model Changes

Other Resources

Using the Project.Open Event

Office Fluent User Interface Developer Center

2007 Office System Add-In: Icons Gallery

Office 2010 Add-In: Icons Gallery

Digitally sign your macro project