VBA Event Handler Example

I felt that it is time to give VBA some love. I was surfing Web and came across this post on Changing the Cell Background Color which gave me an idea for a blog post. This post is a simple example of how to write an event handler that checks if a name of a task has a prefix of “XYZ_”. If it does, it changes the background color of the Task Name cell to yellow.

This could be useful in scenarios where there is validation when saving to the server and you want to warn the user before the save. For example, say there is a third party application that inserts tasks into project plans automatically. When tasks are inserted by this application, it is prefixed with a code, “XYZ_”. This allows project managers to quickly identify tasks that have been inserted by the third party app. To prevent project managers from inserting the task with the same prefix, a Project Server event handler has been written to prevent tasks with the prefix from any user, except for the user context used by the third party app. This event is only fired during a save to Project Server. To give an early warning to the project manager that the project will fail on saving to the server, we can do following:

  1. Open Project and then the Visual Basic Editor (Alt + F11)

  2. Create a new Class Module for the Project

     

    Note: If you want the event to be fired for all projects that are associated with a Project Server, you will need to check out the Enterprise Global and create the event handler in it. For simplicity, I am only creating the event handler for this project.

  3. Change the name of the module to something meaningful, such as EventHandlers.

  4. Copy the following Code into the class module (This is the event handler):

    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, Cancel As Boolean)
        
        MsgBox ("Test")
       
    End Sub

    Note: This link is to the Project 2003 SDK. It is a list of all the Project Client events you can hook into:

    https://msdn2.microsoft.com/en-us/library/aa679860(office.11).aspx

  5. Open the ThisProject Object:

  6. Paste in the following code at the top of the ThisProject Object:

    Dim X As New EventHandlers

    Sub Initialize_App()

        Set X.App = MSProject.Application
        Set X.Proj = Application.ActiveProject

    End Sub

    This will setup the event handler to fire before a task is changed.

  7. Now select the "Project" Object and then the "Open" procedure:

    This will stub out the built in event handler that will fire when the project opens. Here we want to call the initialization method we created in step 6:

    Call Initialize_App

At this point we have the event handler hooked up and every time the user changes a task, they will get an annoying test message box. To test it, run: ThisProject.Initialize_App.

Here is what you should get when you change a task:

Now that we have the before task change event handler working, we need get the task that changed to change the cell background color to yellow if the task name begins with "XYZ_". In step 4 we created the event handler, we will need to change the code from displaying the test message box to:

Private Sub App_ProjectBeforeTaskChange(ByVal tsk As MSProject.Task, ByVal Field As PjField, ByVal NewVal As Variant, Cancel As Boolean)
   
    If (Field = pjTaskName) Then
        If (InStr(NewVal, "XYZ_") = 1) Then
            ActiveCell.CellColor = pjYellow
        End If
    End If
   
End Sub

Now every time a user changes a task name to begin with "XYZ_" they will see the background color of the cell change to yellow:

 

My scenario may be a bit of overkill, but hopefully it illustrates how to use the Before Task Change event and how to change the background color of a cell. Maybe in a future post, I will implement the server event that checks the names of the tasks.

Chris Boyd