I have a Master-Project, and inside it I have several Sub-Projects.
I want to format the Color of the Rows based on a value of a field Text5.
Current Problem: How to use VBA code to refer to the Row in the 2nd Sub-Project.
When I run the code, and I modify Text5 value in Task ID 5 (in Sub-Project 2), it modifies the color of Task ID 5, but of Sub-Project 1.
How can I use the SelectRow and add refference to the desired Sub-Project ?
My Code (relevant section)
Sub FormatChangedTasks()
Dim SubPrj As Subproject
Dim Tsk As Task
Dim i As Long
For Each SubPrj In ActiveProject.Subprojects
' compare the name of Sub-Project with the one saved in the "Stack" srray
If SubPrj.SourceProject.Name = ModifiedPrjName Then
For Each Tsk In SubPrj.SourceProject.Tasks
If Not Tsk Is Nothing Then
i = Tsk.ID
' check if Tsk.Text5 value has changed from value in "StatusStackArr" array
If StatusStackArr(i - 1).StatusOldVal <> Tsk.Text5 Then
' **** at the line below it selects the Row from the top
' (not the desired Sub-Project) ******
SelectRow Row:=i, RowRelative:=False
' --format entire row --
Select Case Tsk.Text5 ' Get the Field's used field, not name
Case "R", "Y", "G"
FontEx CellColor:=7, Color:=0
FontEx Italic:=False ' Font regular
Case "Complete"
FontEx Italic:=True ' Font Italic
FontEx CellColor:=15, Color:=14 ' Background Silver ; font Gray
End Select
' rest of code (un-relevant)
To format text in a table, use the FontEx method. This method formats the active (selected) cells. To select a row for formatting, use the SelectRow method. To use this method you need to know:
For both options 1 and 2 tasks hidden by filters or collapsed summaries mean that the row parameter for the SelectRow method must be adjusted accordingly. It is very difficult to know which tasks might be hidden. The best way to use this method is to first make sure that all tasks are visible (see code).
Additionally, when working with subprojects, it is a challenge to calculate the absolute row position as first you need to get the task count of subprojects higher up in the schedule plus any native tasks in the master project itself (although this is atypical).
All of this leads to using the Find method, option 3, as the best way to select a task within a master project. To select exactly the correct task, then Find method must be used with a unique field. The Unique ID field is the only field guaranteed to be unique.
When tasks are added to a project, an incremental Unique ID is assigned, starting with 1. When projects are combined within a master project, the Unique IDs are changed by adding a seed value so that there are no duplicates within the master project.
The seed value is based on an internal, subproject "index". The first subproject's tasks are given a seed value of 4194304, the second subproject's tasks have a seed value of 8388608 (4194304 * 2), and so forth.
If a subproject is removed from the master, it's "index" is not re-used. Similarly, if subprojects are rearranged in the master, the "index" values do not change. Therefore, you cannot use the Index property of the subproject object to obtain the internal "index" value that is used to create the seed as that property simply indicates the order of subprojects.
(Note, there is a way to get this internal value, but that's beyond the scope of this question. FYI it also requires selecting all tasks.)
To select a task in the user interface, you'll need to know it's Unique ID in the master schedule. If you access the task by way the SourceProject.Tasks method you'll be accessing the tasks in their native subproject and the Unique ID will not include the seed value. For example, the Unique ID will be 2 which is not unique within the master project and therefore not sufficient to select the task with the Find method.
Since you are already looping through all the tasks in the schedule, the easiest way to determine the master Unique ID is to loop through the tasks natively in the master project. To do that select all tasks and loop through the selection.
Sub FormatChangedTasks()
' show all tasks
FilterClear
SelectAll
SummaryTasksShow (0)
OutlineShowAllTasks
Dim AllTasks As Tasks
Set AllTasks = ActiveSelection.Tasks
Dim Tsk As Task
For Each Tsk In AllTasks
If Not Tsk Is Nothing Then
' compare the name of Sub-Project with the one saved in the "Stack" srray
If Tsk.Project = ModifiedPrjName Then
' check if Tsk.Text5 value has changed from prior value
' NOTE: use a dictionary here instead of an array
If StatusStackArr(i - 1).StatusOldVal <> Tsk.Text5 Then
Find "Unique ID", "equals", Tsk.UniqueID
SelectRow
' --format entire row --
Select Case Tsk.Text5 ' Get the Field's used field, not name
Case "R", "Y", "G"
FontEx CellColor:=7, Color:=0
FontEx Italic:=False ' Font regular
Case "Complete"
FontEx Italic:=True ' Font Italic
FontEx CellColor:=15, Color:=14 ' Background Silver ; font Gray
End Select
' rest of code (un-relevant)
End If
End If
Next Tsk
End Sub
Notes: