« Securing Your MS Project Files and Macro Code | Main | Just needs a little dog to ride on the tank »

Working with Task and Assignment Fields VBA

One common problem people face with project is that there are three classes of custom fields; task fields, assignment fields and resource fields. If you are in a resource view and you are looking at the Text1 field it won't have the same information as if you are looking at the Text1 field in a task view. This is true with reports as well. The solution is to copy over the items from the one field to the other. This is painful unless you automate it. So, to reduce the pain here is VBA code which does it for you:

Sub CopyTaskFieldToAssignment()
'This macro copies information in the task text5 field
'into the assignment text5 field so that is can
'be displayed in a usage view or in a report.
'Modify the line noted below to fit your needs
Dim t As Task
Dim ts As Tasks
Dim a As Assignment
Set ts = ActiveProject.Tasks
For Each t In ts
If Not t Is Nothing Then
For Each a In t.Assignments
'change the following line to use
'for a different custom field
a.Text5 = t.Text5
Next a
End If
Next t
End Sub

Pretty easy. This one should have no problems because each assignment only has a single task that it references. However, going the other way could be a problem as each task can have several assignments. To sidestep the issue we can simply concatenate all of the text from all of the assignments. The code would then look like this:

Sub CopyAssignmentFieldToTask()
Dim t As Task
Dim ts As Tasks
Dim a As Assignment
Set ts = ActiveProject.Tasks
For Each t In ts
If Not t Is Nothing Then
t.Text5 = ""
For Each a In t.Assignments
'change the following line to use
'for a different custom field
t.Text5 = t.Text5 & ", " & a.Text5
Next a
End If
Next t
End Sub

The line t.Text5 = t.Text5 & ", " & a.Text5 appends whatever is in the assignment field to whatever is already existing in the task field.

Some simple modifications can make it work to copy from the resource fields.

RELATED POSTS

Comments (3)

Manuel Acosta:

Please.

Disculpe que le escriba en espaƱol, mi consulta es: como puedo poner el valor de una variable creada en visual basic (ejemplo: avance) en la barra de gantt, hasta ahora solo puedo hacerlo con los campos que tiene ms project, pero no encuentro la manera de asignarlo desde una variable personalizada.

Se puede realizar la operacion??

Le agradeceria alguna recomendacion o sugerencia.

Atte.

Manuel.

Brad Earle:

Is there a quick macro to copy the custom fields that were created in one project to another? Going through each field, importing, then changing the name to be the same is rather annoying.

It would seem that this would be a simple macro, yet I haven't been able to figure it out.

Thanks.

(Note from Jack: You can use the organizer to do this. Try recording a macro of moving the fields over and see what happens.)

Malcolm Manning:

Is it possible to copy the overallocation flag from the resource fields to the task fields. What I would like to achieve is highlighting in the Gantt view any tasks that have an overallocated resource assigned to it. I know this can be seen by opening a separate resource graph window but I would like to present it in a printed Gantt.
Thanks,

--------------------
Yes it probably is possible, but I'm not sure you would like the results. The general code would be to walk through all the tasks and then the resources on that task and flag any tasks which have an overallocated resource:


Sub resoverload()

Dim t As Task

Dim r As Resource

For Each t In ActiveProject.Tasks

t.Flag1 = False

For Each r In t.Resources

If r.Overallocated Then

t.Flag1 = True

Next Resource

Next Task

End Sub


Once you have this then you can filter or format based on Flag1. -Jack

Post a comment

(Comments are moderated to fight SPAM and will be published after I have a chance to approve them. Thanks for waiting.)

About

The previous article is Securing Your MS Project Files and Macro Code.

The next article is Just needs a little dog to ride on the tank.

Current articles are in the main index page and you can find a complete list of articles in the archives.

Creative Commons License
This weblog is licensed under a Creative Commons License.
Powered by
Movable Type 3.34