What is driving your decisions?

I recently came across this interesting question from Thomas in LinkedIn MS Project Schedule group


The simplified version of his requirement: Filter all milestones in the next 6 months along with its immediate predecessors. In the below plan, the current date falls in October and his requirement is to highlight Go Live 1, Build 1 and Build 2 (as Go Live 1 is the only milestone in the next 6 months and its immediate predecessor are Build 1 and Build 2)

I evaluated different options such as (a) custom fields with formula, (b) custom filters, (c) write VBA code. The first two options was eliminated as the formula can test only the current task and cannot test its predecessor tasks. To test the task and its predecessor, VBA macros is the solution.

Let’s start with a pseudocode
1. For each task in plan do
1.1. If task is not empty and active
1.1.1. If the task is milestone and is happening in the next 6 months Mark the milestone to True For each predecessor of the current task Mark the current predecessor to True
2. Highlight all the tasks with Marked as True

Now you need to translate each statement by using Project model object, properties and methods.
1. Click View tab. Click Macros dropdown and select Visual Basic


2. In the editor, double click your current project on the left panel. This will display the code editor.
3. Now, insert a procedure named FindMilestonesAndImmediatePredecessors by selecting Insert menu, and then Procedure.

4. Let’s first loop through all the tasks in the current project

5. Now mark the current task if it is non-empty active task and milestone happening in the next 6 months.

6. Now loop through the immediate predecessor of the milestone and mark these tasks

7. After marking the milestones and its immediate predecessors, you can highlight these tasks by creating a filter MilestonesAndImmediatePredecessors

The complete code is shown below

8. Now to run this macro click View tab. Click Macros dropdown and select View Macros. Select the macro FindMilestonesAndImmediatePredecessors and click Run button


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s