Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Gantt Chart in EXCEL

  Asked By: Gerritt    Date: Sep 23    Category: MS Office    Views: 2614
  

How can I do this with VB code? Note - I am novice at VB.

I want to create a Gantt Chart like graphic by coloring a grid
(columns 1-52
weeks, rows dictated by number of Projects). The Design task would be
colored
red, Install Hardware blue etc. Each project would have 1 row (only)
where
Design, Install Hardware etc would be colored in the appropriate
cells on the
grid (Project1 Design task would show in 26th cell in the row,
Install
Hardware 37th thru 40th)

Note I will import the data (100's of rows) from MS Project, but need
to present it succinctly in Excel for my client. I am long time MS
Project user and can say that it can't do this, there I am looking to
Excel.



ProjectID TaskID Start Week Duration

Project1 Design 26 1
Project1 Install Hardware 37 4
Project1 Install Software 41 4
Project1 Configure 49 1
Project1 Prep 50 2
Project1 Support 52 1

Project2 Design 2 1
Project2 Install Hardware 15 2
Project2 Install Software 17 3
Project2 Configure 20 1
Project2 Prep 21 1
Project2 Support 22 4

Share: 

 

18 Answers Found

 
Answer #1    Answered By: Cais Nguyen     Answered On: Sep 23

There is a file at http://normay.files-upload.com/
that might help.

Gantt Chart Using Conditional Formatting 11_15_06.xls

 
Answer #2    Answered By: Jaspreet Kapoor     Answered On: Sep 23

If you do not want it to be dynamic it would be a fairly simple matter to
write a macro to step through the input table and colour the appropriate
cells.

If you do want it to be dynamic you will need to use the SheetChange event to
redo all the colours in the row  in which the change occurs - sightly more
complex to program and might slow the spreadsheet down noticeably (because it
will be fired by every change in the worksheet).

What parts of this can you do and what parts do you need help with?

 
Answer #3    Answered By: Elaine Stevens     Answered On: Sep 23

To answer your question about - I am a complete VBA
novice (just figuring out how to enable VBA in Excel right now; I do have a
programmer/IT background though).



Not sure what you mean by dynamic (that is my ignorance BTW!). I expect to
import the data  into a worksheet (from MS Project), then run a Marco and I
get the chart. The data would be refreshed periodically (weekly?) and the
chart readied for print. My guess it is not dynamic based on your definition
below.



If the macro/VB code  is already written, I could make changes like set the
right color, range etc. It is my intention to take a VBA class, but right
now, I am stuck with an immediate need that has to be fulfilled. I thought
this could be done w/o VB code, but right now, it seems not.

 
Answer #4    Answered By: Alexis Castillo     Answered On: Sep 23

Dynamic means it changes when you change any of the data  items without you
needing to tell it to run a macro. I will look at it as a static (manual
update)

Can I assume that when you have imported your sheet it will have in each row
(apart from blank rows)

In Column A, the project  name
In Column B, the design  stage
In Column C, the start  week in the range 1-52
In column D, the duration in weeks

There is a blank line after each project

There is a heading which will be in row  1
There will be a blank row on row 2
The data starts on row 3

Each project has six stages as shown below. The names of these stages are
exactly as shown and will never change.

Will any of the stages ever be missed, if so how will this appear in the data
imported from MSProject?

What you want is a spreadsheet showing on each row:

In Column A the Project name
In Columns B to BA (I think that is 52 columns, but lets assume it is - anyhow
the next 52 columns) colours indicating which stage is planned for those
weeks.

When you confirm this, or let me know of any changes I will make a macro for
you. That macro will need to be run each time  you import  the data (once a
week)

Project1 Design 26 1
Project1 Install Hardware 37 4
Project1 Install Software 41 4
Project1 Configure 49 1
Project1 Prep 50 2
Project1 Support 52 1

 
Answer #5    Answered By: Dot net Sachin     Answered On: Sep 23

Do you have a friendly name? I like to address people by their first name if
possible.

Assuming my assumptions were correct the following should do what you want.

Note it is hardcoded to use Sheet1 as the source sheet and Sheet 2 as the
Target sheet. (but those, and the colour definitions can be altered in theiir
respective lines after the Dims.

I do not think it is necessarily the most efficient way to do this and it does
not allow for tracking other han manually. I did develop a good tracking
Gantt in either excel  or access years ago. If you want I will try to find it.
It uses shapes placed on the sheet as drawing objects rather than coloured
cells.

When you use it watch out for lines that have been split by the email programs
and join them. There should not be many.

If you want any more info get back to the list.

*** START code  ***


Sub GenerateGantt()

Dim J
Dim strProject As String
Dim strCurrproject As String
Dim strStage As String
Dim intStart As Integer
Dim intDuration As Integer

Dim wksSourceSheet As Worksheet
Dim wksTargetSheet As Worksheet
Dim lngSourceRowNumber As Long
Dim lngTargetRowNumber As Long
Dim LastRow As Long

Dim ColorDesign As Long

Dim ColorInstHwr As Long
Dim ColorInstSwr As Long
Dim ColorConfig As Long
Dim ColorPrep As Long
Dim ColorSupport As Long

Dim ActionColor As Long

ColorDesign = RGB(255, 0, 0) 'red
ColorInstHwr = RGB(0, 0, 255) 'blue
ColorInstSwr = RGB(0, 255, 0) 'Green
ColorConfig = RGB(255, 128, 128) 'Pink
ColorPrep = RGB(255, 255, 0) 'Yellow
ColorSupport = RGB(0, 0, 0) 'Black

Set wksSourceSheet = ActiveWorkbook.Sheets("Sheet1")
Set wksTargetSheet = ActiveWorkbook.Sheets("Sheet2")



'Find end of data
wksSourceSheet.Activate
wksSourceSheet.Range("A65536").Select
LastRow = Selection.End(xlUp).Row



lngTargetRowNumber = 2
strCurrproject = ""
lngSourceRowNumber = 2
Do While lngSourceRowNumber < LastRow
lngSourceRowNumber = lngSourceRowNumber + 1
If wksSourceSheet.Range("a" & lngSourceRowNumber).Value <> "" Then
With wksSourceSheet
strProject = .Range("A" & lngSourceRowNumber).Value
strStage = .Range("B" & lngSourceRowNumber).Value
intStart = .Range("C" & lngSourceRowNumber).Value
intDuration = .Range("D" & lngSourceRowNumber).Value

End With
With wksTargetSheet
If strProject <> strCurrproject Then
strCurrproject = strProject
lngTargetRowNumber = lngTargetRowNumber + 1
.Range("A" & lngTargetRowNumber).Value = strProject
End If
Select Case strStage
Case "Design"
ActionColor = ColorDesign
Case "Install Hardware"
ActionColor = ColorInstHwr
Case "Install Software"
ActionColor = ColorInstSwr
Case "Configure"
ActionColor = ColorConfig
Case "Prep"
ActionColor = ColorPrep
Case "Support"
ActionColor = ColorSupport
Case Else
MsgBox "Unrecognised stage"
Exit Sub
End Select
For J = intStart + 1 To intStart + intDuration
.Cells(lngTargetRowNumber, J).Interior.Color = ActionColor
Next J
End With
End If
Loop

wksTargetSheet.Activate


End Sub

 
Answer #6    Answered By: Renee Lane     Answered On: Sep 23

See response below. I have clarified where needed. Otherwise the spec is
good.


Dynamic means it changes when you change any of the data  items without you
needing to tell it to run a macro. I will look at it as a static (manual
update)

Can I assume that when you have imported your sheet it will have in each row
(apart from blank rows)

In Column A, the project  name
In Column B, the design  stage
In Column C, the start  week in the range 1-52[Dinesh ] for now, though my
forecast horizon could go up to 18-24 months
In column D, the duration in weeks

There is a blank line after each project[Dinesh] yes, for now, although
prefer no blank line; the extract pushes out rows  w/o blank lines; but I can
insert manually if it helps keep the VBA simple.

There is a heading which will be in row  1
There will be a blank row on row 2
The data starts on row 3

Each project has six stages as shown below. The names of these stages are
exactly as shown and will never change.

Will any of the stages ever be missed, if so how will this appear in the
data
imported from MSProject?[Dinesh ] good Q; stages can be missed. e.g a
project may not have Design or a Design and Install HW etc.

What you want is a spreadsheet showing on each row:

In Column A the Project name[Dinesh ] ,leave 1-2 columns spare, start in
Column D instead
In Columns B to BA (I think that is 52 columns, but lets assume it is -
anyhow
the next 52 columns) colours indicating which stage is planned for those
weeks.



When you confirm this, or let me know of any changes I will make a macro for
you. That macro will need to be run each time  you import  the data (once a
week)

Project1 Design 26 1
Project1 Install Hardware 37 4
Project1 Install Software 41 4
Project1 Configure 49 1
Project1 Prep 50 2
Project1 Support 52 1

 
Answer #7    Answered By: Volney Fischer     Answered On: Sep 23

The only line I think you may need to change will be

For J = intStart + 1 To intStart + intDuration

Which becomes

For J = intStart + 2 To intStart + intDuration + 2

This is to allow the two blank columns.

No need to worry about blank lines. If they are there it will skip them
anyhow.

 
Answer #8    Answered By: Sophie Campbell     Answered On: Sep 23

Now, I just need to focus on creating time  headings and
formatting. The headings could be done with a macro also (since this is a
forecast, the start  month will change as you move forward.



To your point "I do not think it is necessarily the most efficient way to do
this", I agree. While I call it a Gantt chart  (that's how most people
understand), it's actually (what I call) a Heat Map. It is not intended for
tracking projects. It is more a program level view (to identify cross
project issues) as to where the work (and work demand) is concentrated.
Ideally (not sure excel  is the right tool?), the chart could be just one row
that has all the projects  'stamped' on it. Each project  would stamp some
color on the right cells. The darkest spots would be areas of interest (or
concern) and investigation. However, I think I am dreaming:-).

 
Answer #9    Answered By: Adalwine Fischer     Answered On: Sep 23

I enjoyed creating that and am pleased it works.

If you have problems with the rest of it let us know.

I would simply put the dates for all the columns except the first shaded one
as x plus 7. (i.e. the formula in E1 would be "=D1+7" copy this formula to
all the relevant cells to the right.and format the cells as dates) then you
only need to put the first date in D1. I would do this manually.

The single line idea is feasible but I think it would be a pain to track back.
One option would be to change the present  macro to add a number  1 to 6 (for
the respective stages) each cell  as you colour it - you could set the font
colour the same as the interior colour so the numbers are not visible. Then
use the countif function to put a row  of counts (I would put them at the top
rather than the bottom) for each stage. The higher numbers would show  the
higher concentrations. You could colour these as well but the code  to do that
would need to be at the end of the macro as it would be working off data
changed by the earlier part of the macro.

 
Answer #10    Answered By: Kristin Johnston     Answered On: Sep 23

If you can guide me to changing the colors (need more somber, corporate
colors, gray, blue, greens etc:-)), that would be great; especially need to
change black.

 
Answer #11    Answered By: Beatriz Silva     Answered On: Sep 23

