Setting Subproject Status Date with VBA

If you read yesterday’s post, you’ll remember that I was kvetching about how setting the status date in the master project didn’t set the status date for subprojects – and how this impacted both task updates and EVMS calculations in the master project.

I could have just left it there, but I figured I’d sit down with my newfound VBA skills and see if I couldn’t write a macro to update subprojects’ status date in a master project.

Well, I did and here it is:

Sub StatusDate()

    Dim SP As Subproject
    Dim ISMaster As Boolean
    Dim MasterStatusDate As String

    If ActiveProject.Subprojects.Count > 0 Then
        ISMaster = True
    End If

    MasterStatusDate = InputBox("Enter the status date for the project.", "Set Status Date", MasterStatusDate)

    For Each SP In ActiveProject.Subprojects
        SP.SourceProject.StatusDate = MasterStatusDate
        Debug.Print SP.SourceProject.Name
        Debug.Print SP.SourceProject.StatusDate
    Next SP

End Sub
Setting Subproject Status Date with VBA

7 thoughts on “Setting Subproject Status Date with VBA

  1. Megan says:

    This seems great and I am definately going to ask one of our developers to look at adding this when we upgrade to 2010 as updating the status date is such a pain for our PM’s when they run multiple projects at the same time.

  2. […] Setting Subproject Status Date with VBA in cui viene mostrata una tecnica per impostare in modo automatico tramite una macro di Project, la Data Stato (Status Date) di tutti i sottoprogetti contenuti in una Master Plan (o Progetto di Consolidamento). Se vogliamo aggiornare tutti i sottoprogetti ad una certa data e fare un pò di analisi e reportistica, normalmente dobbiamo impostare uno per uno la Data Stato di tutti i sottoprogetti, la macro ci viene in aiuto per farlo in un colpo solo. […]

  3. tony bombata says:

    fantastic stuff. i’d been trying to do this for two days before i stumbled upon this thread. i am also trying to put together a portfolio of projects handled by my team.

    many thanks for this

    Also, in the previous thread, you added a new custom field to each of the projects called TEMP_Status which is set to display the Status Date at the task level. can you please share the code for this?

    I have been trying to do this but have been unsuccessful


  4. tony bombata says:

    i was able to do it, but i have a different challenge now. i’ve been struggling with this for weeks
    we have several project files on sharepoint. your code works fine on a local pc, but when we run on sharepoint, i immediately get a runtime error 91 ‘object variable or with block variable not set’. would appreciate any pointers. seems to be some issue between vba and sharepoint

    1. Last I checked, you couldn’t do the subproject linkage with projects stored in SharePoint (albeit it’s been a while). Something with the path/storage within SharePoint that’s not quite equivalent to keeping everything in a shared folder. I wouldn’t be surprised if VBA couldn’t find the source file when they’re stored in SharePoint.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s