Selecting tasks with vba code

[Jurgen D]

C2M/GRAFAN 20 20 25 0 days 1 day? Wed 14/04/04
Manufacturing 0 days 1 day? Wed 14/04/04
task1 0 days 1 day? Wed 14/04/04
task2 0 days 1 day? Wed 14/04/04
task3 0 days 1 day? Wed 14/04/04

This is a small project within ms project. Now i want to write a some
vba code to calculate something automatically. The following has to
happen: when i fill
in a number next to a task:

C2M/GRAFAN 15 10 25 0 days 1 day? Wed 14/04/04
Manufacturing 0 days 1 day? Wed 14/04/04
task1 5 0 days 1 day? Wed 14/04/04
task2 5 0 days 1 day? Wed 14/04/04
task3 5 0 days 1 day? Wed 14/04/04

The program has to take the sum of everything beneath 20, 20 or 25 and
then substract it from those figures. there for i have to select the
last level tasks but how can you specify in vba that you wan the last
leven or top level tasks. Can you use groups? or how should i do this?

Any help is welcome

Thanks

 

[John]

Jurgen,
I'm sorry but I don't understand the example provided. Are the numbers
right after what appears to be the Task Name, stored in spare fields?
What does "beneath" mean? For instance, in the second example are the 5s
"beneath" the 15?

Regardless, to answer you question there are a couple of different ways
in VBA to identify the task level. The method I most often use is a test
on the Outline Level field. Another method is to use the OutlineChildren
or OutlineParent properties. It kind of depends on how you want to do it
and which method is easiest to program for your particular need.

John

 

[Jurgen D]

C2M/GRAFAN 20 20 25 0 days 1 day? Wed 14/04/04
Manufacturing 0 days 1 day? Wed 14/04/04
task1 0 days 1 day? Wed 14/04/04
task2 0 days 1 day? Wed 14/04/04
task3 0 days 1 day? Wed 14/04/04

This is a small project within ms project. Now i want to write a some
vba code to calculate something automatically. The following has to
happen: when i fill
in a number next to a task:

C2M/GRAFAN 15 10 25 0 days 1 day? Wed 14/04/04
Manufacturing 0 days 1 day? Wed 14/04/04
task1 5 0 days 1 day? Wed 14/04/04
task2 5 0 days 1 day? Wed 14/04/04
task3 5 0 days 1 day? Wed 14/04/04

 

[Jurgen D]

C2M/GRAFAN 20 20 25 0 days 1 day? Wed 14/04/04
Manufacturing 0 days 1 day? Wed 14/04/04
task1 0 days 1 day? Wed 14/04/04
task2 0 days 1 day? Wed 14/04/04
task3 0 days 1 day? Wed 14/04/04

This is a small project within ms project. Now i want to write a some
vba code to calculate something automatically. The following has to
happen: when i fill
in a number next to a task:

C2M/GRAFAN 15 10 25 0 days 1 day? Wed 14/04/04
Manufacturing 0 days 1 day? Wed 14/04/04
task1 5 0 days 1 day? Wed 14/04/04
task2 5 0 days 1 day? Wed 14/04/04
task3 5 0 days 1 day? Wed 14/04/04

 

[Jurgen D]

Sorry john, i'll try to correct my post and explain it correctly!

C2M/GRAFAN 20 20 25 0 days 1 day? Wed 14/04/04
Manufacturing 0 days 1 day? Wed 14/04/04
task1 0 days 1 day? Wed 14/04/04
task2 0 days 1 day? Wed 14/04/04
task3 0 days 1 day? Wed 14/04/04

This is a small project within ms project. Now i want to write a some
vba code to calculate something automatically. The following has to
happen: when i fill
in a number next to a task:

C2M/GRAFAN 15 10 25 0 days 1 day? Wed 14/04/04
Manufacturing 0 days 1 day? Wed 14/04/04
task1 5 0 days 1 day? Wed 14/04/04
task2 5 0 days 1 day? Wed 14/04/04
task3 5 0 days 1 day? Wed 14/04/04

The code has to do the following:
in the first example you see 20 - 20 - 15
This is something i enter in the gantt chart.
When i enter i 5 somewhere under the first 20, the 20 has to change to 15.
of when i enter a double 5 under the second 20, the second 20 has to change to 10.

So what i don't know:
How can i create code that always runs when i change the figures
In the current project you see c2m/grafan - manufacturing and tasks
but it also can be c2m/grafan - manufacturing - project1 and then the tasks.
My code also have to work here.
What methods could i use in order that my code always works.

Thank you very much for your help. it could be my jumpstart for my career.

 

[John]

Jurgen,
I guess we see different things. You said the first example shows
20-20-15. I see 20-20-25. The data may be entered while the Gantt Chart
view is active, but into what fields (columns) is the data entered?

If you needed to simply sum up the subtask values to the summary line,
it could be easily done with a simple formula in a custom number field
(e.g. Number1). However, you apparently want to do the reverse and
unless a very clever formula can be created, VBA appears to be needed.

There are two ways to have the values calculate when a change is made.
One is to write a simple macro that is manually run from a toolbar
button after all the changes have been made. The second is to develop an
autorun macro that detects a Change Event. Either macro can be used on
any file.

John

 

[Jurgen D]

John,

You were write we see 20 - 20 - 25. I understand that i need vba but
my problem is that i don't know how many subtasks there are. Is there
a function in vba to get the deepest subtasks because only those i
have to sum up. Wich functions in vba should i use??

Greetzz

 

[John]

Jurgen,
As I mentioned there are a couple of ways to track subtasks (i.e.
"children") and depending on the structure of your file one method may
be easier than the other. And, if the file is a master file with
subprojects, it first needs to be "exploded" to ensure all levels of
subtasks are available.

Try the method I would probably use. First "explode" the file. (If you
don't have a master with subprojects, you can ignore this statement).
Then, set up a loop to run through all tasks in the file. Something
similar to this:

Sub SubtractFromTotal()
outlineShowTasks expandinsertedprojects:=True
Dim t As Object
For Each t In ActiveProject.Tasks
If Not t Is Nothing Then
[your code here]
End If
Next t
End Sub

Within the loop, look for summary tasks. When found, store the outline
level as a variable. Then set up a test that subtracts the current
subtasks value from the summary as long as the outline level of the
current task is greater than the stored outline value. If you need to
expand the subtraction to carry through all subindenture levels, you
will need to set up the outline level variable as an array so you can
keep track of each summary outline level.

That's the basic structure. Hope this helps.
John