none
Custum field formula RRS feed

  • Question

  • Hi,

    I am struggling with what is probably an easy task for some, please help.

    I have created a custom task text which i would like to add a formula to that when a value of yes is entered it will turn all text in that entire column Red. Is there a way to do this?

    Thanks

    Steve 

    Monday, August 13, 2012 3:11 PM

Answers

  • Hello Steve,

    I guess you mean "the entire row", not "the entire column". I am afraid this is only possible by VBA. Pls see Brian's article here at this external link.

    Regards.

    • Marked as answer by Steve.D.H Monday, August 13, 2012 4:22 PM
    Monday, August 13, 2012 3:59 PM
  • Hi Steve - not an easy task at all.

    There's no native "conditional formatting" functionality in MS Project. You have to do this via VBA.

    Also, text fields are a finite resource and always under pressure, so rather than using a text field to record a boolean (i.e. Yes/No) I'd recommend using a flag field instead.

    The code below assumes you'll use flag1 to do this (you can easily change this if you need to use another field), and that this column is displayed in the current view. Note that any formatting applied with this method is specific to the current view, so if you move to a different view the formatting will not be apparent:

    Sub ApplyFormattingToFlag1()
    ' ===================================================================================
    ' ===== This section applies the formatting to the current view                 =====
    ' ===================================================================================
    Dim t As task
    Dim pj As Project
    Dim boo_AnyYes
    Dim app As Application
    Set app = MSProject.Application
    Set pj = activeProject
    boo_AnyYes = False
        For Each t In pj.Tasks
        
            If t.Flag1 = True Then
                boo_AnyYes = True
            End If
        
        Next t
        
        
        With app
            .ScreenUpdating = False ' Attempt to stop the screen refreshing
        
            .FilterApply "&All Tasks"
            .OutlineHideSubTasks
            .OutlineShowAllTasks
            
            .SelectTaskColumn Column:="flag1"
            
            If boo_AnyYes Then
                .FontEx CellColor:=1, Pattern:=1
            Else
                .FontEx CellColor:=16, Pattern:=0
            End If
        
        End With
    End Sub

    • Marked as answer by Steve.D.H Monday, August 13, 2012 4:26 PM
    Monday, August 13, 2012 4:02 PM
  • Hi,

    I tried using/modifying the examples in Brians link with limited success. The macro below works ok on single files apart from the fact it does not refresh the text colour of column text11 for the last changed task. For example if i run the macro all text is changed apart from  text11 of the last activity that i updated in text 11.

    Also this macro does not work when it is used in master plans it seems to change the wrong tasks. Also it does not change project summary tasks. Any idea how i could change this macro to work in master plans?

    The basic idea is to use text colour in our master plans to identify tasks & projects which either are scheduled (black text), require further scheduling to level resources (red text) or are placeholders (blue text)

    Regards

    Steve

    Sub Refresh_Text_Colour()
    Dim tskT As Task
    For Each tskT In ActiveProject.Tasks
        Select Case tskT.Text11
            Case "Requires Scheduling"
                SelectRow Row:=tskT.ID, RowRelative:=False
                Font Color:=pjRed
            Case "Placeholder"
                SelectRow Row:=tskT.ID, RowRelative:=False
                Font Color:=pjBlue
            Case ""
                SelectRow Row:=tskT.ID, RowRelative:=False
                Font Color:=pjBlack

        End Select
    Next tskT
    End Sub


    Hello Steve,

    If you can send me the mpp files, I'll take a look at them (projectuser-at-fastmail.fm).

    Regards.

    • Marked as answer by Steve.D.H Wednesday, August 15, 2012 8:16 AM
    Tuesday, August 14, 2012 1:49 PM
  • It's funny that you meant row instead of column - my original code was for rows but I changed it to columns for you!

    Here's a new version using filters rather than a for/next loop. Makes it faster for large schedules and seems to work for inserted tasks too.

    Same assumptions apply:

    • Text11 is visible in the current view
    • Only applies the formatting to the current view
    Sub ApplyFormattingBasedOnText11()
    Dim pj As Project
    Dim ts As Tasks
    Dim obj_OriginalSelection As Long   'used to restore the scroll location within the original view
    Dim lng_FirstTaskInSelection As Long
    Dim app As Application
    Set app = MSProject.Application
    Set pj = activeProject
    With app
        .ScreenUpdating = False ' Attempt to stop the screen refreshing
    'Create (or refresh) the filters required to apply formatting
        .FilterEdit name:="text11ReqsScheduling", TaskFilter:=True, create:=True, OverwriteExisting:=True, fieldName:="Text11", test:="equals", value:="Requires Scheduling", showInMenu:=False, ShowSummaryTasks:=False
        .FilterEdit name:="text11Placeholder", TaskFilter:=True, create:=True, OverwriteExisting:=True, fieldName:="Text11", test:="equals", value:="Placeholder", showInMenu:=False, ShowSummaryTasks:=False
        .SaveSheetSelection
        If Not ActiveSelection Is Nothing Then  'If the current selection doesn't include any tasks
            obj_OriginalSelection = 1           'Set the cursor reset value to 1
        Else                                    'otherwise, get the row number which the cursor is at
            lng_FirstTaskInSelection = ActiveSelection.Tasks(1)
            obj_OriginalSelection = pj.Tasks.uniqueId(lng_FirstTaskInSelection).id
        End If
        
        'First, reset all text to black
        .OutlineHideSubTasks
        .OutlineShowAllTasks
        .SelectAll
        Font Color:=pjBlack
        
        'Then filter for those containining "Requires Scheduling" in Text11
        .FilterApply name:="text11ReqsScheduling"
        .SelectAll
        'Set the font to Red
        Font Color:=pjRed
        'Then filter for those containing "Placeholder" in Text11
        .FilterApply name:="text11Placeholder"
        .SelectAll
        'Set the font to blue
        Font Color:=pjBlue
        .FilterApply "&All Tasks"
    'Restore the previous selection
        .RestoreSheetSelection
        .EditGoTo obj_OriginalSelection
    End With
    End Sub
    You might want to consider adding a hook into this sub-procedure from the BeforeSave event, to force the conditional formatting to refresh whenever you save.

    • Edited by Andrew Simpson Tuesday, August 14, 2012 3:18 PM Afterthough about triggering from an event)
    • Marked as answer by Steve.D.H Wednesday, August 15, 2012 8:16 AM
    Tuesday, August 14, 2012 3:15 PM
  • Hi Steve,

    Assuming you're implementing this via a global file rather than on individual MPPs, open the "ThisProject" module in VBE:

    Insert the following code:

    Private Sub Project_BeforeSave(ByVal pj As Project) Call ApplyFormattingBasedOnText11

    End Sub

    This will refresh the conditional formatting each time you save the file, and shoudln't impact performance too much.

    If you're adding the code to individual MPP files instead of the Global, just add the same code to the ThisProject module for the file instead (further down the VBE explorer).

    Thanks,
    Andrew


    • Edited by Andrew Simpson Wednesday, August 15, 2012 4:13 PM
    • Marked as answer by Steve.D.H Thursday, August 16, 2012 7:30 AM
    Wednesday, August 15, 2012 4:12 PM
  • Hi steve,

    No it won't work if you've grouped the view. The reason is, if you apply your grouping, then select a cell in one of the group header rows (shaded in yellow), you'll see that the formatting options are disabled for this section (i.e. can't set bold, underline, recolour). That's the method that the VBA is trying to run, but isn't available.

    You could stop this error occuring by adding an error trap to the code:

    At the top of the sub procedure, before the varaible declarations, insert a row stating:

    On error go to StopSub

    And before the row which says "End Sub", insert the following row:

    StopSub:

    Then when an error does occur, it won't be presented to the user.

    You could also amend the code so that it swaps to the correct view, applies the formatting, then swaps back to the original view before saving. Perfectly feasible, I'm just a bit tight on time at the moment.

    If you wanted to make a start on this yourself, I'd suggest recording a couple of macros that change views then inspecting the code that is created for you. That'll show you the basics.

    Thanks,
    Andrew

    • Edited by Andrew Simpson Thursday, August 23, 2012 9:51 AM Formatting had disappeared!
    • Marked as answer by Steve.D.H Thursday, August 23, 2012 9:52 PM
    Thursday, August 23, 2012 9:48 AM
  • Hi,

    I tried using/modifying the examples in Brians link with limited success. The macro below works ok on single files apart from the fact it does not refresh the text colour of column text11 for the last changed task. For example if i run the macro all text is changed apart from  text11 of the last activity that i updated in text 11.

    Also this macro does not work when it is used in master plans it seems to change the wrong tasks. Also it does not change project summary tasks. Any idea how i could change this macro to work in master plans?

    The basic idea is to use text colour in our master plans to identify tasks & projects which either are scheduled (black text), require further scheduling to level resources (red text) or are placeholders (blue text)

    Regards

    Steve

    Sub Refresh_Text_Colour()
    Dim tskT As Task
    For Each tskT In ActiveProject.Tasks
        Select Case tskT.Text11
            Case "Requires Scheduling"
                SelectRow Row:=tskT.ID, RowRelative:=False
                Font Color:=pjRed
            Case "Placeholder"
                SelectRow Row:=tskT.ID, RowRelative:=False
                Font Color:=pjBlue
            Case ""
                SelectRow Row:=tskT.ID, RowRelative:=False
                Font Color:=pjBlack

        End Select
    Next tskT
    End Sub


    • Edited by Steve.D.H Tuesday, August 14, 2012 9:50 AM
    • Marked as answer by Steve.D.H Wednesday, August 15, 2012 8:16 AM
    Tuesday, August 14, 2012 9:49 AM

