MS Project Tips Archives

June 24, 2005

MS Project Tip - More than one resource in graph

The resource chart in Microsoft Project is a useful way of seeing how the resources on your project are loaded, but by default it only shows one resource at a time. This is not adequate when you want to see how the project as a whole is resourced. Unfortunately, the method to set it to show all resources is not easy to discover. In fact I'd been using Project for a couple of years before someone showed me how to do it (Thanks Adrian!). So to make things a little easier, here is how to show more than one resource in the resource graph view.

  • From the window menu select "split"
  • Select the top pane by clicking in it somewhere. You know it is selected because the bar on the far left containing the name of the view turns darker.
  • Go to the view menu and select any view which has resources - task usage, resource usage and the resource sheet are probably the most useful.
  • Select the bottom pane by clicking on it somewhere.
  • Go to the view menu and select the resource graph view.
  • Right click on the resource graph area on the right.
  • From the menu that pops up choose "Format Barstyles"
  • On the left half of the dialog box that appears (the part marked "Selected Resources") set the "Show as:" to be anything other than "Don't Show".
  • Click OK until you are back to where you started.
  • Now select resources or tasks with resources in the top pane and the chart for all of those selected resources will be shown in the bottom pane.
  • This tip works for Project 98, 2000, 2002, 2003 and likely future versions.

    June 28, 2005

    MS Project Tip - Renaming Fields

    One thing that confuses people about Microsoft Project is that there is often more than one way to do things. Naming fields is one of those things. As far as I am concerned there is a right way and a wrong way.

    This is the wrong way:

  • Double-click on the column header.
  • In the "Title" box enter the new title for the field.

    Why is it wrong? Well, the change is only made in that place. There is a new title for the field, but the title is not as widely used as the name. To really rename a field so you can use the new name in grouping, filtering, reports etc. you need to use a different method.

    Here is the correct way to rename a field:

  • Go to the tools menu
  • Select Customize / fields
  • Find the text2 field
  • Click the "rename" button
  • Enter the new name.
  • Click OK until you are back where you should be.

    Now any dialog boxes that present a list of fields will show the new name in the list - the old one will too so don't worry.

    Changing the field name applies to any table and any view that uses that field. Changing the field title (the first method)is limited to the single table that you have changed.

    My guess is that when project first came out you used the first method of changing the field title, but after a while they improved the functionality. However, they never cleaned house and removed the old method. In a way this is understandable - Why fix what is not broken? But on the other hand, eventually you end up with so much old stuff that the user interface becomes cramped and confusing. Project has definitely reached that point.

    One more thing to discuss and that is Master Projects and Inserted Projects. If you have changed the field name in a sub project and then inserted it into a master project the new field name will not show up. You MUST change it in the master project for the change to show. This is also true for views and other sorts of formatting. The parent does not inherit from the child.

    Note to those who prefer the old way: Yes I can think of some situations where changing the field title instead of the field name would be useful, but I'd be willing to live without the function.

  • August 16, 2005

    MS Project Tip - Working with Float

    In Microsoft Project everything is scheduled "As Soon as Possible" by default. This is generally a good thing, though it is a bit misleading at times. It is very common for people to look at the schedule and see that something did not start on the "start" date and get concerned. Fortunately there is an easy way to tell whether a task starting late is a problem or not.

    The way to do this is to look at the Total Slack (also referred to as Total Float) for the task. This value shows you the amount of time that a task can move later in time without affecting the finish of the project. Project calculates this automatically for you by performing a forward pass calculation - adding up the durations of tasks going forward to determine how early they can finish, and then a backward pass calculation - the same thing but from the project finish date which was calculated in the forward pass. As a result of these calculations you get an early finish date and a late finish date for every task. The difference between the two is the total slack or float.

    The problem with slack is that it is not always visible in the gantt chart. To make it visible is simple. First go to the "Format" menu and select "Barstyles. A dialog box comes up. Add two new barstyles as shown here:

    You note that I have two barstyles added and that they both use "finish" and "late finish" except in the opposite order. The reason for this is that Project draws all bars from left to right. The first bar will only be drawn if finish is earlier than late finish. The second bar will only be drawn if late finish is earlier than finish. This is a useful technique to remember for drawing other sorts of bars where you are comparing against another date (such as baseline, deadline etc.)

    The result of adding these barstyles is shown in the next diagram. You can see for the first group of tasks that Task D has total slack of one week. So if you are in the second week of the project and contemplating whether Task B or Task D is more important to get started on, the answer should be clear that it is Task B.

    The Neg Float bar comes into play when your tasks are already too late. In this case I set a "Must Finish On" constraint on the Finish milestone. In effect this makes Tasks A, B & C late by one day. You can see the red bar show up indicating that they are 1 day late. Task D is still showing 4 days of total slack.
    By making this information visible on the Gantt chart it is much easier to see what tasks are the ones which will affect your project completion and which tasks are less critical.

    August 29, 2005

    Writing Project VBA macros using the Macro Recorder

    One of the easiest ways to learn how to use Microsoft Project VBA is to use the macro recorder. It does not always produce reusable output, but it does output the correct syntax and it identifies the objects, properties and methods which are involved in what you want to do. Let's work through a simple example like zooming the timescale to show only the selected tasks.

    Start with turning on the macro recorder by going to the tools menu / select "macros" / select "record new macro". Give it a title and note where it is going to be saved.

    Now select some tasks in your project. Then go to the "view" menu / select "zoom" / select "selected tasks" and click OK. Now we turn off the macro recorder either by going back to the tools menu and choosing "stop recorder".

    Now you can look at the code. It should look something like this:

    Sub Macro1()
    ' Macro Macro1
    ' Macro Recorded 8/29/05 by yourusername.
    SelectRow Row:=-6, Height:=2
    ZoomTimescale Selection:=True
    End Sub

    This code is OK, but it is not reusable because each time you run it, it will select two rows which are 6 rows above where ever your cursor is. Chances are you don't want that. So we edit it and remove that row.

    Sub Macro1()
    ZoomTimescale Selection:=True
    End Sub

    This code works fine, IF you have a valid selection. Try running it on a blank row and you get an error. So we need to make one more modification to it.

    Sub Macro1()
    If Not ActiveSelection = 0 Then
    ZoomTimescale Selection:=True
    End If
    End Sub

    Now if we have a task or tasks selected this code will zoom the view to show the entire duration of the longest task. An obvious next step is to assign this to a toolbar button so you can zoom the selection with a single click.

    A more complicated example is exporting a file to excel. I can never remember the exact syntax off the top of my head, but turning on the macro recorder and exporting makes it easy. Here is the code I get while creating a map and saving a file. Note: for formatting reasons I've added several line continuation characters "_" so that the long lines will fit on the screen correctly.

    Sub Macro2()
    MapEdit Name:="Map 1", Create:=True, _
    OverwriteExisting:=True, _
    DataCategory:=0, _
    CategoryEnabled:=True, _
    TableName:="Task_Table1", _
    FieldName:="Name", ExternalFieldName:="Name", _
    ExportFilter:="Critical", _
    ImportMethod:=0, _
    HeaderRow:=True, _
    AssignmentData:=False, _
    TextDelimiter:=Chr$(9), _
    TextFileOrigin:=0, _
    UseHtmlTemplate:=False, _
    TemplateFile:="C:\...\Centered Mist Dark.html", _
    MapEdit Name:="Map 1", _
    DataCategory:=0, FieldName:="Finish", _
    MapEdit Name:="Map 1", DataCategory:=0, _
    FieldName:="% Complete", _
    MapEdit Name:="Map 1", DataCategory:=0, _
    FieldName:="Resource Names", ExternalFieldName:="Resource_Names"
    FileSaveAs Name:="C:\foo.xls", FormatID:="MSProject.XLS5", _
    map:="Map 1"
    End Sub

    You can see that the macro recorder makes this a lot easier than typing this in from scratch.

    November 17, 2005

    Microsoft Project Tip - Formulas and the IIF statement

    IIF(immediate if) statements are one of the most commonly used functions in ms project formulas. An IIF statement is basically a condensed version of the "If ...Then .. Else" statement which is often used in programming. The iif statement takes three arguments:

    IIf( expression, truepart, falsepart )

    The first is the expression you want to evaluate. It needs to be constructed so that it has a true or false answer so it is commonly used to compare vs. a particular value. (see this article for comparing with "NA" ). What the iif statement does next is dependent on whether the result is true or false.

    If the expression is true then the truepart is returned. This sounds quite simple and can be very simple. You could return something like a text value or a number. However, the power of the iif statement is that the truepart can be another expression, even another iif statement. This allows you to construct and test many parameters in a single formula.

    If the expression is false then the falsepart is returned. Like the truepart it can be an expression or set of nested expressions.

    The difficult part of constructing a good nested iif statement is to put the tests in the correct order. Once the statement follows a path to the end, any other ends are not evaluated. The second limitation is that custom field formulas are limited to 256 characters so be economical with your text.

    The IIF statement is also available in Excel for cell formulas, but in more recent versions of Excel (XP, 2003, perhaps 2000) it is called the If statement with exactly the same syntax.

    November 18, 2005

    Formatting Task Links in MS Project

    Someone asked if it is possible to hide the dependency lines (links) in Microsoft Project. By going to the format menu and selecting "layout" you can turn them off for all tasks. But there is a workaround for formatting them individually which can hide or highlight links. To do this we rely on the behavior of project to use the first applicable barstyle as the one which controls the link color.

    What this means is that if the first barstyle in the list applies to all tasks and is blue, then all the link lines are going to be blue. Since we can layer barstyles we can set the first barstyle to be one color and then overlay it with a different color. The link will take the first color and the task bar will take the color of the later style.

    By using the conditional formatting capability of flags we can set this differently for different tasks and thus control the link color on an individual basis.

    The first step is to set up some barstyles. In the screenshot you can see that I created white, red and green barstyles BEFORE the default blue barstyle. I use flags to control which tasks these barstyles are used for.


    Then we set the flags for the individual tasks. You can see below that the task with Flag1 set to Yes has a white link line with its successors. Likewise with the green and red link lines.


    Combining this with a custom field formula to set the value of the flag fields, you can programmatically change the color of the link lines in almost any manner you choose.

    December 5, 2005

    MS Project Tip - Creating User-defined Filters

    One of the most common activities with large schedule files is filtering them down to show the most important tasks. In most cases, important means immediate and many industries (the construction industry for example) a 3-week look ahead schedule showing current and upcoming activities is very common.

    Project's built-in filters allow you to filter based on a date range, but the date range filter asks for user input and you need to select the time periods. There is no way to easily automate this in VBA short of recreating and applying the filter, but in the more recent versions of project there is a very simple way to do this by using a custom field formula.

    I've described how to set up custom field formulas before. (Please click the link and read about them if you are new to the topic). For filtering purposes, the simplest way is to use a Flag field. Flag fields have a value of either "Yes" or "No". You can set them manually, but here we are going to use a formula. The formula I am showing performs a few simple tests. First it checks for all tasks that begin before a date three weeks in the future. Then it removes any which are already complete. Of these tasks it selects all of those which are not finished yet. If the task meets those criteria, the field is marked "Yes". If it doesn't then the field remains as "No". Here is the formula:

    IIf(([Start]<(Now()+21) And ([% Complete]<100)) And ([Finish]>Now()),"Yes","No")

    If you paste this formula into a flag field (for example Flag1) then all you need to do is create a filter which shows any tasks for which Flag1 is "Yes". Note that the formula as written doesn't show any incomplete tasks which are supposed to be completed in the past. Theoretically if you have an updated schedule you won't have any tasks like that, but you can modify the logic to add that condition. Using this technique you should be able to get around almost any interactive filtering challenge.

    November 28, 2006

    Microsoft Project Tips: Hiding Resource Percentages

    Once in a while the resource percentage showing on the task bar becomes too much. If you are wondering what I mean, take a look at this screen shot:


    See how each resource has the percentage shown after it? That happens whenever the resource is not assigned at exactly 100% to the task. When you update your schedule with actual work it is very likely that the resource will not longer be at a perfect 100%. The simple answer is to change the barstyle so that the text shown is the Resource Initials rather than the resource name. To do this, go to the format menu and select barstyles. Select the bar you want to edit and click on the Text tab in the bottom half. Change resource names to resource initials. Now, there is one more thing to do. Go to the resource sheet. Make sure it shows the resource intials column. Select the resource name column, then choose "copy" or CTRL+C. then select the resource initials column and paste CTRL+V the information into that column.

    December 11, 2006

    How to Change Cell Background color in MS Project

    Changing the background color for a task cell in Microsoft Project is now a two-step process:

    • Step 1 - Upgrade to Microsoft Project 2007 - you just can not do this in Project 2003 or earlier.
    • Step 2 - Rightclick on the cell, choose "font" and set background color and pattern.

    Sadly there are still only 16 colors to choose from, but they are getting there.

    August 29, 2007

    Views and Tables in Microsoft Project

    There is often some confusion about the difference between a view and a table in Microsoft Project. This should help clarify the difference between the two.

    A view consists of a number of potential elements:

    A screen (the right side typically, but may include splits. Also includes any special formatting for that screen such as modified barstyles etc.)
    A table (the data displayed on the right side
    A group (optional)
    A filter (typically "All tasks" but may be anything you want)

    Different views may access the same table or group or filter or screen but may use a different combination.
    Modifying a table will affect all views which use that table.
    For this reason, and because I frequently add/remove columns, it is good practice to build a specific table for each view. For example you might build a presentation view which uses a table and filter customized to show only the elements you want to present, or a print view etc.

    The data displayed in the table or view does not belong to the table. It is present in the file and is only displayed in the table, so deleting columns in the table does not delete that information. Deleting a row however will delete the information.

    This information is the same for all versions of Project, with the exception of Project 98 and earlier which did not have grouping.

    About MS Project Tips

    This page contains an archive of all entries posted to Project in the MS Project Tips category. They are listed from oldest to newest.

    Microsoft Project 2010 is the previous category.

    Programming is the next category.

    Many more can be found on the main index page or by looking through the archives.

    Creative Commons License
    This weblog is licensed under a Creative Commons License.
    Powered by
    Movable Type 3.34