Project 2007 - Custom field forumla not recalculating on open

[SPD999]

Hello All,

New to MS Project so please be gentle . I have added a custom column with a forumla which is suppose to warn me if a task assigned to my department is in danger of being overdue, via graphical indicators. The problem I am having is that the forumla doesn't seem to 'recalulate' unless I make a change to the columns in the forumla. However the forumla is based on the Start/Finish date of the task which does not get changed. My hope was that everytime I opened the project the forumla would recalculate based on today's date and update the value. Again it works fine if I change any of the values in the columns refered to in the formula.

For completetion sake I below is the formula and basically what it is suppose to do:

1. Set Value to > 0 (1 initially) if the Resource has a specific string in it (DBS) that hasn't been complelted yet
2. Set Value to 2 if task hasn't started yet and start date is within 2 days but not past the finish date
3. Set Value to 3 if isn't complete and finish date is prior to today

IIf(InStr([Resource Names],'DBS')=0 And InStr([Resource Names],'All')=0 Or [% Complete]=100,0,IIf(([% Complete]<100 And [Start]>Now()+2) Or ([% Complete]>0 And [Start]<=Now() And [Finish]>Now()),1,IIf([% Complete]=0 And ([Start]=Now() Or [Start]=Now()+1 Or [Start]=Now()+2) Or ([Start]<Now() And [Finish]>Now()),2,IIf([Finish]<Now() And [% Complete]<100,3,4))))

Thanks
Steve

 

[JulieInMaine]

Hi Steve,

Project does not calculate automatically on open and your reference to Now()+2 is likely what is causing the issue. You can manually recalculate on open with F9 to recalculate the formula. You could also add a simple macro to the on open event to calculate all.

Obviously the F9 command is easier

I hope this helps.

Julie

 

[SPD999]

Hello JulieInMaine,

Thanks for the response, that does help. Can't beleive after all my googling with MS Project + Recalculated I didn't get 1 hit for F9. The developer in me does like triggering recalc in open event, don't suppose you would have the code i would need to do that would you?

Thanks again
Steve

 

[JulieInMaine]

Hi Steve,

If you turn on the Macro recorder and press F9 it will give you the one line code "CalculateAll"

Julie

 

[SPD999]

Thanks Julie!

 

[SPD999]

Hi Juile,

Sorry I am back again. I searched around and found this example on the MSDN site which is suppose to display a message when the project is opened. But nothing happens, just wondering if there is something I am missing. I added this to the project by going into the Visual Basic Editor -> VBAProject -> MSft Project Object -> ThisProject - double-clicking to get code window and adding. When I open the project nothing happens

Sub Project_Open(ByVal pj As Project)
If (pj Is Nothing) Then
MsgBox "Called Project_Open from Global.MPT."
Else
MsgBox "Global.MPT: Opened project: " & pj.Name
End If
End Sub

Thanks
Steve

 

[JulieInMaine]

Steve,
Check your macro settings. Tools > Options, Security. Likely your macro settings are disabled without notification.

 

[SPD999]

Hello Julie,

Spot on, had to set to Low, but finally got it

Thanks Again
Steve

 

[JulieInMaine]

Great, glad you have it sorted out, Steve and thanks for the feedback.