Automatically setting the task Publish field to No

G&T (Gordons) with a slice and ice….

If you’re using assignments for team members, you’ll have noticed that completed assignments still appear on the My Tasks page, even when the resource has updated the task and the task is 100% complete.  This causes confusion for resources, and consternation for clients in general.  Completed assignments disappear after the pre-requisite number of days (30 by default), but still…..

Typically the only way around this is to set the task Publish to No.  This can be done by inserting the Publish field to No for tasks that are 100% complete within the project plan, but the problem with this is that it isn’t very automatic.  The best you can get is to use the fill handle to drag the field down…..


So, I wrote some VBA to set this automatically.  Basically, if the Task Percent Complete = 100% then the Publish field is set to No.  Some of my customers don’t want to publish all tasks too far ahead (for reasons I won’t go into here), so I’ve only set the field to No if 100% complete, and not Yes if <> 100%.  Note that for tasks with multiple assignments, an assignment could be 100% complete, though the task could still be <> 100%, and there isn’t much we can do about it.

Here’s the VBA, it comes with no warranties.  You’ll either need to run the macro, or automate it using one of the events (change, calculate, save etc…) – I’ll leave you to figure that one out.  If you use it anywhere and you make some £ out of it, donate some to charity will you?  Today is 11/11/09 (Remberance Sunday in the UK) and there are plenty of people who gave their lives to help us, so do what you can.

Sub SetPublishField()

‘ 091111 – Ben Howard – Copyright.

‘ Sets the task Publish field to No if the % complete of the TASK is set to 100, otherwise, leaves it as previously set,

Dim Temp As Long

Dim Tsk As Task

For Each Tsk In ActiveProject.Tasks

‘ check to see if a task exists

If Not Tsk Is Nothing Then

‘ check to see if the task is external or a summary

If Not (Tsk.ExternalTask Or Tsk.Summary) Then

If Tsk.PercentComplete = 100 Then

Tsk.IsPublished = False

End If

End If

End If

Next Tsk

End Sub

Thanks to Jack Dahlgren and Rod Gill begin_of_the_skype_highlighting     end_of_the_skype_highlighting for their on-line and book help – okay so this took me less than 30 mins to prove the concept, but it would have taken a lot longer without their posts or books. 

Ben Howard, MVP