ID: Q123260
The information in this article applies to:
In Microsoft Excel, you can create Gantt Charts that show task status for project planning and control. The example in this article shows the steps necessary to generate this type of charts using a Visual Basic, Applications Edition, procedure.
The following Visual Basic macro creates a Gantt Chart.
Microsoft provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support professionals can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific needs. If you have limited programming experience, you may want to contact the Microsoft fee-based consulting line at (800) 936-5200. For more information about the support options available from Microsoft, please see the following page on the World Wide Web:
http://www.microsoft.com/support/supportnet/refguide/
Sub gantt_chart()
Dim rge As Variant
Dim mn As Variant
Dim shtname As Variant
'defines the variables
rge = Selection.Address()
'get the cell address
mn = Selection.Offset(1, 1)
'return the min value for the scale
Title = InputBox("Please enter the title")
'Asks the user for title
shtname = ActiveSheet.Name
'retains the name of current sheet
Application.ScreenUpdating=False
'Turns screen updating off
Charts.Add
'Create a paper model chart
ActiveChart.ChartWizard Source:=Sheets(shtname).Range(rge), _
Gallery:=xlBar, Format:=3, PlotBy:=xlColumns, CategoryLabels _
:=1, SeriesLabels:=1, HasLegend:=1, Title:=Title, _
CategoryTitle:="", ValueTitle:="", _
ExtraTitle:=""
' Basic chart definition
ActiveChart.Legend.Delete
'deletes the legend
ActiveChart.SeriesCollection(1).Select
'activates series 1
With Selection.Border
.Weight = xlThin
.LineStyle = xlNone
End With
'definition for the border for series 1
Selection.InvertIfNegative = False
'turns Invert if negative to false
Selection.Interior.ColorIndex = xlNone
'indicates that the area is set to none
ActiveChart.PlotArea.Select
'select the chart plot area
ActiveChart.Axes(xlCategory).Select
'select axis(1)
With ActiveChart.Axes(xlCategory)
.ReversePlotOrder = True
.TickLabelSpacing = 1
.TickMarkSpacing = 1
.AxisBetweenCategories = True
End With
'axis 1 definition
ActiveChart.Axes(xlValue).Select
'select axis(2)
With ActiveChart.Axes(xlValue)
.MinimumScale = mn
.MaximumScaleIsAuto = True
.MinorUnitIsAuto = True
.MajorUnitIsAuto = True
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = False
.HasMajorGridlines = True
.HasMinorGridlines = False
End With
' Axis(2) definition
End Sub
1. Open a new worksheet and enter the following values:
A1: B1: START C1: DAYS D1: DAYS
A2: TASK B2: DATE C2: COMPLETED D2: REMAINING
A3: TASK-1 B3: 1/1/91 C3: 150 D3: 15
A4: TASK-2 B4: 5/1/91 C4: 21 D4: 31
A5: TASK-3 B5: 7/1/91 C5: 0 D5: 114
A6: TASK-4 B6: 10/1/91 C6: 0 D6: 4
A7: TASK-5 B7: 10/15/91 C7: 0 D7: 31
A8: TASK-6 B8: 11/1/91 C8: 0 D8: 2
want to use on the chart.
menu, and select the gantt_chart macro. In version 97, click Macro on
the Tools menu, then click Macro and select the gantt_chart macro.
The Gantt chart should now be displayed on a new chart sheet. Additional formatting can be added as needed.
For additional information on creating a Gantt, please see the following article(s) in the Microsoft Knowledge Base:
ARTICLE-ID: Q73281
TITLE : Excel: Creating Gantt Charts
"User s Guide," version 5.0, chapters 15, 17, 18
Additional query words: 97 7.00 5.00 gannt
Keywords : kbprg kbdta KbVBA
Version : WINDOWS:5.0,5.0c,7.0,97
Platform : WINDOWSLast Reviewed: May 17, 1999