Tips on using this forum..

(1) Explain your problem, don't simply post "This isn't working". What were you doing when you faced the problem? What have you tried to resolve - did you look for a solution using "Search" ? Has it happened just once or several times?

(2) It's also good to get feedback when a solution is found, return to the original post to explain how it was resolved so that more people can also use the results.

How to create an EXCEL GANTT CHART using VBA

8 replies [Last post]
Mohamed Ghafir
User offline. Last seen 21 weeks 6 days ago. Offline
Joined: 29 Sep 2017
Posts: 5
Groups: None

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.

**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)

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)


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


Bian Mutang Tagal
User offline. Last seen 1 week 2 days ago. Offline
Joined: 12 Jun 2014
Posts: 8


Bian Mutang Tagal
User offline. Last seen 1 week 2 days ago. Offline
Joined: 12 Jun 2014
Posts: 8

 Hi Mohamed Ghafir, it would be nice to have a go at your program. A few years ago I developed an Excel VBA program AutoGrouper that groups and formats hierarchical WBS data extracted via copy-paste from Primavera P6. Essentially with just one click the raw data pasted to Excel would be transformed to the familliar default Activity Table look (blue, red, yellow, activity level, etc). The next step was to show the corresponding Gantt Chart but I never got round to figuring out how to do it.

It would be nice to see how we could integrate our code. I'm not a programmer by trade but I did try to adhere to proper programming practice such as short line length, CamelType, OOP concepts, error-catching, and using MZ-Tools. The only problem is I need some time remembering the password to my code, as this was before I started using password managers >.<

Will PM you my email address

Albert Teng
User offline. Last seen 5 weeks 36 min ago. Offline
Joined: 10 Jun 2020
Posts: 2
Groups: None

Hi Mohamed Ghafir,

Would appreciate if you could share the program or send link to my email, Thank you in advance.

User offline. Last seen 16 weeks 1 day ago. Offline
Joined: 19 Mar 2020
Posts: 2
Groups: None

Dear Mohammed,

Could you please send me this  program to my e-mail adress

Bui Duc Thuan
User offline. Last seen 16 weeks 1 day ago. Offline
Joined: 9 Jul 2013
Posts: 2
Groups: None
Dear Mohamed Ghafir, May you share with me a copy? Thank you Thuan
hafiz abidin
User offline. Last seen 1 week 6 days ago. Offline
Joined: 24 Jun 2016
Posts: 2
Groups: None

Hi Mohamed Ghafir,


I would like  to try this program, can you share this progrom along with instructions. Kindly email to me at

Christopher Middl...
User offline. Last seen 17 weeks 6 days ago. Offline
Joined: 24 Apr 2019
Posts: 2
Groups: None
Hi Mohamed I have sent a message with regards to receiving this via my email address as outlined in the message. Your work is greatly appreciated.
Zoltan Palffy
User offline. Last seen 5 hours 58 min ago. Offline
Joined: 13 Jul 2009
Posts: 2723
Groups: None


Since you are planning to give this for free to the planning community

I would like to try this program. Can you please send this to me along with any instructions.

If you want I can help to promote this program.

I have sent you a message here on Planning Planet with my eamil address