Every so often you want to do something so simple, and the software seems to stand in the way.
Microsoft Project seems to be the default scheduler nowadays. It has the same ease of use as the rest of the Office suite. In fact someone described it to me as just like Excel. So if it like Excel, why can’t it do conditional formatting? I am not after complex stuff, but the very simple stuff. In particular I was looking to highlight a BRAG status (That’s Blue, Red, Amber, Green just in case you were asking) so that as well as the letter you got a shaded background. After all you could do the shading, and yes I know it could do little indicators, but I wanted to colour in the background.
As i couldn’t find this easily I turned to the internet for help, after all it all seems to be out there somewhere if you know how to ask the question. However as much as I tried I couldn’t find the answer. Hence the reason for this blog, as I managed to find an answer and I thought others may want to take what I have done and extend this into other areas.
In order to do this I needed to write some MS Project Macros, 4 to be precise. The first ones I wrote were some trigger macros MS Project helpfully provides some functionality that runs when an event happens. What I wanted was something that ran after a task has been updated. Unfortunately, that didn’t exist so I had to cheat. I wrote one macro which ran just before a task was updated and remembered which line had changed. I then wrote a macro which ran when the cursor moved to another field. The second macro then moved the cursor back to original field, and depending on the current value of the field (BRAG) coloured in the background of the cell. It then moved the cursor back to where it had come from, so that the user (me) didn’t get upset that the cursor was in a different position.
Having created these 2 macros, I then needed to be able to run the macros. Following some guidance from the internet I wrote a macro which would allow those to run on a given project. That was great. However it meant that whenever I opened a new MS Project file I needed to remember to run the macro, so I wrote a final macro which was an Auto_Open macro, which runs when the file is opened, so therefore whenever the file with the macro in opened it would also run the conditional formatting macro.
And for one final step I put all 4 modules into he Global.MPT file, which means that whenever MS Project opens my conditional formatting macro will open and will run on the current project.
The bit I know you are wanting is what code did he write, and more importantly which module did he put it in so I can get this to work for me. So here are some step by step instructions. Do note that I had the BRAG status in Text1, which is why you will see the reference to Text1 below. If you are using a different field then you will need to change the code below.
As it stands the macro assumes that only one cell is changed at a time. It doesn’t handle the values in Text1 being dragged down with the mouse. I am sure that someone could take what has been done here and expand this. If they do, lets hope they add a comment below so that this can be expanded and be really useful. It also doesn’t work too well if you have collapsed a few summary tasks and then you select (using the mouse) a cell that is above the one that is being updated. It also throws an error if you select a task which is beyond the list of IDs in the project plan i.e. if you go up to row 10 and then you select a blank space beyond row 10 it will error as it doesn’t know the ID of the row you have gone to, because there isn’t one.
- Open MS Project
- Create a Class Module
- Rename the Class Module ‘EventHandlers’ (without the quotes)
- Create 2 declarations
Public WithEvents App As Application Public WithEvents Proj As Project
- From here you can then create 2 handlers which will allow you to do the necessary changes. The first handler checks for a task being changed. This will allow you to remember which task has changed. The second handler checks when the cursor has moved into another cell.
- The first handler example will be:
Private Sub App_ProjectBeforeTaskChange(ByVal tsk As MSProject.Task, _ ByVal Field As PjField, ByVal NewVal As Variant, Cancel As Boolean) Dim MyField As Long MyField = FieldNameToFieldConstant("Text1", pjTask) If Field = MyField Then TaskID = tsk.ID Else TaskID = 0 End If End Sub
- If the cell that is about to change is Text1, then remember the ID that has changed
- Note: you can’t make the colour change in this handler as this is checking before something is changed rather than afterwards. This is why you need the second handler
- The second handler example is:
Private Sub App_WindowSelectionChange(ByVal Wndw As MSProject.Window, _ ByVal sel As MSProject.Selection, ByVal selType) Dim CurrentID As Long Dim CurrentCol As String If TaskID > 0 Then CurrentID = sel.Tasks.Item(1).ID CurrentCol = sel.FieldNameList(1) SelectTaskField Row:=TaskID - CurrentID, Column:="Text1", RowRelative:=True RAGStatus = ActiveCell.Text Select Case RAGStatus Case "B" Font32Ex CellColor:=RGB(91, 155, 213), Color:=RGB(0, 0, 0), Bold:=False Case "G" Font32Ex CellColor:=RGB(146, 208, 80), Color:=RGB(0, 0, 0), Bold:=False Case "R" Font32Ex CellColor:=RGB(255, 0, 0), Color:=RGB(0, 0, 0), Bold:=False Case "A" Font32Ex CellColor:=RGB(255, 192, 0), Color:=RGB(0, 0, 0), Bold:=False End Select SelectTaskField Row:=CurrentID - TaskID, Column:=CurrentCol, RowRelative:=True TaskID = 0 RAGStatus = "" End If End Sub
- This checks to see if there is anything that needs updating – this is the TaskID>0. If there was something that has changed the other handler sets the TaskID to be ID of the task that was changed. If there was nothing changed the TaskID is reset to be zero. This ensures that the macro doesn’t check anything it doesn’t need to
- It then remembers the ID and the field that is currently selected
- It then goes back to the cell that changed and picks up the value of the cell. You can’t use the absolute reference, because if there have been tasks which have been rolled up it moves the cursor to the wrong point
- Depending on the value of the cell it then sets the foreground colour to blank, makes the font not bold and colours in the background of the cell
- It then returns to the cell that we moved to, again using a relative reference to cope with the fact there may be rows that have been hidden. If you use an absolute reference it moved the cursor somewhere other than what had previously been selected
- Finally it resets the TaskID and RAGStatus values to ensure that it doesn’t loop indefinitely
- Having created a class module, how does it actually run, as you can’t see it from the macros menu. You therefore need to create an ordinary module.
- This new module is where the macro will be run from. This needs a few declarations:
Dim X As New EventHandlers Public TaskID As Long< Public RAGStatus As String
- The first one of these is needed to initialise the class module. Note how it has the same name as the class module.
- The second 2 are declared as public because they are used in the class module and we need to remember them all the time the class module is running
- In order to initialise the class module we need another macro:
Sub Initialize_App() Set X.App = MSProject.Application Set X.Proj = Application.ActiveProject End Sub
- This one means that the class module will work. As we declared X about as an Eventhandler, this means that the macros in the class module will now apply to the active project
- Of course what would be really good at this stage is if we could run this everytime
- In order to do this you need to create a new macro called Auto_Open which will run every time the project sheet is opened
- This simply needs to run the Initialize_App that we created earlier
Sub auto_open() Call Initialize_App End Sub
- This means that the macro will run and if Text1 is changed then it will colour the cell in.
I am hoping that this will be useful to people, I know I could have done with this when I was looking to do this