Status VBA help

[Chris]

Hey all...


Piling on the questions - sorry.

I want to add some VBA which runs through each task / line in the plan and
looks at what should have started or finished and if late turns the task
line red...

Can anyone point me in the right direction please...

Chris

 

[Dale Howard [MVP]]

Chris --

Your best bet would be to post this question in the
microsoft.public.project.developer newsgroup, so our VBA specialists can
assist you. Hope this helps.

 

[akphidelt]

You have to loop through the tasks. This is untested and just off the top of
my head but it should bring you in the right direction

Dim tsk as Task
Dim mydate as Date

for each tsk in Tasks
if Not tsk Is Nothing Then
if tsk.start < tsk.actualstart Then
tsk.fontcolor = red
End If
End If
next tsk

Once again, very bad code, you will have to auto fill the correct
start/actualstart names... and figure out the fontcolor code for red.

But basically that shows you how you can loop through the tasks.

 

[Jim Aksel]

Easy, you hire me since I have a macro that does it (hee hee)

Here is some guidance. First, since this is a VBA type question you should
be posting on our sister group microsoft.public.projectdeveloper. Happy to
help here, but please post additional questions over there.

You need to deceide on the rules you want to apply beofre you change the
line. Here is some of what I use
(Foregive me on the syntax, you will get the idea)

Dim myProj as ActiveProject
DIm pTask as Task
For each pTask in myProject.Tasks

If not pTask is nothing then (don't check blank lines)
If pTask.External=False (don't check external lines in the file)
If pTask.Summary=False (you probably want to skip summaries for what you are
requesting)

'These are all separate stand alone IF-THENs...
If pTask.Start< myProj.Status Date .... it should have started, check for
[Actual Start]
If pTask.Finish<myProj.Status Date .... it should have finished, check for
[Actual Finish]
If pTask.Start <=myProj.StatusDate AND pTask.Finish>=myProj.StatusDate
....update %Complete needed

Check there are not [Actual Start] or [Actual Finish] greater than the
status date (Nothing happens tomorrow)

End If 'Summaries
End If 'Externals
End If 'Task is nothing

Next pTask


Closing Comment on "If it is late..." I have some rants about this on my
blog... you should be looking at SPI. However, if you are using %Complete
for progress then use pTask.Status which returns an integer value if the
task is Late, On Schedule, Complete or Future Task.

The members of pjStatusType are:

pjComplete = 0
pjFutureTask=3
pjLate=2
pjNoData=4
pjOnSchedule=1

HTH


--
If this post was helpful, please consider rating it.

Jim Aksel, MVP

Check out my blog for more information:
http://www.msprojectblog.com

 

[JulieS]

Hi Chris,

The problem with a VBA solutions is you'd need to run it to update
the colors. Consider creating a custom formula in the flag field to
test whether the task is late (you'll first need to define what you
mean by late). Once you have the flag set correctly, create a
custom bar style to show the late tasks. The advantage with this
approach is that once the project calculates, the bar colors/styles
update automatically. No need to remember to run the macro again.

This won't change the font color but you could then create a custom
filter and set the highlight to change font color.

I hope this helps. Let us know how you get along.

Julie
Project MVP

Visit http://project.mvps.org/ for the FAQs and additional
information about Microsoft Project

 

[Chris]

Thanks all...

Firstly sorry for wrong group posting...

I'll start having a go and will come back with my results

Chris

 

[JulieS]

No trouble Chris. I see you've re-posted to the developer
newsgroup.

Julie

 

[Chris]

Hi all.

I have posted to developer but will also finish in this group.

I have tried the following but to no avail - it does not like the color
formatting. Advice appreciated.

Sub updates()

'Dim myProj As Project
Dim pTask As Task
Dim myDate As Date
myDate = Date


For Each pTask In ActiveProject.Tasks


If Not pTask Is Nothing Then
'If pTask.External = False Then '(don't check external lines in the file)
'If pTask.Summary = False Then '(you probably want to skip summaries for
what you are
'requesting)

If pTask.Start < myDate And pTask.PercentComplete = "0" Then '.... it should
have started
pTask.FontColor = red
End If
'If pTask.Finish < myDate Then 'Date .... it should have finished
'pTask.FontColor = red
'End If
'If pTask.Start <= myDate And pTask.Finish >= myDate Then
'pTask.FontColor = red
'End If


'End If 'Summaries
'End If 'Externals
End If 'Task is nothing

Next pTask

End Sub