In part two of his series on using VBA in Microsoft Project 2010, Andrew Makar describes how to build a custom task field highlighting feature.
In my previous Visual Basic Programming for Microsoft Project 2010 post, I provided a simple Hello World tutorial that introduced how to use VBA code to extend the Ribbon UI and create a simple message. Now I'll show how to identify all the tasks in your project schedule that are missing a project baseline using VBA.In practice, new tasks are added to a project schedule as the schedule executes. The project manager identifies more granular tasks or adds new tasks based on modified scope. One step that is easily overlooked is the need to baseline the new tasks that were added to the project schedule. Usually I will insert the Baseline Finish column and filter on any tasks that have a Baseline Finish of NA. I prefer to view all the tasks with the missing baseline in the context of the entire project schedule (Figure A). Figure A
Project schedule with the missing baseline (Click the image to enlarge.)When you have a large project schedule, it can be difficult to identify the tasks missing the baseline dates within the entire schedule. A useful technique is to highlight the specific Baseline Finish Date fields that have the missing Baseline Finish dates. Figure B highlights the tasks with missing baseline finish dates without having to filter out the other tasks. Figure B
Tasks with missing baseline finish dates are highlighted. (Click the image to enlarge.)
The algorithm for the solution is:
- Select the Gantt Chart view and activate the Entry table.
- For each task in the schedule, check the Baseline Finish date for the NA value.
- If the date is NA, toggle the background color to yellow.
- Select the 1st row in the schedule when the checking is complete.
- Incorporate error handling in case the Baseline Finish date hasn't been added to the Entry table.
To build this custom task field highlighting feature, create the VBA routine by following these step-by-step instructions.
Step 1: Create the menu item
Using the code base from my last Microsoft Project tutorial, I will add another menu button using this code:
myNavBar = myNavBar + " <mso:button id=""missingBaseline"" label=""Missing Baseline"" " myNavBar = myNavBar + "imageMso=""DiagramTargetInsertClassic"" onAction=""toggleMissingBaseline""/>"Remember to access the Visual Basic editor by selecting the Developer tab in Microsoft Project 2010 and clicking the Visual Basic icon (Figure C). Figure C
Visual Basic icon
This snippet of code will add another button to the MyTools group of buttons on the Utility tab; we created this group of buttons and custom tab in my previous Microsoft Project VBA tutorial. The full subroutine that creates the ToggleMissingBaseline button and the Hello World button from the earlier tutorial is included at the end of this tutorial.
Step 2: Build the VBA subroutine
In the Visual Basic editor, create a new subroutine called toggleMissingBaseline and paste the following code:
Public Sub toggleMissingBaseline()
On Error GoTo ErrorHandler
Dim tsks As Tasks
Dim t As TaskDim rgbColor As Long
Set tsks = ActiveProject.Tasks
'Switch to Gantt Chart Entry View
ViewApplyEx Name:="&Gantt Chart", ApplyTo:=0TableApply Name:="&Entry"
For Each t In tsks
If (Not t Is Nothing) And (Not t.Summary) Then
SelectTaskField Row:=t.ID, Column:="Baseline Finish", RowRelative:=FalsergbColor = ActiveCell.CellColorEx
If t.BaselineFinish = "NA" Then
' Check whether the manual task color is white.
If rgbColor = &HFFFFFF Then
Font32Ex CellColor:=&H66FFFF ' Change the background to yellow.
Font32Ex CellColor:=&HFFFFFF ' Change the background to white.
' The task is automatically scheduled, so change the background to white.
End IfNext t
'Selects the top row in the table SelectRow Row:=0, RowRelative:=False
MsgBox "Error: Insert Baseline Finish Column and Toggle Again"Exit Sub
Step 3: Save your file and test itSave your file, close Microsoft Project, and re-open it to test the new Toggle Missing Baseline button (Figure D). Figure D
Missing Baseline button
The Baseline Finish field will toggle between white and yellow background colors, and each time the cursor will select the top row in the project schedule.Try to hide the Baseline Finish date and press the Missing Baseline button. Since the set of tasks assumes the Baseline Finish data is in the table, Microsoft Project will throw an error if it is not part of the view. Since the routine contains error handling, the appropriate message is displayed (Figure E). Figure E
The Font32Ex CellColor:=&H66FFFF line of code references a hexadecimal RGB value. In Microsoft Project's implementation, the RGB values are reversed, with the R value stored in the last byte position. You can use a hexadecimal color tool like ColorHexa to pick different colors -- just remember to reverse the value when adding it to the VBA code.
Using this code base, you can now loop through all of your tasks and perform various checks based on key project criteria. There may be more elegant ways to accomplish this same highlighting task, such as creating a custom filter and applying it to the Highlight filter, but you wouldn't have the chance to explore other opportunities with Microsoft Project and Visual Basic. You can also find a lot of helpful programming examples using Visual Basic at the Microsoft Project Developer Curriculum site.
In my next post, I'll explain how to build upon the code base and identify late tasks in the schedule using a similar method.