How to create an EXCEL GANTT CHART using VBA

I'm a planner in the UK, and been working as a planner for just over 2 years in few different rail projects.

and one of the main challenges is that most of the engineers were NOT reading the P6 PDF programmes, 

even if they did, they really didn't pay attention to the details.

because everytime I talk with the engineers to get updates on their activity, 

them sound like they haven't read the programmes and don't know the dates that they should be aiming for,

and eventually one of them told me it's because the programme is too big,

there's too much text. 

too many columns.

the Ghantt Chart looks complicated because the activities are not sorted in order, 

even if we sorted by finish date the problem wasn't really solved

because there's a feedback loop between multiple disciplines, 

so they will have to go up and down from page to page to know what's the predecessor/successor of a certain activity. 

Doing filters in P6 can diffinately help, but it doesn't solve the problem completely, 

it still requires searching back and forth to follow a sequence of activities.

So after a discussion I found out they don't care to read the programme, 

They ONLY want to know what are the activities they need to focus on in the next 1,2,3 or 4 weeks.

That's IT!

I knew excel may have the solution, because you can easily

highlight a set of columns and look at the activities that lie in the shaded area.

so I searched google for Excel ghannt charts, and I found a bunch of templates,

I used many of them, but non offered anything better than what we have in P6

This is when I discovered Excel VBA,

I started dabbling with it and saw the huge power in this software,

basically, what you can do is limitless! HOWEVER.

It required to either hire a Developer to create a VBA code and template for you,

or learn how to code and do it myself.

I choose to do it myself and took multiple courses on the subject just to make this idea work.

After alot of trial and error and help from some really talented developers, 

I finally got the software that has all the features I want and MORE.

[[wysiwyg_imageupload:7243:]]

**With a click of a button, it can do all of the following

1) as you can see above to create kind of a visual horizontal sequence of activities on excel (instead of 1 activity on each row)

2) It Automatically COLORS activities based on their status

Completed = Blue

Green = Not Started & Not Critical

Orange = In-progress & Not Critical

Red = Critical activity (Not Started or In-progress)

Pink = Near Critical (you can define the near critical)

[[wysiwyg_imageupload:7245:]]3) It positions the activity start and finish date (it's very accurate)

4) You can change the dates columns to be daily or weekly as show below

• 1 day per column,
• or 1 week per column (see below)

[[wysiwyg_imageupload:7244:]]

Since using this, all engineers have been looking and engaging with the programme at every meeting (Finally)!

I'm planning to give this for free to the planning community,

PS: there are extra features (really good & time-saving extra features) that I will talk about later, if I see some interest.

If this is something you are interested in, please let me know and I will give you the free version that I talked about above and I will be very happy to know I made a contribution.

Otherwise I'll be the only user of this tool