Project VBA for indenting tasks

[wiwchar]

i have some familiarity with VBA for Excel. This is my first attempt at VBA
for Project. What I have is a list of parts that are at various levels in a
bill of materials. The information that I have copied into Project is the
part number/description and the level in the bill. I have recorded a few
macros that will indent the task a number of steps. What I would like is to
have code go through the list, look at record in the custom field that
indicates the level, and then indent that number of times minus 1. I need the
macros to go through until it completes this for the last task. The bills
that I will be importing have 4000 parts or more. For now, I have name the
custom field BOM_Level and it is a number field.

Any help is greatly appreciated.

Thanks in advance.

 

[Jan De Messemaeker]

Hi,

I've written that already but.. are you aware that task on numner N+1 cannot
have a level larger than the one on task N plus 1? If yes, try this snippet:

for each job in activeproject.tasks
if not job is nothing then
do while job.number7 > job.outlinelevel
job.outlineindent
loop
do while job.number7<job.outlinelevel
job.outlineoutdent
loop
end if
next job

Two remarks on this
1. Don't try to address BOM_Level, VBA does not know this alias (unmess it
is defined on Project server but even then job.BOM_Level will not work)
2. You may have hundreds of error messages if the outline levels cannot be
achieved - I did not program error routines, that I leave up to you )

Greetings,


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

 

[wiwchar]

Thanks for the assistance.

Tried running this and the code stopped at

job.outlineoutdent

I changed the job.number7 to job.number1 as this was the field that I named
BOM_Level.

Any more insight?

Thanks.

 

[Jack Dahlgren MVP]

Something like this should work if your BOM Levels never skip a step on the
way down ( ie: 1, 2, 3, 3, 1, 2, 2, 3, 4 is OK but 1, 3, 3, 4 is not because
it skips level 2) Skipping levels on the way back up the hierarchy is OK.

Sub indentme()
For Each Task In ActiveProject.Tasks
Task.OutlineLevel = Task.Number1
Next Task
End Sub

-Jack Dahlgren

 

[Jan De Messemaeker]

Hi,

Isn't that number field then 0?

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

 

[wiwchar]

I get the same error message; The argument value is not valid. Occurs on
Task.OutlineLevel = Task.Number1

 

[Jan De Messemaeker]

I supposed from the start you would program error trapping routines, I know
they are necessary.
Let's try to solve this one.
At that statement, how much is Task.number1?

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

 

[wiwchar]

i have tried this on various lines with the value of Task.number1 varying
from 1 through 10. Same result.

Is there a way to upload a sample file or a PPT to show what I am working
with?

Thanks again.

 

[Dean C]

I copied Outline Level, Outline Number and Name from an existing file to a
new file to simulate copying in from Excel, but have something to compare the
actual Outline Number value to. Jan's and Jack's macros worked for me. You
may need to add "Dim job as Task" for Jan's and a similar variable definition
for Jack's. Jack's took 82.6 seconds to process 1000 tasks, I gave up on
Jan's at five minutes and learned that 851 tasks had been processed.

The code below handled 1365 tasks in less than 2 seconds. I didn't add the
code needed to determine the maximum value for the number1 field, in the file
I used it was 8. You can add the code or use autofilter to look up the
maximum value and ask for the value in an input box. Thanks for having
trouble with the other macros, now that I developed this I'm going to use it
for fixing corrupt files.

Sub SetLevelByValue()
StartTime = Timer
For mylevel = "2" To "8"
FilterEdit Name:="OutlineLevel", TaskFilter:=True, Create:=True,
OverwriteExisting:=True, FieldName:="Number1", Test:="is greater than or
equal to", Value:=mylevel, ShowInMenu:=False, ShowSummaryTasks:=False
FilterApply Name:="OutlineLevel"
SelectSheet
OutlineIndent
Next mylevel
FilterApply Name:="All Tasks"
mydur = Timer - StartTime
MsgBox prompt:="This took " & mydur & " seconds"
End Sub