a Gantt chart.. well, kinda. to chart events on a schedule

scorp007

New Member
Joined
Sep 27, 2014
Messages
9
My experience has been mainly with procedural programming. I am basically a VBA neophyte. babe in the woods. I have created some VBA macros that worked.. but they are crude. I am not used to working with OO type languages (this is the first). So, bear with me if I ask some stupid questions.

I have also used some very sweet and to the point macros - that I've copied from this forum. And learned much from them. So I thank the people here for contributing so much.

I am trying to create a chart from some data, and I would like to use VBA to do this, as the schedules change - people come and go, projects change their times sometimes. I would like a type of gantt chart.. but not really, because I don't care about showing progress. Just what project jobs are running when. I have jobs that run weekly, monthly, annually, biannually, and just kind of random because they depend on other factors. Gantt charts seem to be only able to show one event per row (from the ones I've seen created from Excel). I need to be able to show several events per row - for instance, a job that runs biannually will have 2 "events" positioned by date for the row for that project.

I would think this wouldn't be hard, but I have been going around in circles trying to find an example that I could use, and can't. I probably am not looking for the right type of chart.

My goal is to be able to take this data and create a chart with a VBA macro (because the data changes sometimes) where I can go right to the date I'm interested in (usually today or next week) and see what Jobs/projects are running in what time frame at that moment.



Below is my data and a crude representation of what I have in mind.

ok.. I cannot load a picture from my pc here.. and I can't seem to I will try to paste something below the table. So, I will try to copy what I have in mind to another post.

ProjectEvent CycleEvent Type
pA201424b
pA201450b
pB201406-201437w
pC201439a
pD201403-201419w
pD201433x
pD201448x
pD201411x
pE201407x
pE201413x
pE201425x
pE201428x
pE201444x
pf201420-201436
m

<tbody>
</tbody>
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
not very good. I created spreadsheet with different colors for the different types of events - weekly (w), monthly(m), bi-annual (b), annual (a), random(x).
The time column headers are year and cycle -which is the ISO week number.
oh well. I was hoping to create a chart that would show the schedule better. :(

YEAR 2014 2015
cycle (ISO week number 0102030405060708091011121314151617181920212223242526272829303132333435363738394041424344454647484950515253010203040506070809101112131415
Proj_A B B
Proj_B WWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWW
Proj_C A
Proj_D wwwwwwwwwwwwwwwww x x x
Proj_E x x x x x
Proj_F m m m m m

<colgroup><col><col><col span="68"></colgroup><tbody>
</tbody>
 
Upvote 0
I found a picture kind of like what I would like to do. 1 project per row. Columns with sequential week numbers. and the events shown with some indication of duration as well as time. This is just a template, though with no VBA.

ProjectTimelineSummary.png
 
Last edited:
Upvote 0
again... this only allows one event per row. If I had that situation, I would know how to do this, as this technique is used a lot and documented often on the web.

I need to be able to chart multiple events per project row.

but I appreciate you trying to help.

Lynne
 
Upvote 0
Hmm, well in that case it might be worth looking at something that makes a chart like in that link but with a bunch of zero value bars and then creates text boxes containing whatever relevant text you need with the right colour, etc, and then places them in a certain place relevant to a fixed point of your choosing (like the top left corner of the chart). I don't know how to write that in VBA as I'm a novice there too but maybe it gives you an idea for a direction to think in? Sorry I misunderstood before this isn't something I've ever had to do myself.
 
Upvote 0
Steph, thanks for your reply.

I was hoping there was a more straightforward solution going from data to chart.

I realize that I can just use project as row, and ISO week # as column and color the appropriate cell for the project/week event. But I was hoping to see how others more experienced with creating charts would do this with VBA.

Lynne
 
Upvote 0
Does anyone know how to do this using VBA? I probably should not have mentioned "Gantt" in the title. It is not a simple gantt chart. more like a schedule to show multiple jobs and when they run.

What I would like to know is if I can use the data in my first post with VBA to create an event chart that can show more than one event per row. Maybe a scatter chart would be better, or a multi horizontal line chart.

Is this something really difficult? I thought it would be straightforward, just that I didn't know how to set it up for a straightforward convert data to chart.
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,172
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top