Cancel the last request. I am able to change the colors.

 
Answer #12    Answered By: Yvonne Watkins     Answered On: Sep 23

One of my functions is as a scheduler in the engineering/construction
industry. The premier scheduling program is Primavera P3 - very
capable scheduling program but horribly when it comes to graphics. I
started to use the method of shading cells to represent schedule
graphics, but the limitation of 255 columns can be quite limiting
(only about 70% of a calendar year or just over a year for workdays)
If you are tracking a project  greater than this timeframe then this
methodology will not work.

I have developed another method for producing bars from autoshapes to
stretch over columns which represent any timeframe I want. Without
getting into all the code  (because the code I have written is pretty
user unfriendly due to my amateur status) here is the concept I used.

The key code is the following:
MyDocument.Shapes.AddShape(msoShapeRectangle, FromLeft, FromTop,
BlockWidth, BlockHeight).Select
Where:
FromLeft is the distance from the left side of the sheet to the left
side of the drawn object
FromTop is the distance from the top of the sheet to the top of the
drawn object.
BlockWidth is the width of the drawn object
BlockHeight is the height of the block

Calculation for the FromLeft value is as simple as a loop to check
the date of each column timeframe which the start  date would fall.
(For example: if each column is 1 month then the date within the
header cell  may be mm/1/yy (and formatted as mmm-yy) and I would loop
through each header cell until my month and year for start are the
same as the month and year for the header cell. I could then use the
left value of that header cell or get more specific by taking the
left value of the cell and adding the percentage of the cell width
based upon the ratio of start day / number  of days in month)

