Condiional Formatting in MS Project

20 October 201520 October 2015 Stuart

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

You’re Great

29 May 201529 May 2015 Stuart 1 Comment

I am prompted to write this following a few discussions I have seen and contributed to over the past month. It was initially the discussion on Linked In about the value of the PDF CV (apparently from a recruiter’s point of view it’s terrible). Then at the PMO flashmob I was handed a different style business card, which just had PMO on one side, and the person’s details on the other. I then saw the write-up from that flashmob. All of this tumbled around a bit and it got me thinking where are we?

How recruiters see CVs

Chatting to Lindsay Scott from Arras People it was clear that she (as a recruiter) sees things differently from the rest of the people who I spoke to about a CV. In order for the recruiter to see your CV (which is obviously great), then a keyword search is done, so if you don’t have the correct combination of keywords then you aren’t going to appear in the short(er) list that is going to be presented to the recruiter.

Only once you have passed the keyword test, which by the way is done against a database and not your CV will someone then actually look at your CV.

How candidates want CVs to appear

You (like me) have probably spent hours poring over your CV, making sure that aligns to the keywords that are in the job advert. Making sure not only the spelling, but the grammar is perfect. Therefore when you send your CV it is so sparkling everyone is dazzled and therefore it must be the one that is read, and everything else is ignored. You get an immediate pass into the interview round, because it is so obviously you are made for the role.

Reading a CV

I have spent some time reading CVs when I have wanted to recruit. The first thing to say is that I read a CV for a contract role differently from a CV for a permanent role. Why? Well put simply the contract person is one I want to come in and be able to start the job first day. Whereas the permanent person I want to see an element of where they are going to be, what will be their future in the organization? So yes, even at CV reading stage I am looking at career development.

There are other things I look at regarding career history, looking for length of contracts, renewals, drive and ambition as well as technical skills. If I am then interested in them I may do some research on Linked In to find out a bit more about them. I typically get a second opinion as well. This is normally done by getting the other person to read the CVs and then we compare notes, so as not to influence the other person. To ensure a level playing field I try and sort them in surname order as well.

A new generation of CVs?

As we all seem to want different things from a CV, perhaps this is the wrong format. We are using something that is paper based (what ever happened to that electronic office?). Perhaps we should start to go the HTML route (for those non technical think of it as a web page). This would then allow the recruiters to be happy as we could all stuff our web pages with those wonderful META tags which have all of the buzz words. We can get the fancy formatting that the person writing the CV wants. We can even get a nice picture included as well. It then means that the format is contained. If the recruiter then wants to put their contact details as a header & footer, then all they need to do is to put a frame around the CV content and put their information at the top & bottom. For those people who like paper, as it allows it to be scrawled on and the o’s and a’s coloured in during a dull meeting moment, you can print the pages as well.

Hang on a minute though …… this sounds like I have just described linked in? If we have that, then remind me, why do we all have a CV and a business card?

PMO Maturity first steps

24 May 201524 May 2015 Stuart

Make us better

I wonder how many times people in PMOs have heard something similar to this. You get pulled aside by one of the senior managers in the department and get asked to improve what you are doing. It’s simple they explain, if you are such an expert, then all you need to do is suggest a few things, get them implemented and the world will be a better place.

So where to start?

I received one of these challenges the other day, so I thought it would be easy to ‘just make the PMO more mature’. However that seems sometimes like saying to a kid to ‘grow up’. Does it mean that the kid should get a job, worry about the mortgage rate and pensions? Or does it mean something completely different?

So when looking at the maturity level of the PMO I started by looking to see if I could find anything about PMO Maturity levels. I was very surprised to find only one thing that directly related to a PMO maturity level, something called a PMO Maturity cube, which did help to a certain extent, but I couldn’t see it tie up with other things I had read.

