Microsoft KB Archive/74598

{| = Excel Macro to Create Gantt Chart =
 * width="100%"|

Last reviewed: November 4, 1994

Article ID: Q74598 The information in this article applies to:
 * Microsoft Excel for Windows, versions 2.x, 3.0, 4.0
 * Microsoft Excel for OS/2, versions 2.2, 2.21 and 3.0

NOTE: The following article is provided as information only.

Because of legalities, this article should only be     transmitted via FAX to customers, not read over the phone. When sending out this article, a disclaimer should be attached.

SUMMARY
You can use Microsoft Excel to create a Gantt Chart showing task status for project planning and control. The macro listed below creates a basic Gantt Chart. Instructions on how to use the macro follow.

Enter the following in a macro sheet:

A1: Gantt_Chart A2: A3: =ECHO(FALSE) A4: min=OFFSET(SELECTION,1,1,1,1) A5: title_text=INPUT(&quot;Please Enter the Title for this Chart.&quot;,2) A6: =NEW(2,2) A7: =GALLERY.BAR(3,TRUE) A8: =SELECT(&quot;S1&quot;) A9: =PATTERNS(2,1,1,1,,2,1,3,2,FALSE,FALSE) A10: =SELECT(&quot;Axis 2&quot;) A11: =SCALE(1,1,1,TRUE,TRUE,FALSE) A12: =SELECT(&quot;Axis 1&quot;) A13: =SCALE(min,TRUE,TRUE,31,TRUE,FALSE,FALSE,FALSE) A14: =FORMAT.TEXT(,,2) A15: =FORMAT.FONT(0,1,FALSE,&quot;Helv&quot;,8,FALSE,FALSE,FALSE,FALSE) A16: =GRIDLINES(FALSE,FALSE,TRUE,FALSE) A17: =ATTACH.TEXT(1) A18: =FORMAT.FONT(0,1,FALSE,&quot;Helv&quot;,12,TRUE,FALSE,FALSE,FALSE) A19: =FORMULA(&quot;=&quot;&quot;&quot;&title_text&&quot;&quot;&quot;&quot;) A20: =SEND.KEYS(&quot;{ESC}&quot;) A21: =RETURN

MORE INFORMATION
To make this macro work properly in Microsoft Excel versions 2.x, modify the macro above with the following changes:

A6: =NEW(2) A8: =SELECT(&quot;S1P1&quot;) A20: =SELECT(&quot;&quot;)

Steps to Use the Macro
  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  Select cell A2 and format it with the month/day/year format you want to use on the chart. Highlight A2:D8 and activate the macro by choosing the CTRL key combination you defined for Gantt_Chart. Enter the chart title when prompted.