Search code examples
vbaonchangems-project

Microsoft Project VBA to update Custom field on task change

I have been wracking my brain trying to work out how to write a small piece of code that will activate only when particular fields at a task level have been modified.

I tried to make this code work at the project change level with a for each loop and select cases but that lags the whole program and still doesn't give me the result I need. I also tried to make it work when run manually with a for each loop and select cases or a bunch of If statements, but again, it can't tell me which field changed, but it can highlight a discrepancy between two fields.

The goal is to have a change log field (Text10) that auto updates based on the field that is modified and the date of the change. I only care about 4 fields changing (Date1, Date2, Date3, Date4).

e.g. If [Date1] is modified, Text10 = "Date1 modified 10/11/21"

Note: If 2 fields are modified, I would be happy enough with just listing the last one.

I was hoping there was some sort of "On Change, If Target = xxx" but I have not been able to find anything like that.

I also tried implementing the code as defined here >> Microsoft Documents: Project.Change Event but I am unclear what this is supposed to do and couldn't actually see it doing anything / I never got the message box I believe was supposed to appear.

I am using Microsoft Project Standard 2019.

Solution

  • After much research and trial and error, I ended up solving this.

    To get it working, I added a Class Module and ran a piece of code on open to initialize it. This essentially tells Project to start watching for events. I then use the "Field" variant to fill the field name amongst the text string and "NewVal" variant to fill the result. This was an easy solution in the end. The code I found that worked is below:

    In Class Module "cm_Events"

    Public WithEvents MyMSPApp As MSProject.Application

Private Sub Class_Initialize()

    Set MyMSPApp = Application

End Sub


Private Sub MyMSPApp_ProjectBeforeTaskChange(ByVal tsk As Task, ByVal Field As PjField, ByVal NewVal As Variant, Cancel As Boolean)

    'What you want the code to do
 
End Sub

    In Module "m_Events"

    Public oMSPEvents As New cm_Events
Sub StartEvents()

    Set oMSPEvents.MyMSPApp = MSProject.Application
    
End Sub

    In ThisProject code

    Private Sub Project_Open(ByVal pj As Project)

    Call m_Events.StartEvents 

End Sub