Developer

Monitoring task changes in Project 2000

Take advantage of the new tools in Microsoft Project 2000. This step-by-step guide will show you how to write a macro that will prevent unauthorized editing of your project.


The coolest Project 2000 features you have never heard of are the new application level events. These new events allow you to place code in your projects to respond to such things as the creation, deletion, or change of a task, resource, or assignment.

Now this might not sound very exciting until you realize it enables you to write VBA code that can effectively prevent your project from being edited unless the user enters a password. This wasn’t possible in Project 98. We will be building and installing such a macro in this article.

The basics of the application event
The application level events we will be using to monitor task changes are different from the events Project 98 users are familiar with. In 98, the events were all listed in the VBA editor automatically, and you could choose the one you wanted to place code into. With the application events, there is a little more to it.

Before you can make use of these events, you must first create a class module in the VBA editor. To do this you:
  1. Open the VBA editor.
  2. In the Project Explorer window, select the project into which you want to insert the application events.
  3. From the Insert menu, select Class Module.
  4. In the Properties window, click on the box to the right of Name and type Change.

Into this class module, type the following code:
Public WithEvents App As Application

This declares the variable App as an Application object and enables event procedures to be written within it. These are the new events we want to access to monitor things like changes to tasks.

The next step is to initialize the class module so that Project knows to “watch” the application events. This is done with the following code, which can be placed into any module in a project. For this example, we will be placing it in the Project_Open event so that whenever the project is opened, it will initialize the application events. To do this, follow these steps:
  1. In the Project Explorer, double-click on the Microsoft Project Objects folder for the project where you placed the class module.
  2. Double-click on ThisProject(name) (where name is the name of your project).
  3. In the code window, enter the following code:
Dim TChange As New Change
Private Sub Project_Open(ByVal pj As Project)
 InitApp
End Sub
Sub InitApp()
 Set TChange.App = Application
End Sub
Sub ExitApp()
 Set TChange.App = Nothing
End Sub

In the Dim line, we are declaring a variable called TChange to represent a new instance of the class module we created above, Change.

Project_Open is a project level event that runs whenever the project is opened. Into this event, we have placed a call to the InitApp procedure. The InitApp procedure contains the code that actually “turns on” the application events. Once this code is run, your project will be monitoring the application events.

The ExitApp procedure ends the monitoring of these events until the project is opened again.

The stage is now set for creating the code that will respond to changes in your tasks.

The code
The code we will be covering here will monitor changes made by users to fields in the tasks of the project. If the code detects an edit, it will ask the user for a password. If the user enters the correct password, he or she is free to make edits to the project for the rest of the time the project is open. In essence, the monitoring of the task edits is turned off. The monitoring will be turned back on the next time the project is opened. If the password is incorrect, the code will display a message telling the user that the password is wrong and that the edit will not be allowed.

Remember that any line that begins with a single quote mark ( ‘ ) is a comment. The code below contains comments that describe the different sections of the code. I put these descriptions in the code rather than in the text of this article so that they will be easy to find after you have pasted this code into your project, but you can remove any lines with the single quote. Doing so will not affect how the code runs.

You should paste the code into the class module called Change (the module we created at the beginning of this article). Paste it below the line Public WithEvents App As Application.

This macro is an example of a function, which is code that returns a value to the code that calls it. In this case, it will return a value that determines whether the edits a user has made will be allowed or cancelled.
Function MonitorChanges()
'This function will prompt the user for a password
'If correct the monitoring of application events will stop and MonitorChanges
‘will return FALSE thereby allowing the edit
'If wrong then MonitorChanges will return TRUE and will cancel the edit
 
'Variables Declaration
 'bolLockAll is a Boolean value (Yes or No) that will be used to control if
 'this macro should continue
 Dim bolLockAll As Boolean
 
 'strPassword is a String value (any alpha-numeric data) used for the password
 Dim strPassword As String
'Initialize Error Handling
 On Error GoTo ErrorHandler
'Enable Locking?
 bolLockAll = True
 'If bolLockAll is True then the macro will function normally
 'If bolLockAll is False then the macro is basically turned Off
 
 'You set this to False if you wanted to turn off this macro without having
 'to delete the code from the project. There might be times when you would want
 'the 'locking' of the project for a short time. Setting bolLockAll to False
 'will allow you to do this without deleting the code.
'Set Password
 strPassword = "editsok"
 'This is the text that the user will need to enter in order
 'to be able to edit the project. By default it is editsok
 
'Check to see if bolLockAll is True
If bolLockAll = True Then
 'Ask for Password and test it against strPassword
 If InputBox(Prompt:="Enter Edit Password", _
 Title:="Project Edits Locked") <> strPassword Then
  'If password is wrong then tell the user
  MsgBox Prompt:="Incorrect Password, Edit Disallowed", _
  Buttons:=vbCritical
  'Cancel the edit they made
  MonitorChanges = True
  'Setting MonitorChanges to True will cancel the edit in the
  'event procedure.
 Else
  'If the password is correct then turn off monitoring so
  'that the user will not be prompted for password for every edit
  ThisProject.ExitApp
  'ThisProject.ExitApp is a call to the ExitApp procedure you placed
  'into the ThisProject object. ExitApp sets the TChange object
  'to Nothing, clearing it so that event monitoring stops.
 
 End If
