VBA code needed for a customized field

[ProjectUser]

Can someone help me with VBA code that involves a customized date field that
takes it value from a specific task’s completion date, but only displays the
customized date field at the outline 2 level?
Thank you,

 

[John]

Can someone help me with VBA code that involves a customized date field that
takes it value from a specific task’s completion date, but only displays the
customized date field at the outline 2 level?
Thank you,

ProjectUser,
When I think of a customized field, I think of a spare field that has
been customized with a formula, indicator or value list and that
customization will apply to all task rows. So in my mind you really
don't want a "customized" date field.

Without knowing a little more about which tasks are to be included, and
what the desired formula is, it is kind of difficult to even show pseudo
code. If you can give us some more details, maybe we can help.

John
Project MVP

 

[ProjectUser]

Hi John,
Yes, I really do need to have a customized field in this case. Here’s why:
I’m on a construction project that has multiple sites with identical WBS and
tasks across all sites. So, Outline 2 is the Site Name. Within this
schedule that’s repetitive, per site, is a task (design accepted) that will
trigger a “milestoneâ€.
I need to develop a view “report†at the Outline Level 2 level that looks
similar to an excel spreadsheet. In other words, it report will show no
tasks, only the site names (outline level 2) along with a number of fields.
So, this triggered “milestone†is the customized field I’m referring to. I
need it to have the same value as the completion date of a task.

Does this make it clearer?

Here's my feable attempt that certainly doesn't work. Maybe you can help me
fix this:

Sub Custom()

Dim tsk As Task
Dim assgn As Assignment

For Each tsk In ActiveProject.Tasks

If tsk.Name = "Design Accepted" And tsk.Duration = "100%" Then
assgn.Date1 = (here's where I'm totally clueless!)


End If
Next tsk

End Sub




Thanks,

 

[John]

Hi John,
Yes, I really do need to have a customized field in this case. Here’s why:
I’m on a construction project that has multiple sites with identical WBS and
tasks across all sites. So, Outline 2 is the Site Name. Within this
schedule that’s repetitive, per site, is a task (design accepted) that will
trigger a “milestoneâ€.
I need to develop a view “report†at the Outline Level 2 level that looks
similar to an excel spreadsheet. In other words, it report will show no
tasks, only the site names (outline level 2) along with a number of fields.
So, this triggered “milestone†is the customized field I’m referring to. I
need it to have the same value as the completion date of a task.

Does this make it clearer?

Here's my feable attempt that certainly doesn't work. Maybe you can help me
fix this:

Sub Custom()

Dim tsk As Task
Dim assgn As Assignment

For Each tsk In ActiveProject.Tasks

If tsk.Name = "Design Accepted" And tsk.Duration = "100%" Then
assgn.Date1 = (here's where I'm totally clueless!)


End If
Next tsk

End Sub

ProjectUser,
I think you misunderstood my statement about custom fields. To
differentiate spare fields whose contents is developed by a formula
versus VBA code, I refer to the former as a customized field. It's a
fine point, probably not worth further discussion.

I take issue with your statement about, "... identical WBS and
tasks...". If the tasks are being performed at different sites, they are
NOT identical. They may have the same description (although they should
not), same work content, same duration, etc., each one is in fact unique
simply because they are at different sites. For example, painting house
"A" is NOT the same task as painting house "B".

OK, enough clarifying philosophy. Based on your description why not
simply create a filter the looks for outline level 2, name contains
"design accepted" and percent complete, (I assume your statement about
duration equalling 100% is not what you meant), equals 100 and you
should be all set. No VBA required. Am I missing something?

John
Project MVP

 

[ProjectUser]

The filter won't work because the design accepted is a task below the site
name (level 2). Sorry, I'm trying to make this complicated.

Is there a way to create a customized date field who's date is driven by the
completion of a task, but only report this customized date at level 2?

 

[Jack Dahlgren]

There is a way to do this. But it will require the use of several fields.
Use one field to determine if the task is a level 2 summary
Use another field to uniquely identify the task which is driving the date
Use that field to set the value of the date. All tasks except the one you
want to report get a value either very large or very small - depending on
what you want to do. The task you do want to roll up uses the actual date.
Use an iif() statement for this. Then for the summary task roll up the min
or max for that field.
Finally use another field which uses an iif statement based on the first
field and the third field which will display the value only if the task is a
summary level 2.

-Jack

 

[John]

The filter won't work because the design accepted is a task below the site
name (level 2). Sorry, I'm trying to make this complicated.

Is there a way to create a customized date field who's date is driven by the
completion of a task, but only report this customized date at level 2?

ProjectUser,
I wouldn't say you are making it complicated, I'd say it's more a matter
of having difficulty explaining exactly what you want. Here is what I
preen from your latest post - correct me if I've got it wrong. You would
like to detect if the "design accepted" task under each site is 100%
complete. If it is, you want to put the completion date in a spare date
field at the site summary level which is outline level 2.

Jack's approach probably works, I didn't try it, but it definitely is
complicated - just like you wanted. However, since you posted to this
newsgroup and your "going in" approach was to use VBA, try this code and
see if it does what you need.

Sub SiteReady()
Dim t As Task
For Each t In activeproject.Tasks
If Not t Is Nothing Then
If InStr(1, t.Name, "design accep") > 0 And t.PerCentComplete =
100 Then
t.OutlineParent.Date1 = t.Finish
End If
End If
Next t
End Sub

John
Project MVP

 

[ProjectUser]

John & Jack,

Thanks for your replies!

There is a way to do this. But it will require the use of several fields.
Use one field to determine if the task is a level 2 summary
Use another field to uniquely identify the task which is driving the date
Use that field to set the value of the date. All tasks except the one you
want to report get a value either very large or very small - depending on
what you want to do. The task you do want to roll up uses the actual date.
Use an iif() statement for this. Then for the summary task roll up the min
or max for that field.
Finally use another field which uses an iif statement based on the first
field and the third field which will display the value only if the task is a
summary level 2.

-Jack

 

[Jack Dahlgren]

I agree with John that the VBA solution is easier and more elegant.

-Jack

 

[John]

John & Jack,

Thanks for your replies!

ProjectUser,
You're welcome and thanks for the feedback.
John