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:
Public WithEvents App As ApplicationPublic 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:
Dim X As New EventHandlers
Sub Initialize_App() Set X.App = MSProject.Application Set X.Proj = Application.ActiveProjectEnd SubThis will setup the event handler to fire before a task is changed.
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:
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.
The other day I had a request from an internal customer that wanted to create deliverables for a large
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
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.....
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.