Set Outline Level using If/Then and Loop

Question

I'm trying to set an outline level in MS Project using a VBA Code.

I have all the information in MSProject, and would like to set the column "Outline Level" column, based on The value in "Text 1" column>

I am not able to set the types of columns, as these are preset by Project.

I have tried to copy and paste, manually the values from Text 1 into Outline Level, but project won't allow it, so I'd like to do an If/Then statement, to enter the values. If Text 1 is a 1, then Outline Level is a 1, etc. I'd like to enter each value in Outline Level, then go to the next row and do the same, until each Outline Level has been entered in.

Sub OutLineLevel()

Set projApp = ActiveProject

Dim OutlineLevel As Long
Dim Text1 As Long
Dim i As Long

For i = 1 To 4

    
    If projApp.Text1(i, 0) = 1 Then
        projApp.OutlineLevel(i, 0) = 1
    ElseIf projApp.Text1(i, 0) = 2 Then
        projApp.OutlineLevel(i, 0) = 2
    ElseIf projApp.Text1(i, 0) = 3 Then
        projApp.OutlineLevel(i, 0) = 3
    ElseIf projApp.Text1(i, 0) = 4 Then
        projApp.OutlineLevel(i, 0) = 4
    Else: projApp.Text1(i, 0) = 5
    End If
Next i

End Sub

I'm getting "Property Not Defined" at the first "If" statement

john-project · Answer 1 · 2023-03-22T16:05:23.010

1

I don't understand why you would want to create the hierarchy from a Text field but here's a very simple macro that should do what you want.

By the way, the reason you get the error is because you are trying to use Text1 as an array but it is never declared as an array.

Sub SetOutline()
Dim t As Task
For Each t In ActiveProject.Tasks
    If Not t Is Nothing Then
        t.OutlineLevel = t.Text1
    End If
Next t
End Sub

edited Mar 22 '23 at 16:05

answered Mar 22 '23 at 00:05

john-project

331
1
7

John. This was great - I did change Dim "t" to Dim "tsk" for clarity, but it worked great. Thank you! – lyonsguy Mar 23 '23 at 00:10

score 0 · Answer 2 · answered Mar 22 '23 at 00:07

0

Here is code to set the tasks' outline levels to the value in their Text1 field:

Sub SetOutlineLevel()

    Dim tsk As Task
    For Each tsk In ActiveProject.Tasks
        tsk.OutlineLevel = tsk.Text1
    Next tsk
    
End Sub

Note: The values in Text1 must be valid (for example, the first task must be level 1, outline levels must not increase by more than one at a time, etc).

answered Mar 22 '23 at 00:07

Rachel Hettinger

7,927
2
21
31

Rachel, I tried setting a Text1 value beyond the "one level at a time" and the interesting result is that Project "self-corrects". But I certainly agree that some type of Text1 validity checking is necessary. – john-project Mar 22 '23 at 16:13
John and Rachael - it is difficult doing a copy/paste or mass update in MS Project as John Indicated. I consolidated your two solutions, and it seems to be working. Thank you! I'm also learning that VBA has different objects for Project and Excel (likely obvious to more experience people than me), but it is giving me difficulty integrating project and excel with a single code. Thank you for your collective corrections!! – lyonsguy Mar 23 '23 at 00:13
lyonsguy, I've written a lot of macros that "integrate" Project and Excel. It's just a matter of studying and using the Object model for each app. – john-project Mar 23 '23 at 00:26

Set Outline Level using If/Then and Loop

2 Answers2