I then fell back on the good old P3O manual, and that advised when improving a P3o where you needed to start with was a P3M3 assessment, and there is one of those in the P3O manual, however I thought that it would be good to see if I could download one from the AXELOS website. At the time of writing this is no longe available as a free download from their store, but if I wanted to pay several hundreds of pounds I could have access to one.

Then where?

Having drawn a blank there I decided that perhaps I could make do with the information that was in the P3O manual. Having got my assessment, and bear in mind that the P3O manual suggests that at level 3 or above we would have a virtual model, and I was definitely not in one of those, then I had to be below level 3, what things could I get the PMO to do? If should therefore be quite simple. All I would need to do is have a look in the wonderful Appendix F and it would tell me that in orer to increase my maturity here are the simple things I need to do at a level 1, then I can add the following things in for level 2 etc, all nicely split down into categories such as risk, benefits i.e. nicely aligned to a P3M3 assessment model. After all they all come from AXELOS right, so everything should be nicely aligned.

Well I was sadly disappointed. In Appendix F of the P3O manual then there is nothing that mentions a P3M3 level, so that has drawn a blank.

Where next?

So if I can’t do something so simple then do I have to make it up each time. Is there nothing out there that suggests how we can improve from level to level. After all if the PMO aren’t helping the organisation improve, who is doing it. Yes the senior management can suggest and promote ‘betterness’, but they aren’t the people who actually get involved in the detail.

So I welcome sensible suggestions in the comments below for where I (and anyone else reading this) can go to to find out the steps to improve their maturity within their PMO

Training PMOs

17 March 201517 March 2015 Stuart

Training for PMOs

Following on from my previous post one of the comments asked what training is available for PMOs?As far as I am aware there are only 2 accredited courses which are primarily aimed at the PMO person. These are the PPSO courses (Foundation and Advanced) and the P3O courses (Foundation and Practitioner). There may be other courses out there, which are aimed at PMOs, but are not examined upon. Although courses without accreditation can be useful for those attending them, it can be difficult for organisations to understand whether people who have attended these courses are now ‘better’ individuals who are able to do the job they were employed for. Hence the popularity of exam focussed courses.

PPSO

The PPSO exams was produced to align to the PPSO manuals (Volumes 1 & 2). Where PPSO stands for Project and Programme Support Office. These were written by David Marsh back in 2000, and I believe they have had an update since, in about 2004 that still makes them over a decade old. Does that make them not worthwhile? I would say they still do have a place as they can explain some of the basics (at foundation level) for a PMO person. However as they are not aligned to current AXELOS/PMI/APM terminology then people who have been on other courses end up having to learn a new set of out dated terminology to pass.

P3O

The P3O exams align to the current P3O manual, which was updated in 2013, and therefore do align strongly to the rest of the AXELOS manuals. This makes them current for those individuals taking them. However at present due to the focus of the P3O manual being portfolio based (and yes the 2013 refresh is better than the 2008 original in giving programme and project offices a look in), this may not be of as much relevance to certain PMOs. As it focusses on setting up a PMO then it does have a good deal of relevance for the PMO leader/manager as it allows them to come away with an understanding of how a PMO needs to be structured in a large organisation.

What’s missing?

So if we have both of these courses what is missing that will enable our PMOs to improve and become the great individuals they strive to be?

Well in my view what is missing is the detail. So often I speak to PMOs, and see questions on linked in, that ask ‘How’ as in ‘How can I get risk management to work’; ‘Do you have a template for lessons learned’. I think what is required is a matrix of training modules that can be built up so you can either take them at a topic level e.g. risk, or they can be taken at a skill level e.g. reporting. What we then can do from an exam perspective is that we take this as a points system. So each one of the modules is worth a set of points, based on complexity, knoweldge imparted etc. You then require a minimum set of points in order to become a level 1 PMO person, a higher level of points to become a level 2 PMO person etc. By organising the training this way it would enable the people who don’t do a particular PMO topic to skip that topic, but still become qualified as a level 1 PMO. Like most qualifications that work this way (I am thinking Open University here) there would need to be a minimum set of compulsory modules at each level, but I am sure this can be incorporated.

