VBA Event Handler Example

  • Article

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

Comments

  • Anonymous
    February 18, 2007
    The other day I had a request from an internal customer that wanted to create deliverables for a large

  • Anonymous
    August 12, 2008
    Chris - I just posted this on the Microsoft Project Developer board, so forgive me for cross-posting.  I was able to follow your steps for this event handler example and would like to expand it. Can I highlight a row when a specific resource is entered? Here's my attempt at the code.  I'm interested in hearing if this can be a way of conditional formatting. 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)    If (Field = pjResourceName) Then        If (InStr(NewVal, "Member Firm") = 1) Then            ActiveCell.CellColor = pjYellow        End If    End If End Sub

  • Anonymous
    February 11, 2010
    I am looking for an after task change event. Actually PMs here want to highlight a task to amber color at the same instant when its 100% completed. I tried it with before task change event but that that does so one instance later meant when its 100% complete and again i put something out there.......Is there any way using those combination of events i would be able to get the color change instantaneously at the same time i put 100% out there.....Plz do reply.....

  • Anonymous
    December 22, 2012
    This is exactly what I need but for Project 2010, I can't use it in my application because I get "user-defined type not define" error.