All replies

  • Hello Steve,

    I guess you mean "the entire row", not "the entire column". I am afraid this is only possible by VBA. Pls see Brian's article here at this external link.

    Regards.

    • Marked as answer by Steve.D.H Monday, August 13, 2012 4:22 PM
    Monday, August 13, 2012 3:59 PM
  • Hi Steve - not an easy task at all.

    There's no native "conditional formatting" functionality in MS Project. You have to do this via VBA.

    Also, text fields are a finite resource and always under pressure, so rather than using a text field to record a boolean (i.e. Yes/No) I'd recommend using a flag field instead.

    The code below assumes you'll use flag1 to do this (you can easily change this if you need to use another field), and that this column is displayed in the current view. Note that any formatting applied with this method is specific to the current view, so if you move to a different view the formatting will not be apparent:

    Sub ApplyFormattingToFlag1()
    ' ===================================================================================
    ' ===== This section applies the formatting to the current view                 =====
    ' ===================================================================================
    Dim t As task
    Dim pj As Project
    Dim boo_AnyYes
    Dim app As Application
    Set app = MSProject.Application
    Set pj = activeProject
    boo_AnyYes = False
        For Each t In pj.Tasks
        
            If t.Flag1 = True Then
                boo_AnyYes = True
            End If
        
        Next t
        
        
        With app
            .ScreenUpdating = False ' Attempt to stop the screen refreshing
        
            .FilterApply "&All Tasks"
            .OutlineHideSubTasks
            .OutlineShowAllTasks
            
            .SelectTaskColumn Column:="flag1"
            
            If boo_AnyYes Then
                .FontEx CellColor:=1, Pattern:=1
            Else
                .FontEx CellColor:=16, Pattern:=0
            End If
        
        End With
    End Sub

    • Marked as answer by Steve.D.H Monday, August 13, 2012 4:26 PM
    Monday, August 13, 2012 4:02 PM
  • Andrew,

    Thanks for you time and effort, i will try this macro tommorrow.

    Regards

    Steve

    Monday, August 13, 2012 4:26 PM
  • Project user,

    Yes i meant row! I will investigate this tommorow in addition to trying the macro andrew has posted below.

    Regards

    steve

    Monday, August 13, 2012 4:27 PM
  • Andrew,

    I decided to stop over at work & give this a go tonight.

    The 1st time i ran the macro it worked fine. I then changed some of the flags, yes to no & no to yes etc. Now when i run the macro it seems if there is just 1 yes the entire column turns red. Also I had posted incorrectly, is there a way to make this macro change the text on the entire Row rather than the back ground colour on the entire column ?

    Regards

    Steve

    Monday, August 13, 2012 4:43 PM
  • Hi,

    I tried using/modifying the examples in Brians link with limited success. The macro below works ok on single files apart from the fact it does not refresh the text colour of column text11 for the last changed task. For example if i run the macro all text is changed apart from  text11 of the last activity that i updated in text 11.

    Also this macro does not work when it is used in master plans it seems to change the wrong tasks. Also it does not change project summary tasks. Any idea how i could change this macro to work in master plans?

    The basic idea is to use text colour in our master plans to identify tasks & projects which either are scheduled (black text), require further scheduling to level resources (red text) or are placeholders (blue text)

    Regards

    Steve

    Sub Refresh_Text_Colour()
    Dim tskT As Task
    For Each tskT In ActiveProject.Tasks
        Select Case tskT.Text11
            Case "Requires Scheduling"
                SelectRow Row:=tskT.ID, RowRelative:=False
                Font Color:=pjRed
            Case "Placeholder"
                SelectRow Row:=tskT.ID, RowRelative:=False
                Font Color:=pjBlue
            Case ""
                SelectRow Row:=tskT.ID, RowRelative:=False
                Font Color:=pjBlack

        End Select
    Next tskT
    End Sub


    • Edited by Steve.D.H Tuesday, August 14, 2012 9:50 AM
    • Marked as answer by Steve.D.H Wednesday, August 15, 2012 8:16 AM
    Tuesday, August 14, 2012 9:49 AM
  • Hi Steve, sorry I haven't had much spare time today.

    Are you sure it's not just the blue highlighting on the last change that is obscuring the changed formatting? Try changing another value/inserting a new row and see if the format is correct.

    Also, I've found that the for/next loop to apply formatting can take a long time to run for large schedules, and I found that a far quicker solution for rows was to apply a filter for the values in question, then select all rows and apply formatting. I've got an example which I'll look out later (time permitting), but you may be able to record a macro that applies the formatting in this way.

    Thanks,
    Andrew

    Tuesday, August 14, 2012 9:59 AM
  • Hi Andrew,

    Yes you are correct the blue highlighting is obscuring the new formatting, if i save the project it refreshes the screen :-).

    The issue with this filter not working within a master plan still causes me issues

    Regard

    steve

    Tuesday, August 14, 2012 12:41 PM
  • Hi,

    I tried using/modifying the examples in Brians link with limited success. The macro below works ok on single files apart from the fact it does not refresh the text colour of column text11 for the last changed task. For example if i run the macro all text is changed apart from  text11 of the last activity that i updated in text 11.

    Also this macro does not work when it is used in master plans it seems to change the wrong tasks. Also it does not change project summary tasks. Any idea how i could change this macro to work in master plans?

    The basic idea is to use text colour in our master plans to identify tasks & projects which either are scheduled (black text), require further scheduling to level resources (red text) or are placeholders (blue text)

    Regards

    Steve

    Sub Refresh_Text_Colour()
    Dim tskT As Task
    For Each tskT In ActiveProject.Tasks
        Select Case tskT.Text11
            Case "Requires Scheduling"
                SelectRow Row:=tskT.ID, RowRelative:=False
                Font Color:=pjRed
            Case "Placeholder"
                SelectRow Row:=tskT.ID, RowRelative:=False
                Font Color:=pjBlue
            Case ""
                SelectRow Row:=tskT.ID, RowRelative:=False
                Font Color:=pjBlack

        End Select
    Next tskT
    End Sub


    Hello Steve,

    If you can send me the mpp files, I'll take a look at them (projectuser-at-fastmail.fm).

    Regards.

    • Marked as answer by Steve.D.H Wednesday, August 15, 2012 8:16 AM
    Tuesday, August 14, 2012 1:49 PM
  • It's funny that you meant row instead of column - my original code was for rows but I changed it to columns for you!

    Here's a new version using filters rather than a for/next loop. Makes it faster for large schedules and seems to work for inserted tasks too.

    Same assumptions apply:

    • Text11 is visible in the current view
    • Only applies the formatting to the current view
    Sub ApplyFormattingBasedOnText11()
    Dim pj As Project
    Dim ts As Tasks
    Dim obj_OriginalSelection As Long   'used to restore the scroll location within the original view
    Dim lng_FirstTaskInSelection As Long
    Dim app As Application
    Set app = MSProject.Application
    Set pj = activeProject
    With app
        .ScreenUpdating = False ' Attempt to stop the screen refreshing
    'Create (or refresh) the filters required to apply formatting
        .FilterEdit name:="text11ReqsScheduling", TaskFilter:=True, create:=True, OverwriteExisting:=True, fieldName:="Text11", test:="equals", value:="Requires Scheduling", showInMenu:=False, ShowSummaryTasks:=False
        .FilterEdit name:="text11Placeholder", TaskFilter:=True, create:=True, OverwriteExisting:=True, fieldName:="Text11", test:="equals", value:="Placeholder", showInMenu:=False, ShowSummaryTasks:=False
        .SaveSheetSelection
        If Not ActiveSelection Is Nothing Then  'If the current selection doesn't include any tasks
            obj_OriginalSelection = 1           'Set the cursor reset value to 1
        Else                                    'otherwise, get the row number which the cursor is at
            lng_FirstTaskInSelection = ActiveSelection.Tasks(1)
            obj_OriginalSelection = pj.Tasks.uniqueId(lng_FirstTaskInSelection).id
        End If
        
        'First, reset all text to black
        .OutlineHideSubTasks
        .OutlineShowAllTasks
        .SelectAll
        Font Color:=pjBlack
        
        'Then filter for those containining "Requires Scheduling" in Text11
        .FilterApply name:="text11ReqsScheduling"
        .SelectAll
        'Set the font to Red
        Font Color:=pjRed
        'Then filter for those containing "Placeholder" in Text11
        .FilterApply name:="text11Placeholder"
        .SelectAll
        'Set the font to blue
        Font Color:=pjBlue
        .FilterApply "&All Tasks"
    'Restore the previous selection
        .RestoreSheetSelection
        .EditGoTo obj_OriginalSelection
    End With
    End Sub
    You might want to consider adding a hook into this sub-procedure from the BeforeSave event, to force the conditional formatting to refresh whenever you save.

    • Edited by Andrew Simpson Tuesday, August 14, 2012 3:18 PM Afterthough about triggering from an event)
    • Marked as answer by Steve.D.H Wednesday, August 15, 2012 8:16 AM
    Tuesday, August 14, 2012 3:15 PM
  • Andrew,

    That seems to work great thankyou.

    Unfortunately I am, as you probably altready realize a novice at VBA. Did not know you could create a "hook". Can i ask of you to point me in the right direction on how to do this or somewhetre to research how to do it?

    Regards

    Steve

    Wednesday, August 15, 2012 8:16 AM
  • Thanks for your assistance with this matter, very kind

    Regards

    Steve

    Wednesday, August 15, 2012 3:34 PM
  • You're welcome. --Regards.
    Wednesday, August 15, 2012 3:47 PM
  • Hi Steve,

    Assuming you're implementing this via a global file rather than on individual MPPs, open the "ThisProject" module in VBE:

    Insert the following code:

    Private Sub Project_BeforeSave(ByVal pj As Project) Call ApplyFormattingBasedOnText11

    End Sub

    This will refresh the conditional formatting each time you save the file, and shoudln't impact performance too much.

    If you're adding the code to individual MPP files instead of the Global, just add the same code to the ThisProject module for the file instead (further down the VBE explorer).

    Thanks,
    Andrew


    • Edited by Andrew Simpson Wednesday, August 15, 2012 4:13 PM
    • Marked as answer by Steve.D.H Thursday, August 16, 2012 7:30 AM
    Wednesday, August 15, 2012 4:12 PM
  • Hi Andrew,

    Sorry to come back to you after such a long time, I have another question if you would be so kind?

    The macro you kindly wrote for me seems to work great on my standard gantt view, however when in the same view I group by resource names in accending order it does not work (I get error "Run time error '1100'    The method is not available in this situation")

    Regards

    Steve

    Wednesday, August 22, 2012 3:05 PM
  • Hi steve,

    No it won't work if you've grouped the view. The reason is, if you apply your grouping, then select a cell in one of the group header rows (shaded in yellow), you'll see that the formatting options are disabled for this section (i.e. can't set bold, underline, recolour). That's the method that the VBA is trying to run, but isn't available.

    You could stop this error occuring by adding an error trap to the code:

    At the top of the sub procedure, before the varaible declarations, insert a row stating:

    On error go to StopSub

    And before the row which says "End Sub", insert the following row:

    StopSub:

    Then when an error does occur, it won't be presented to the user.

    You could also amend the code so that it swaps to the correct view, applies the formatting, then swaps back to the original view before saving. Perfectly feasible, I'm just a bit tight on time at the moment.

    If you wanted to make a start on this yourself, I'd suggest recording a couple of macros that change views then inspecting the code that is created for you. That'll show you the basics.

    Thanks,
    Andrew

    • Edited by Andrew Simpson Thursday, August 23, 2012 9:51 AM Formatting had disappeared!
    • Marked as answer by Steve.D.H Thursday, August 23, 2012 9:52 PM
    Thursday, August 23, 2012 9:48 AM
  • Andrew,

    Ok thanks i will give this a go myself, a simple but great idea :-) Im sure i can muddle through this one on my own, rather than you invest any more time.

    Thanks for the explanation at least i understand now.

    Kind Regards

    Steve

    Thursday, August 23, 2012 9:52 PM