Help with VBA for showing project resources
-
jeudi 19 juillet 2012 11:12
Hi all,
I have not worked with VBA before and I am trying to create a local field which holds the current project team members. The idea is to allow the PM select one of the resources as the "Task Owner" for any task in the plan. I cannot use an enterprise lookup table since I need the field to get its values per project.
So I am actually using a local lookup field as a list value. Since it will not auto-update when e.g. I add a new resource or delete an existing resource, I will bind it to an event (e.g. Calculate). What I do is copy selected values to a temp field initially, delete the lookup contents and re-populate from "Resources", then copy back previously selected values from the temp field. It doesn't work as expected in all cases. Can anyone assist please or propose an alternative way to accomplish my requirement ?
Private Sub LoadProjectTeam()
Dim r As Resources, Temp As Long, tsk As Task, counter As Long
For Each tsk In ActiveProject.Tasks
If Not (tsk Is Nothing) Then
tsk.Text30 = tsk.Text2 'copy selected values to the temp text30 field, text2 is a local lookup field
End If
Next tsk' Need to empty the lookup since new resources may have been added or existing ones have been deleted
On Error GoTo ErrorHandler1
Do While CustomFieldValueListDelete(pjCustomTaskText2, 1) = True
Loop' Weird, the above code not only deletes value list but also selected values in text2...
ErrorHandler1:
Resume Label1Label1:
Set r = ActiveProject.ResourcesOn Error GoTo ErrorHandler2
For Temp = 1 To r.Count 'add current project resources to lookup
If (CustomFieldValueListAdd(pjCustomTaskText2, r(Temp).Name) = False) Then
MsgBox "Error determining project team"
End If
Next TempErrorHandler2:
Resume Label2Label2:
On Error GoTo ErrorHandler3
For Each tsk In ActiveProject.Tasks
If Not (tsk Is Nothing) Then
If (tsk.Text30 <> "") Then
tsk.Text2 = tsk.Text30 'copy back from the temp field the previously selected values
End If
End If
Next tskErrorHandler3:
Resume Next
End Sub- Modifié tasaras jeudi 19 juillet 2012 11:14
Toutes les réponses
-
vendredi 20 juillet 2012 03:55Modérateur
If the default only items in liost allowed, then if you delete the selected item it will delete the selected value. So, if the selected value is for an existing resource, don't use it.
You also then need to ignore this resource so you don't ad it again.
Rod Gill
The one and only Project VBA Book
Rod Gill Project Management
-
vendredi 20 juillet 2012 06:53
Thanks Rod.
I checked the option to allow values outside the list, but nothing changed: selected values are always deleted as soon as the list is emptied (CustomFieldValueListDelete)...
I will give it another try....
Tassos
-
vendredi 20 juillet 2012 20:28ModérateurAh well, this isn't a biggie as saving the value in a Text field first isn't hard!
Rod Gill
The one and only Project VBA Book
Rod Gill Project Management
-
samedi 21 juillet 2012 09:06
Thought so too, however this simple line (taken from my code above) does not always return the saved value; sometimes it just returns an empty string :(
tsk.Text2 = tsk.Text30 'copy back from the temp field the previously selected values
