VBA Geeking: How To Change The Color Of A Task Row In MS Project

Sorry about the two week absence. I was on vacation last week (as much of a vacation cleaning out a garage and painting a bedroom can be–more on that another day), and the week before I was crazy busy at work preparing to be out for a week.

So, to celebrate being back, I thought I post a little something that came up just before I left.

A co-worker asked if I knew a way to change the color of a task row in MS Project when something in the task changes. The first question I asked her was “Does Project have a Task Change event?”. I’m not too familiar with the object model for MS Project, so I didn’t know off the top of my head.

Her answer was “Yes, it does.” Okay, good. The hardest part is done. I didn’t have to worry about creating my own custom event, something at which I’m not terribly strong. All I had to do was to capture the task being changed and change that task’s row’s color.

I started off by recording a macro to see what the VBA looked like when I changed the row color for a task. From there, I set about ripping it apart and re-writing it.

The result is below. The way I approached it was to grab the active cell. That should be the task that’s being altered. The Cell object in Project has the Task as one of it’s properties. So once I grabbed the Cell, I could set another variable to the Task.

From the Task, you can find the Task ID as a property. This is ultimately what I was looking for. Using the Task.ID, you can select an entire row. And once the row is selected, you can change the color.

My own little addition to this clean up was to make the code a function with a boolean argument. If the inbound boolean was True, then fill the cell with a color (I chose yellow, but you can fill in your own preference). If the boolean was False, then blow any highlighting away. Using this argument, a developer could make an evaluation in the Task Change event and decide whether the row should be filled or not.

Here’s the finished code:

Function ChangeTaskColor(FillIt As Boolean)

' Descripion: Change the color of the active task.

' Arguments: FillIt (boolean): True=fill in the task row with a color;

'                               False=clear any color from the row

 

' Constant declarations

Const vNO_FILL As Variant = -16777216

Const vYELLOW As Variant = 62207

 

' Variable declarations.

Dim Cel As Cell

Dim Tsk As Task

' Set the active cell variable first, then

' use it to get the task in which the cell resides.

Set Cel = ActiveCell

Set Tsk = Cel.Task

 

' Using task ID, select the entire row.

SelectRow Row:=Tsk.ID, RowRelative:=False

' Highlight the selected row.

If FillIt Then

Font32Ex CellColor:=vYELLOW

Else

Font32Ex CellColor:=vNO_FILL

End If

End Function

As I said, I’m not all that familiar with VBA in MS Project, so I had to poke around the object model for a bit. This might not be the most optimal way of approaching this. If anyone has a better way of tackling this challenge, please comment below. Always interested in learning better ways of doing things.