none
VBA - Change Task Cell Background Color based on cell value RRS feed

  • Question

  • The routine below loops through all rows in the Project and, based on the number in one custom field, changes the background color of that cell.  Works fine, except:

    The problem is that this routine loops through tasks and then uses "SelectTaskField Row:= taskID" to get to the field/cell that I want to modify.  To make this work I have to do "OutlineShowAllTasks", so that the Row number will correlate with the Task ID.  That changes the display of tasks that are closed.

    (The routine toggles the "Change Highlighting" because otherwise that will override the highlighting that the routine makes).

    Solutions might be:

    1) address the field by task instead of row

    2) recognize which tasks are closed, memorize those, then re-open them

    3) something else?

    Any suggestions on this would be appreciated 

    Sub SetPriorityColors() Dim wrkCHFlag As Boolean wrkCHFlag = False If EnableChangeHighlighting Then ToggleChangeHighlighting wrkCHFlag = True End If ScreenUpdating = False ScreenUpdating = True Set ts = ActiveProject.Tasks For n = 1 To ts.Count If Not ts(n) Is Nothing Then ts(n).OutlineShowAllTasks End If Next n

    For n = 1 To ts.Count If Not ts(n) Is Nothing Then Set tsk = ts(n) SelectTaskField Row:=tsk.ID, Column:="Number10", RowRelative:=False Select Case tsk.Number10 Case Is >= 9 Font32Ex CellColor:=&HFF99CC Case Is >= 8 Font32Ex CellColor:=&H66CCFF Case Is >= 7 Font32Ex CellColor:=&H66FFFF Case Is = 0 Font32Ex CellColor:=&HFFFFFF Case Is = 3 Font32Ex CellColor:=&HFFCC99 End Select End If Next n If wrkCHFlag Then ToggleChangeHighlighting End If SelectTaskField Row:="1", Column:="Number10", RowRelative:=False End Sub







    • Edited by codequest1 Sunday, October 25, 2015 4:08 PM
    Sunday, October 25, 2015 4:04 PM

Answers

  • codequest1,

    I don't see that the ScreenUpdating will have any effect on what you are trying to do, so I'd eliminate those lines.

    As I mentioned in my response to your other post, Project is less versatile than Excel when it comes to setting font characteristics. With Excel, much if not all, formatting of cells can be done while using background processing in VBA (i.e. operating on Excel objects independent of what is shown in the visible screen view), but Project has always been lagging behind in that functionality. Font/field cell formatting in Project has always required the use of foreground processing in VBA (i.e. must select objects in the visible view). Whether that has changed in Project 16, I do not know, but I kind of doubt it.

    Okay, so how do you do what you want? Well, you will have to do your option 2 (i.e. "memorize" the current view structure, do the formatting, and then restore the memorized view). In pseudo-code, this is how I would approach it.

    1. Select all tasks

    2. Step through the ActiveSelection.Tasks looking for summary lines.

    3. Keep track of the ID of each summary line and compare it with the ID of the next task. If the ID is sequential you can assume that summary line is expanded. If the ID is not sequential, you can assume that summary line is collapsed. I would probably keep track of which summary lines are expanded and which are collapsed in an array. Note, this gets more complex is you have summary lines at multiple outline levels.

    4. Once the view structure is stored, go ahead and expand the whole file and do the formatting. Note, in the quick test that I did, I don't see a need to mess with checking the change highlighting. Change highlighting should be independent of setting the field cell background color.

    5. When the formatting is all complete, use the stored view structure array to go back and collapse summary lines as necessary.

    If you are not familiar with using arrays, you can check out this Wiki article. It has a macro I wrote that uses arrays for storing and retrieving data: http://social.technet.microsoft.com/wiki/contents/articles/32126.ms-project-truncated-project-notes-when-viewingexporting.aspx

    Hope this helps.

    John

    • Marked as answer by codequest1 Monday, October 26, 2015 11:57 PM
    Sunday, October 25, 2015 8:22 PM

