MS Project VBA: Coloring Gantt to match Resource

I had copious amounts of spare time working as a technical writer 14 years ago, but one of the things I'm proud of is that for as much time as I wasted, I also invested in my future. I knew the IT guy at the workplace, and convinced him to install a copy of Visual Basic 5 on my computer, and then picked one of those "learn in 21 days" books. Although I have never really had use for Visual Basic in my travels, the Visual Basic for Applications knowledge -- writing macros for MS Project and MS Excel, for instance, has been invaluable in every job I've had since.

Coding isn't for everyone, but I find it interesting and a challenge. It appeals to the geek in me. Had I had more intererst at an earlier age (or VB existed back then -- I have always thought if anything I was born too soon), I would have probably become a master hacker by this point. I love logic and coding problems, and it's amazing to me how few people in business have the skills to do these things.

As an example of geeking out, on Linked In I received a notice of a discussion about how to automatically have the Gantt chart bar (in MS Project) change color depending on the resource. A scheduler had posted the question (posed by the client) and then asked if there was an easy way to do it.

The short answer is no. The slightly longer answer is to do it with VBA coding or a lengthly formula and field links. But, I couldn't turn away from the computer, even though my bathroom tub cleaning was really on my agenda for this morning, so I spent about a half-hour figuring out how I would do it. My guess is that this will turn out to be a requirements-changing product -- what I mean by that is once the client sees the rainbow of colors on the Gantt, he'll rethink his request. (Business "idea people" often need this reality check.)

Of course, the sad (and my girlfriend says "really attractive") thing is that this whole little coding experience was fun. Really fun. Embrace the geek.

Here's the code I wrote below. To work, cut and paste into a module in MS Project. Not that you'll need it.

Sub BarColorByResource()

'This sub will work only if the resource name matches correctly. The units removal section is
'not needed if every resource is allocated at 100%. If no resources match, default bar color is reset.

'Color constants (use on Middle color) ------------------------------------------
'pjColorAutomatic pjNavy pjAqua pjOlive pjBlack pjPurple pjBlue pjRed
'pjFuchsia pjSilver pjGray pjTeal pjGreen pjYellow pjLime pjWhite pjMaroon

Dim Tsk As Task
Dim sResource As String

'Scroll through all data rows in project
For Each Tsk In ActiveProject.Tasks

'Ignore tasks where there is no data (blank lines)
If Not Tsk Is Nothing Then

'Remove units brackets
sResource = Remove_Brackets(Tsk.ResourceNames)

Select Case sResource

Case "Matthew Jones" 'Matthew Jones

EditGoTo ID:=Tsk
SelectRow
GanttBarFormat MiddleColor:=pjAqua


Case "John Smith"

EditGoTo ID:=Tsk
SelectRow
GanttBarFormat MiddleColor:=pjNavy

'etc...

Case Else

EditGoTo ID:=Tsk
SelectRow
GanttBarFormat MiddleColor:=pjBlue 'default color restore


End Select

End If

Next Tsk

End Sub

Function Remove_Brackets(sTmp)

'Function will remove multiple instances of bracketed [] text in a string

iLen = Len(sTmp)

For i = 1 To iLen

sTmpChar = Mid(sTmp, i, 1)
If sTmpChar = "[" Then iLeft = i - 1
If sTmpChar = "]" Then

iRight = i
sTmp = Left(sTmp, iLeft) + Right(sTmp, iLen - iRight)
i = 1
iLen = Len(sTmp)

End If

Next

Remove_Brackets = sTmp

End Function