Handling events in VBA for Microsoft Project

VBA can be used to capture certain events within Microsoft Project.  This could be used, for example, to capture when a field has been changed in the active Project view, when a task is added or when a Project file is saved.  This can therefore be used to automatically update fields or trigger other process through a Macro when these events happen.  As such, it is quite flexible although sometimes a little clunky.

Event Handler Class

The first thing that we need to do is create a Class Module, which in my example I have called myEvents, which will contain the event handling methods.

We need to create an Application and/or Project instance variable WithEvents to capture events.  There are different event handlers available for Application or Project.  Here I have chosen a simple example using the ProjectBeforeTaskChange event handler.  This method needs to be prefixed by App_ which is the variable name for our instance of the Application object.

Option Explicit


Public WithEvents App As Application
Public WithEvents Proj As Project


Private Sub App_ProjectBeforeTaskChange(ByVal tsk As MSProject.Task, _
                                        ByVal Field As PjField, _
                                        ByVal NewVal As Variant, _
                                        ByRef Cancel As Boolean)    

    MsgBox ("Event: Project Task Changed")        

End Sub

ProjectBeforeTaskChange Event

I have used this event as an example as it is a good way of understanding how events work and understanding the fields that are passed to the event handler.

The PjField enumeration signifies the different fields that could be changed in the view where the task has been changed.   In other words, these are the different columns available in the standard Gantt Chart view of Microsoft Project. I have chosen the pjTaskWork field which will show as thw Work column but a full list of fields can be found here. I have then used the Task that the Event was triggered for and the NewVal field to work out what the field has been changed to.

If Field = pjTaskWork Then

    MsgBox ("Work changed for task : " + tsk.Name + _
            " to " + CStr(NewVal) + _
            " from " + CStr(tsk.Work / 60))

End If

Notice the event only triggers for the field when it is directly changed.  If you change another field that causes the Work field to be recalculated the event is not triggered.

The fourth variable passed to the Event handler is Cancel.  If this is set to True and is passed ByRef then the returning True value will cause the change to stop.  In this case, the user will be returned to the field which he is editing with the cursor still blinking for the edit to be accepted.

If Val(NewVal) = 0 Then

    MsgBox ("Can't let you change work to zero")
    Cancel = True

End If

Other Event Handlers

There are multiple event handlers that could be used.  The two pages below in the main VBA reference provide a full list

• Application Events
• Project Events

For completeness, there is a simple example using an Event on the Project object.  This Event fires when the calculate project event happens which may be after a task change or depending on your settings when you manually trigger it.  Either way, it might depending on your setup be a good event to trigger your code from.

Private Sub App_ProjectCalculate(ByVal p As Project)

    MsgBox ("Calcualate project: " + p.Name)

End Sub

Download Full File

The full file for the code below can be downloaded from here in a Microsoft Project file with the VBA Macros already setup and ready to use. (It uses a proof of work formula before opening):

MSP VBA - Part 4- Using Events in Projects

About Me

If you like this post then please comment below.  Please also make a small contribution so that I can post more code snippets and samples here.

Bitcoin:      1MhaZvTqftdebwNihisrAbz22zaSG6tFcN

Ethereum:  0xe69b176A1A5492f19E3595389EA094C732723b69