MS Project 2003 - VBA - Date and Time issue

[petitboeuf]

Hiya Experts

I am trying to do a simple macro that will colour my tasks based on
completed and date

Code
-------------------
Sub HighlightTasks(

Dim t As Task, ts As Task
Dim dEnd As Dat

iDate = InputBox("Please set the date", "Date Input", "dd/mm/yyyy"
If iDate = "" Then Exit Su

Set ts = ActiveProject.Task

For Each t In t

progress = t.PercentComplet
dEnd = t.Finis

If progress > 1 The
SelectRow Row:=t.ID, RowRelative:=Fals
Font Color:=pjGreen, Size:=
End I

If progress = 100 The
SelectRow Row:=t.ID, RowRelative:=Fals
Font Color:=pjBlue, Size:=
End I

If dEnd < iDate The
SelectRow Row:=t.ID, RowRelative:=Fals
Font Color:=pjRed, Size:=
End I

If dEnd > iDate The
SelectRow Row:=t.ID, RowRelative:=Fals
Font Color:=pjBlack, Size:=
End I

Next

End Su
-------------------

My issues are that when I run the code I get iDate to read "18/12/2008
for example and t.Finish reads 21/01/2009 09:46:00 for example..

So as they don't have the same format I cannot match them..

*Edit* PercentComplete does not seem to work either... it change
colours of random tasks regardless of %..

I know the code is simplistic and hopefully you guys can help out

Many thanks in advance

 

[Jim Aksel]

Your input date is the same as the status date, I presume. You can set that
under Project/Project Information...

Insert the "Status" column. Tasks will be graded as "Complete", "In
Progress", "Future Task" and "Complete" depending on the Status Data and
%Complete.

I question your first test --- If %Complete>1 then show green. Why? Suppose
the task should be 90% complete as of the date you input, it should be red
then correct?

Please check my blog, hit "MS Project Tips" on the left side and read the
first one. I am working on a big paper on this topic, but it is not
complete yet. Please keep checking the blog for updates.
--
If this post was helpful, please consider rating it.

Jim Aksel, MVP

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

 

[Jan De Messemaeker]

Hi,

If you declare idate as date the comparison will work

Who says ID is alwaus equal to the row number? You may have collapsed
summary tasks, sorted tasks, etc.
Better start with selectbeginning then in the loop select row 1 then use
activeselection.tasks(1) to get %complete and finish.
HTH






--
Jan De Messemaeker
Microsoft Project Most Valuable Professional
+32 495 300 620
For availability check:
http://users.online.be/prom-ade/Calendar.pdf

 

[petitboeuf]

Thanks for that. I'm trying to combine both info then...


Code
-------------------
Sub HighlightTasks()

Dim t As Task, ts As Tasks
Dim dEnd As Date
Dim iDate As Date

iDate = InputBox("Please set the date", "Date Input", "dd/mm/yyyy")

SelectBeginning

Set ts = ActiveProject.Tasks

For Each t In ts

dEnd = t.Finish

If dEnd < iDate Then
SelectRow Row:=t.ID, RowRelative:=False
Font Color:=pjRed, Size:=8
Else
If t.Status = pjComplete Then
SelectRow Row:=t.ID, RowRelative:=False
Font Color:=pjBlue, Size:=8
End If
End If

If dEnd > iDate Then
SelectRow Row:=t.ID, RowRelative:=False
Font Color:=pjBlack, Size:=8
End If

Next t

End Su
-------------------


This seem to work better. However my uselessness at VB programmin
means that I cannot manage the code that says make the text red if we’r
late unless the task is complete then make it blue…

Any pointers