All replies

  • codequest1,

    Good morning.

    I have a few questions and a couple of comments.

    1. What is the purpose of setting ScreenUpdating to false and then back to true?

    2. The loop you have for OutlineShowAllTasks is unnecessary. Since that Method applies to all summary lines, all you need is simply:

    OutlineShowAllTasks (i.e. no loop at all)

          If you want to only expand selected summary lines, then you would use the OutlineShowSubTasks Method

    What is the goal of the macro? It appears that the project may be in various states of outline expansion (i.e. some summaries collapsed, some expanded, etc.) and you want to color code the background of selected tasks based on custom field Number10. However, in order to affect font changes, you must execute the macro using foreground processing and to do that you expand the whole project. When done, you want to restore the project to it's original state of outline expansion. Is that a valid summary? If this isn't an accurate description of your goal, please elaborate.

    John

    Sunday, October 25, 2015 5:05 PM
  • Thanks for your reply.

    1) The ScreenUpdating false/true was a leftover from when I was initially trying to get this to work, based on a bug report that said that might be an issue.   No other purpose.

    2) Understood, I can do that to simplify the routine.

    Your understanding of the goal is accurate.   

    Rows are relative to sheet position, which changes if Tasks are "open" or "closed".  So it seems odd that MS Project VBA doesn't have a way to select task fields without going through Row, since there isn't a strict correlation between Task and Row.  Or maybe there is such a way, and I don't know about it.

     


    Sunday, October 25, 2015 5:33 PM
  • codequest1,

    I don't see that the ScreenUpdating will have any effect on what you are trying to do, so I'd eliminate those lines.

    As I mentioned in my response to your other post, Project is less versatile than Excel when it comes to setting font characteristics. With Excel, much if not all, formatting of cells can be done while using background processing in VBA (i.e. operating on Excel objects independent of what is shown in the visible screen view), but Project has always been lagging behind in that functionality. Font/field cell formatting in Project has always required the use of foreground processing in VBA (i.e. must select objects in the visible view). Whether that has changed in Project 16, I do not know, but I kind of doubt it.

    Okay, so how do you do what you want? Well, you will have to do your option 2 (i.e. "memorize" the current view structure, do the formatting, and then restore the memorized view). In pseudo-code, this is how I would approach it.

    1. Select all tasks

    2. Step through the ActiveSelection.Tasks looking for summary lines.

    3. Keep track of the ID of each summary line and compare it with the ID of the next task. If the ID is sequential you can assume that summary line is expanded. If the ID is not sequential, you can assume that summary line is collapsed. I would probably keep track of which summary lines are expanded and which are collapsed in an array. Note, this gets more complex is you have summary lines at multiple outline levels.

    4. Once the view structure is stored, go ahead and expand the whole file and do the formatting. Note, in the quick test that I did, I don't see a need to mess with checking the change highlighting. Change highlighting should be independent of setting the field cell background color.

    5. When the formatting is all complete, use the stored view structure array to go back and collapse summary lines as necessary.

    If you are not familiar with using arrays, you can check out this Wiki article. It has a macro I wrote that uses arrays for storing and retrieving data: http://social.technet.microsoft.com/wiki/contents/articles/32126.ms-project-truncated-project-notes-when-viewingexporting.aspx

    Hope this helps.

    John

    • Marked as answer by codequest1 Monday, October 26, 2015 11:57 PM
    Sunday, October 25, 2015 8:22 PM
  • @John:  that all seems to work.  I did not fully implement it, however, all the key statements do what they're suppose to, so I think it's just working out the details.  I'm calling this the solution;  I'll post back if there are any issues.

     Thanks!

    Monday, October 26, 2015 12:32 AM
  • codequest1,

    Okay. . . . if it "all" seems to work, what part didn't you implement?

    Note, I'm offline until tomorrow.

    John


    Monday, October 26, 2015 1:58 AM
  • Here's the full implementation.  It does everything I'm looking for.  

    > Whatever problem I was having with the ChangeHightlighting no longer occurs, so I took that out.

    >  It seems to work fine as is for the multi-level summary tasks that I have in my example (maybe I'm not observing something, but looks fine so far)

    > There may be a way to accomplish this more simply through the ProjectBeforeTaskChange event, following this:

    http://blogs.msdn.com/b/project_programmability/archive/2007/02/12/vba-event-handler-example.aspx

    however, that's another problem for another day.


    Thanks!

    Sub SetPriorityColors()
    
    Dim ar1(100) As Integer
    Dim ind1 As Integer
    ind1 = 0
    
    Dim tsk As Task
    
    Dim wrkCHFlag As Boolean
    wrkCHFlag = False
     
    Set ts = ActiveProject.Tasks
    
    Dim currSumID As Integer
    currSumID = 1000
    
    SelectAll
    Set ts = ActiveSelection.Tasks
    Dim sumFlag As Boolean
    sumFlag = False
    
    For n = 1 To ts.Count
    
            Set tsk = ts(n)
                    
            '---- check if the save summary id (except on the first case) is followed by a task in sequence
            If sumFlag Then
                    If tsk.ID <> currSumID + 1 Then
                    ar1(ind1) = currSumID
                    ind1 = ind1 + 1
                End If
            End If
            
            '----- save the summary id
            If tsk.Summary = True Then
                    currSumID = tsk.ID
                    sumFlag = True
                Else
                    sumFlag = False
            End If
                    
    Next
    
    '===== now set the colors================
    OutlineShowAllTasks
    SelectAll
    
    Set ts = ActiveSelection.Tasks
    For n = 1 To ts.Count
        If Not ts(n) Is Nothing Then
        
            Set tsk = ts(n)
    
            SelectTaskField Row:=tsk.ID, Column:="Number10", RowRelative:=False
            If wrkID = 23 Then
                a = 1
            End If
         
            Select Case tsk.Number10
                Case Is >= 9
                    Font32Ex CellColor:=&HFF99CC
                Case Is >= 8
                    Font32Ex CellColor:=&H66CCFF
                Case Is >= 7
                    Font32Ex CellColor:=&H66FFFF
                 Case Is = 0
                     Font32Ex CellColor:=&HFFFFFF
                 Case Is = 3
                     Font32Ex CellColor:=&HFFCC99
            End Select
                
       End If
    
    Next n
    
    '=======  Now Reset the Outline structure
    SelectAll
    Set ts = ActiveSelection.Tasks
    
    For n = 0 To UBound(ar1) - 1
            If ar1(n) <> 0 Then
                Set tsk = ts.Item(ar1(n))
                tsk.OutlineHideSubTasks
            End If
    
            If tsk.Name = "COMPLETED" Then
                Exit For
            End If
            
    Next
    '================================
    
    SelectTaskField Row:="1", Column:="Number10", RowRelative:=False
    
    End Sub




    • Edited by codequest1 Monday, October 26, 2015 4:46 PM
    Monday, October 26, 2015 4:45 PM
  • codequest1,

    Okay, I'm glad you got it working.

    Yes, you could probably do the same thing with an Event macro but I wouldn't recommend it. Setting up Event based macros are much more involved and it is not necessary for what you are doing. I'd just concentrate on refining the code you have to eliminate some redundancies. For example, for better readability and format, the Dim statements are normally grouped all together at the beginning rather then sprinkled throughout the code. You could also simplify a couple of your loops. Instead of:

    Set ts = ActiveSelection.Tasks
    For n = 1 To ts.Count

    use this

    For Each ts In ActiveSelection.Tasks

    The array is arbitrarily set at 100. That may be to small for larger projects or way overkill for smaller projects. You can set the array dimension dynamically by using the ReDim statement. In you case the maximum number of elements in the array is equals to the number of summary lines in your file, so you could apply the Summary filter and then get a count of the ActiveSelection.Tasks and redimension the array accordingly. Then you won't need the Exit For in your last loop.

    Dim ar1() as Integer

    FilterApply Name:="summary tasks"
    SelectAll
    ReDim ar1(ActiveSelection.Tasks.Count)
    FilterApply Name:="all tasks"

    Those are just a few things I noticed. Hope this helps.

    John

    By the way, if you want to learn more about writing macros for Project, I highly recommend Rod Gill's book on the subject. You can find out more at: http://www.project-systems.co.nz/project-vba-book/index.html
    • Edited by John - Project Monday, October 26, 2015 7:25 PM Rod's book
    Monday, October 26, 2015 7:20 PM
  • Good suggestions.  Thanks!
    Monday, October 26, 2015 10:43 PM
  • codequest1,

    You're welcome. If one or more of my responses answered your question or was at least helpful, please consider marking as answered or with a vote.

    John

    Monday, October 26, 2015 11:26 PM
  • John,

    you are a genius!

    Thanks for sharing.

    Christian

    Btw, your solution is still required for MS Project 2016...
    • Edited by Chris_RB Thursday, August 31, 2017 9:53 AM
    Thursday, August 31, 2017 9:52 AM
  • Thanks for sharing!
    I was really frustrated already....
    Thursday, August 31, 2017 9:54 AM
  • Chris_RB,

    You're welcome and thanks for the feedback.

    John

    Thursday, August 31, 2017 1:26 PM