Worked example

So this is how it see it working as a generic example

		Topic
Theme	Definitions (including templates)	What is this topic about? What is in a typical template for this topic, and what are the definitions for the content of each field including any lookups used
	Reporting & analysis	How can you provide roll up reporting? What data is required in order to produce the relevant reporting? What can you imply based on the report to turn the data into information
	People	How to feedback to a person outside the PMO that they have misunderstood something (or it is wrong) How to run workshops to help identify/update/close items related to this topic How to identify when training is required, and at what level Typical areas of conflict relating to this topic and suggestions as to how this may be resolved.
	Process	What is the process to create/update/close/delete an item for this topic How and when the topic will be used in a project/programme/portfolio lifecycle

And to translate that for a couple of subject it may look something like this:

		Topic
		Risk	Benefits
Theme	Definitions (including templates)	What is a risk? Impact, probability, response types What does a risk register look like What would a risk checklist look like	What is the difference between a cost & a benefit? Different types of benefit – financial & non-financial How can you turn a non-financial benefit into a financial one What does a benefit register/tracker look like
	Reporting & analysis	How can you provide roll up reporting? What data is required in order to produce the relevant risk reporting? How to read the progress report to identify new risks Identifying risks which are now issues	How do you track potential benefits Can you track actual benefits How to construct a Key Performance Indicator Double counting of benefits. How to identify. How to assign a benefit to a particular project
	People	How to run a risk workshop How to chase down a risk action How to handle someone who says they don’t have any risks on their initiative	How to run a benefits workshop How to tell a senior manager that benefits have been double counted and their business case is no longer valid.
	Process	When would you discover a risk? How and when are risks escalated? When can a risk be closed? What level of risk would be appropriate for a project/programme/portfolio?	When are benefits tracked? How to track benefits within your organisation – linkage into other areas What happens to tracking benefits after a project closes

Your thoughts?

Your views appreciated as to whether this may work. It would obviously require a group of people to write the manual, and then an examining body to put together the course material. I don’t think this will become a degree in PMO followed by a Masters in PMO, but something more flexible that our current PM and PMO training that exists.

P3O and Beyond

10 March 201510 March 2015 Stuart

As a follow up from my last post on the future of the P3O I attended the PMO flashmob to hear what Eileen Roden had to say. You can find the full write-up here, but I thought I would give my perspective on the matter.

After a brief career history from Eileen on how she had become the author of the P3O manual, we separated into groups to look at what PMOs had stopped doing, were being asked to do more of and where staring to be asked to do. The full write-up is on the flashmob site.

What I took from the meeting was that some things had changed for the PMOs, and we were moving away from being secretaries and admin people, as shown in the balance of the individuals in the room, there was a fair split between males and females. When I first started in PMOs about 20 years ago there were many more females than males in the profession and those males that were there saw it as a pathway to doing something else.

The role was starting to form as there were many things in the PMOs can do category, with more being added all the time. However there was no real agreement in what a PMO can do in the future.

I was therefore disappointed in the flashmob as it didn’t tell me what I wanted to hear, which was where are PMOs going, what is the vision that we need to aspire to, who are the thought leaders in the field of PMOs. It was quite clear from the people I spoke to during the event, and afterwards in the more social surroundings of the local pub, that most PMOs are struggling to get on with the day job and can’t think about what is happening this afternoon let alone where will PMOs go in 20 months let alone 20 years.

I am not sure based on the output of the flashmob whether there is anything that can be used and taken forward as what a PMO should do, as it did seem to link into the maturity of the organisation, the competence of the individuals within the PMO and project management community. What was obvious though is that a lot activities nowadays are being managed as a project within organisations, so the need for Project Managers and PMOs to support them and the organisation is required.

PMOs are here to stay, we just need the individuals within them to become better in understanding the organisations they work within and therefore how the PMO can transform the organisation.