Problem running Project Macro VBA

[VaReTaS]

Hi,

I'm trying to make a macro that automatically assigns resources to tasks,
depending on the values of two custom text fields.

Imagine, that in the Gantt Chart view I've two custom fields, "text1" and
"text4", and the same thing in resource view, so if I put in the gantt view
text1="E1", and text4="F1" (or anything else), the macro should assign to
each task the resources that have text1="E1" and text4="F1" (or anything
else, depending on the text), based in the resource list field.

But everytime I tryed to ran the macro, the Microsoft Project crashes itself
for a long time and stop running.

The code i'm usign is this one:

--- code ---

Sub AutoAssign()
Dim tskT As Task
Dim rcsR As Resource
Dim asnA As Assignment
Dim lngUnits As Long
Dim blnFound As Boolean

lngUnits = 1

For Each tskT In ActiveProject.Tasks
If Not (tskT Is Nothing) Then
For Each rcsR In ActiveProject.Resources
If Not (rcsR Is Nothing) Then
If (rcsR.Text1 = tskT.Text1) And (rcsR.Text4 = tskT.Text4)
Then
For Each asnA In tskT.Assignments
If asnA.ResourceID = rcsR.ID Then
blnFound = True
End If
Next asnA

If blnFound = False Then
tskT.Assignments.Add TaskID:=tskT.ID,
ResourceID:=rcsR.ID, Units:=lngUnits
Else
blnFound = False
End If
End If
End If
Next rcsR
End If
Next tskT
End Sub

--- End Code ---

I don't know what is happening but it crashes everytime, in MS Project 2003
and even in MS Project 2007.

I hope someone can help me figuring it out, cause I don't know what to do
more and how can I solve this.

Thanks in advance,

VaReTaS

 

[Jan De Messemaeker]

Hi,

I ran the code without any problem.
Still there are a few questions I have.
Are you aware that when Text1 and Text4 are empty, both for the task and for
a resource, the resource is assigned?
This being said, do you have any external tasks in your project? You can't
assign resoruces to external tasks.

Hope this helpe,

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

 

[VaReTaS]

Hi,

I haven't though that way, maybe because I've those fields empty, in most
tasks I tried, it takes much longer to assign all resources to those tasks
that doesn't have anything it the custom text field.

I'll try to set it all well and then run again the macro.

Thanks a lot.

VaReTaS

 

[VaReTaS]

Hi,

Finally I found the problem and, has you said, it was in the empty text
fields, so MS Project takes a long run to assign all my hundreds of resources
to my hundreds of tasks.

Now it works all fine.

By the way, do I have a way to have the macro always running despite of have
to run it everytime I need? Meaning that everytime I put some text in text1
and text4 it assigns resources to tasks?!

I almost forgot to ask something else... once I run the macro it assigns the
resources to tasks depending in the text I've in the text1 and text4 fields,
but if I change the text in the custom fields it doesn't update the
assignments to the tasks, leaving the assigns entered before. Do I've a way
to correct this?!?

And once again thanks a lot for the help...

VaReTaS

 

[Jan De Messemaeker]

Hi,

Two questions.
First, you could code the macro in an event (Projectbeforetaskchange) but on
top of he fact that working with events isn't that obvious, here your code
will have to be very delicate since (a) you have to check two fields - the
event will fire already on the first change!) and the action taken (assign a
resource) will again fire the event... possible but not easy to control.
An alternative is to run the macro in a Project event (Before Save, before
close or Open) where you are sure it won't get into a loop.

About deleting other assignments: can be doine but you must be absolutely
sure about the logic. If Text1 and/or Text4 have changed, do you want to
delete all assignments before assigning the new ones? And if no, which ones
would you delete? How to identify them?

Hope this helps,

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