End If
'Error Handling Routine
'***********************************************
***********************

'This will watch for errors and end the macro smoothly without the
'standard error messages.
'Exit Function stops the code from getting to the error handler unless there is an error
Exit Function
ErrorHandler:
 'Notify user of the error
 MsgBox Prompt:="There has been an error in the Event Code.", _
 Title:="Event Error", Buttons:=vbExclamation
 
 'Re-initialize the Event Object
 ThisProject.InitApp
 'This is a call to the InitApp procedure you placed in the ThisProject
 'object. It is the routine that initializes the monitoring of events.
'***********************************************
*************************

End Function

Next, you must place a call to this code in the BeforeTaskChange event so that the function is run each time a change is made to task data. To do this, follow the steps below.
  1. In the VBA editor, double-click on the class module Change in the Project Explorer.
  2. In the Code window, select App from the Object drop-down list (the one on the left).
  3. From the Procedure drop-down list (the one on the right), select BeforeTaskChange.

After you do this, you will see the following lines appear:
Private Sub App_ProjectBeforeTaskChange(ByVal tsk As Task, ByVal Field As PjField, ByVal NewVal As Variant, Cancel As Boolean)
End Sub

It is in this private subroutine that you will place the call to the MonitorChanges function. The call should look like this:
 Cancel = MonitorChanges

This will make the Cancel argument of the event equal to the output from the MonitorChanges function. This output will be True if the password provided is wrong and False if the password is correct. True will cancel the edit, and False will allow it.

Once you’ve added this call to the BeforeTaskChange event, saved the project, closed it, and reopened it, the code will start working.

Locking your VBA code
The above code will help stop users from editing task data in your project without the password. However, a knowledgeable user could go into the VBA editor and disable this macro. Fortunately, you can protect your VBA project so that a password is needed to edit or even view the code. To do this, follow these steps:
  1. From the VBA editor, right-click on the class module Change.
  2. From the shortcut menu, select VBAProject Properties.
  3. On the Protection tab, check the Lock Project For Viewing check box.
  4. Enter a password and verify it in the boxes at the bottom of the tab.
  5. Click OK.

Now only users with the password you just entered will be able to view or change your code.

Possible additions
The work we have done so far will prompt for a password when a change is made to task data in your project. But what about the changes that might be made to resource or assignment information? What we have done so far will not monitor changes to those types of data. Luckily, the call to the MonitorChanges function we have entered into the BeforeTaskChange event can be pasted directly into the BeforeResourceChange and the BeforeAssignmentChange events to monitor those changes as well. You can add these events by selecting them from the Procedure drop-down list box in the Code window just as you did for BeforeTaskChange.

You may also want to monitor additions and deletions to tasks, resources, and assignments just as you monitor the changes. You can find these events (BeforeTaskAdd, BeforeResourceDelete, and so on) on the Procedure drop-down list in the same place as the events discussed above. In each case, just add the following line to these events so that the user will also be prompted when taking the associated action:
 Cancel = MonitorChanges

Important note
This procedure won’t completely disallow changes—it will disallow only the ones the BeforeTaskChange event sees. Certain edits can be made to a project that these events won’t see. The Project 2000 VBA Help file mentions these exceptions:

“Microsoft Project events do not occur when the project is embedded in another document or application.

“The BeforeTaskChange event doesn't occur when timescaled data changes, when constraint data in the Task Details Form changes, when a task is split by manipulating its task bar on the Gantt Chart, when changes are made to outline level or outline number, when a baseline is saved, when a baseline is cleared, when an entire task row is pasted, during resource pool operations, when inserting or removing a subproject, or when changes have been made using a custom form.”

This means that some project changes won’t be “caught” by the macro we have created above. But it will work for the vast majority of changes.

The biggest benefit of this code is what you have learned from it. You now have a set of macros that will monitor changes to your project and protect most of your work. More importantly, you’ve gained a basic understanding of how these new events work, and you can take that understanding and apply it to other uses. In an upcoming article, we will expand on this knowledge to protect only certain fields from being edited in a project instead of the blanket protection we implemented today.

Brian Kennemer is a Microsoft Project MVP and program manager for Pacific Edge Software. Brian has been using and supporting Project for five years. He writes and speaks for a number of organizations on Project and its use. Brian lives near Seattle.

Every month, Brian Kennemer writes about Microsoft Project in IT Manager Republic. Send a letter if you’d like to ask a Project question or post your comments below.

Editor's Picks

Free Newsletters, In your Inbox