Calculation of the BlockWidth is the same as FromLeft, except to use
the finish date to do the lookup and then taking the difference
between the finish date left - start date left.

Calculation of the FromTop and BlockHeight would be the top value and
row height of the row  in which the bar is to be placed. This can be
further refined to use a slightly larger top value and slightly
smaller height value to get a bar within the row rather than the
entire height of the row.

This process can be looped for as many activities as you have. Start
or Finish Milestones (lines with only one date value) can be treated
differently with a different autoshape (myself, I use a Diamond with
width equal to height and left value with a further deduction of half
the width to center the diamond on the date) You can also use a code
field to designate what color or pattern the shape should have.

Areas of complication:
Start date must be earlier than finish dates or checked to see if the
two dates are the same.

 
Answer #13    Answered By: Yvette Griffin     Answered On: Sep 23

I am seeking the following improvements to the macro; any help appreciated.

1) Allow the ability to set starting cell  from the macro paints the gantt
chart (b4, c3 etc)
2) The macro clears teh whole sheet2 (I think) before proceeding to paint; Add
the ability to clear from starting cell from item 1 above
3) Allow the ability to group/sort after the gantt is painted. That needs
additonal columns to the left ProjectID. The macro will fill rows  in these
columns by copying a specific cell from datasheet(sheet1) e.g Territory,
SalesDept.

