Thanks Thanks:  0
Likes Likes:  0
Results 1 to 9 of 9

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

  1. #1
    New Member scorp007's Avatar
    Join Date
    Sep 2014
    Location
    USA
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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.

    Project Event Cycle Event Type
    pA 201424 b
    pA 201450 b
    pB 201406-201437 w
    pC 201439 a
    pD 201403-201419 w
    pD 201433 x
    pD 201448 x
    pD 201411 x
    pE 201407 x
    pE 201413 x
    pE 201425 x
    pE 201428 x
    pE 201444 x
    pf 201420-201436 m

  2. #2
    New Member scorp007's Avatar
    Join Date
    Sep 2014
    Location
    USA
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: a Gantt chart.. well, kinda. to chart events on a schedule

    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 01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 01 02 03 04 05 06 07 08 09 10 11 12 13 14 15
    Proj_A B B
    Proj_B W W W W W W W W W W W W W W W W W W W W W W W W W W W W W W W W
    Proj_C A
    Proj_D w w w w w w w w w w w w w w w w w x x x
    Proj_E x x x x x
    Proj_F m m m m m

  3. #3
    New Member scorp007's Avatar
    Join Date
    Sep 2014
    Location
    USA
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: a Gantt chart.. well, kinda. to chart events on a schedule

    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.

    Last edited by scorp007; Sep 27th, 2014 at 05:16 PM. Reason: add more explanation

  4. #4
    Board Regular
    Join Date
    Sep 2014
    Posts
    117
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: a Gantt chart.. well, kinda. to chart events on a schedule

    This page may interest you:

    How to Create a Gantt Chart in Excel | Smartsheet

    You will have to use some formulas to get the information as you have it in raw form into the right form to make the chart but that souldn't be too hard. If you need any further help with any of this post back.

  5. #5
    New Member scorp007's Avatar
    Join Date
    Sep 2014
    Location
    USA
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: a Gantt chart.. well, kinda. to chart events on a schedule

    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

  6. #6
    Board Regular
    Join Date
    Sep 2014
    Posts
    117
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: a Gantt chart.. well, kinda. to chart events on a schedule

    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.

  7. #7
    New Member scorp007's Avatar
    Join Date
    Sep 2014
    Location
    USA
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: a Gantt chart.. well, kinda. to chart events on a schedule

    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

  8. #8
    New Member scorp007's Avatar
    Join Date
    Sep 2014
    Location
    USA
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: a Gantt chart.. well, kinda. to chart events on a schedule

    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.

  9. #9
    New Member scorp007's Avatar
    Join Date
    Sep 2014
    Location
    USA
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: a Gantt chart.. well, kinda. to chart events on a schedule

    I found this, and think I will just go through the steps with Record Macro on. See what VBA code that this produces.

    How to Make a Gantt Chart for Repeated Tasks | Katherine S. Rowell

    produces this kind of chart:


Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •