help with autoshapes and gantt charts.

**0neup** · 09-07-2009, 09:13 PM

Hi, im a bit of a noob when it comes to excel. I was just wondering if there was anyway to make my current job easier.

What I have to do is input project information into an existing excel spreadsheet with alot of headings such as project number, suburb, description, tender date, due date for completion, project manager etc... that bit is simple enough and I dont need help with that.

What I need to do next is create a unique gantt chart on a seperate sheet within the file with Auto shapes that are seperated into swimlanes such as major projects, minor projects, internal projects etc... although these autoshapes are like conventional gant chart bars and have to be placed by their planned start and completion dates it is also required that the autoshape contain some info about each job such as its number, suburb, description and project manager. So far I have been doing this manually and was wondering if there was a way that these autoshapes could be auto generated as the data is entered into the first sheet.

If anyone could be of assistance that would be great

Thanks

**Andy Pope** · 09-08-2009, 04:09 AM

You can write code to create the autoshapes and textboxes.
You would need to run the code and create all the elements each time the data source changes.

If project planning is part of your job would it not make more sense to get software, such as Project, which is designed to do just this task?

**0neup** · 09-20-2009, 08:00 PM

Yes it would make sense especially since it would make more sense with my degree, however my coordinator prefers excel as the result is more graphical and apparently as a result, more reader friendly. Im just a student so who am I to complain.

Thanks for your help but do you think you could give me an example of the code I would need to use?

Thanks

**Andy Pope** · 09-21-2009, 05:54 AM

Have a look at this previous thread
http://www.excelforum.com/excel-prog...th-shapes.html

**0neup** · 09-21-2009, 09:38 PM

Hey Andy,

Thanks for that but its not exactly what I was looking for. I have attached a file of an example I have made if you have the time to look at it would really be appreciated!

Sheet 1 'Data' is where the project managers enter their data, although in my example not all fields are used in the file in use they all definitely are. I have only filled the fields that would affect the example.

Sheet 2 'Gantt' is how my coordinator would like the charts to be presented. Easier to understand and more visual for our frequent meetings on the plan. I have put an example of where the dates and information required is comming from.

If the whole process can be automated from the data sheet into the gantt sheet that would be great. If the gantt has to look different that would be fine, however it still needs to be presented within the project managers 'swim lane' and have a description of the job (dates, suburb, description, cost etc..)

If you could help in any way I would be so thankful.

Thanks.

**Andy Pope** · 09-22-2009, 05:08 AM

The thread I posted to has all the code you need.

You need to identify the column on the gantt sheet that includes the start date of any shape you want to add.
You can then use the Left and Top/Height property of the range to position a shape.

You then need to locate the finish column. This will allow you to calculate the Width of the shape from the left/width of start cell to left/width of end cell.

Once you have the shape then you can add and format any text you want displayed.