ProjectID TaskID start  Week Duration

Territory SalesDeptID ProjectID Tasks
North Direct Project1 Design 26 1
Direct Project1 Install Hardware 37 4
Direct Project1 Install Software 41 4
Direct Project1 Configure 49 1
Direct Project1 Prep 50 2
Direct Project1 Support 52 1
South Tele Project2 Design 2 1
Tele Project2 Install Hardware 15 2
Tele Project2 Install Software 17 3
Tele Project2 Configure 20 1
Tele Project2 Prep 21 1
Tele Project2 Support 22 4

 
Answer #14    Answered By: Brent Brown     Answered On: Sep 23

To continue my quest for that perfect Gantt chart  I need some
insight into the workings within Excel color palette.



I have spreadsheet (A) that has the colors I want to use for the Gantt
chart. Therefore, I get the RGB numbers from A and plug into the VBA code  in
the Gantt bar spreadsheet. When I compare colors side by side with both
spreadsheets open, the colors do not match.



For example, color set (255, 204,153) in VBA, results in color (255,255,153)
in the Gantt. I have looked for logic errors, and do not see anything
obvious (but I could be wrong). If anyone can point me to related material
or different line of inquiry to pursue, I would greatly appreciate.

 
Answer #15    Answered By: Garry Sanchez     Answered On: Sep 23

Go to www.iconico.com and get f'ree ColorPic.

It is a wonderful tool which allows you to click on any area of the screen and
tells you the rgb value. It also allows you to blend and modify colors. I
use it all the time  for web design.

AFAIK If you use the same rgb numbers on the same screen you should get the
same color. If you are using two screens they will not match unless you can
tweak them to.

 
Answer #16    Answered By: Chung Tran     Answered On: Sep 23

If 255 columns is a limitation, consider a move to Excel 2007. It allows
many more columns than that.

 
Answer #17    Answered By: Salvador Alexander     Answered On: Sep 23

Isn't that like saying "if your code  is slowing down your machine,
you should consider getting a faster computer"?

There are also considerations with respect to printing graphics. I
have used the other method, but printing something useful (column
headings being visible and shrinking column widths to their bare
minimum) is a challenge itself due to the % reduction required to get
all the columns on the printed page and still maintain a readable
font size in both printed and onscreen view.

 
Answer #18    Answered By: Andrew Bryant     Answered On: Sep 23

No. It is like saying: if your computer is not fast enough for what you
want to do, one option is to get a faster computer.

The 255 columns was mentioned as a limitation. I commented on the fact that
255 columns need not be a limitation. Sounds like a reasonable comment to
me.

 
Didn't find what you were looking for? Find more on Gantt Chart in EXCEL Or get search suggestion and latest updates.




Tagged: