10.18.2009

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

2 comments:

Anonymous said...

The code works and I accept that there is no non VBA method of achieving the result above.

But I know that you can change the colour of bars for example to Blue or Red depending on whether the car is critical or not via Format bar styles. Can you yo uspecify a colour for the bar if the task is behind schedule ?without Code
http://www.mousetraining.co.uk

Matt said...

John: I addressed your question in another post: http://awretchedhive.blogspot.com/2010/02/ms-project-tasks-behind-schedule.html

Cheers,

Matt