VBA GANTT Bar Formatting RRS feed

  • Question

  • I'm working on a MS Project 2007 file that provides a portfolio view of a group of about a dozen projects. We want to set the color for each project's tasks to one of the predefined colors based on the name of the project.  We have tasks, summary tasks, and milestone symbols in each section.  In other words, we want the GANTT bars for Project 1 to have blue bars and symbols, Project 2 to have gray bars and symbols, Project 3 to have fuschia bars and symbols, etc.

    I have written a set of five rules in the Bar Styles table to control how Summary Tasks, Tasks, late Tasks, Milestones and Late Milestones look.  There don't appear to be enough rows in the Bar Styles table to recreate those a dozen times so I'm guessing I need to use the Program Name in a text field and VBA to set the color for each Bar type for each Program name.

    I can override the color for each bar individually to set the colors after creating the Bar Styles but there are 500 lines so I'd rather go with a more elegant approach. I haven't done much VBA programming so I want to make sure I have a sound approach in mind before I dive in and starting working on it.

    Thanks in advance for any pointers you can provide...


    Friday, March 29, 2013 10:27 PM

All replies

  • it sounds like you've already figured out how to toggle the colors with flag fields?  You could create calculations based on the Project field that toggles the flag fields, and use that to control the color of the Gantt Chart bars.

    I just tried it out, and this VBA might get you most of the way there.  You'll need to swap out the T.Name field for the T.Project field, and then plug in the project names (and copy the If..Thens) to match the projects you have in your file.

    You'll have to figure out the color numbers.  Note that when in doubt, you can record a macro of yourself modifying the Gantt and then go into the VBA editor (Alt - F11) and see what the resulting code looks like.

    Sub GanttFormat()

        Dim T As Task
        For Each T In ActiveProject.Tasks
        'Swap out T.Project in the line below
            If T.Name = "Task 1" Then
                GanttBarFormatEx TaskID:=T.ID, StartColor:=0, MiddleColor:=5066944, EndColor:=0
            End If
            If T.Name = "Task 2" Then
                GanttBarFormatEx TaskID:=T.ID, StartColor:=0, MiddleColor:=5066944, EndColor:=0
            End If
         Next T

    End Sub

    Andrew Lavinsky [MVP] Blog: Twitter: @alavinsky

    Saturday, March 30, 2013 2:22 AM