In this video I demostrate using VBA to create an Excel Chart (Gannt) with annotation “Event” diamonds. Lesson from this demo are using VBA to create and add shapes, Adding event code to shapes ans using forms as Popup boxes…

A Gantt chart, commonly used in project management, is one of the most popular and useful ways of showing activities (tasks or events) displayed against time. On the left of the chart is a list of the activities and along the top is a suitable time scale. Each activity is represented by a bar; the position and length of the bar reflects the start date, duration and end date of the activity. This allows you to see at a glance:

  • What the various activites are
  • When each activity begins and ends
  • How long each activity is scheduled to last
  • Where activities overlap with other activities, and by how much
  • The start and end date of the whole project

A recent client had developed a rather crafty Gantt chart strictly with formulas and conditional formatting.  The problem was with 15 projects and 365 cells per yearper  project it had become computationally intense and  as a result ‘buggy’.  I assured him we could come up with a cleaner more elgant solution using VBA to automatically create Gantt Charts.   
Below find:
1) An example of the Gannt Chart
2) The key subroutine I used to generate the BARS   

To demostrate that first green box is constructed with the following call to the DynamicBox subroutine
Call DynamicBox(326, 58.5, 158, 11, "Planned1")


The subroutine to actually add the box is actually quite simple:
Sub DynamicBox(dLeft As Double, dtop As Double, dWidth As Double, dHeight As Double, sName As String)

'************************************************************
'** Date:       01/10/2020
'** Developer:  Ray Mills
'**             Exceland VBA Developer
'** Purpose     Create a Custom Horizontal Box
'**
'*************************************************************

ActiveSheet.Shapes.AddShape _
 (msoShapeFlowchartProcess, dLeft, dtop, dWidth, dHeight).Select
 If InStr(sName, "Planned") > 0 Then
 Selection.ShapeRange.Fill.ForeColor.RGB = RGB(0, 255, 0)
 ElseIf sName = "lnToday" Then
 Selection.ShapeRange.Fill.ForeColor.RGB = RGB(255, 0, 0)
 Else
 Selection.ShapeRange.Fill.ForeColor.RGB = RGB(0, 0, 255)
 End If
 
 Selection.ShapeRange.Fill.Solid
 Selection.ShapeRange.Fill.Visible = msoTrue
 Selection.Name = sName


End Sub

AS long as you know that you can set dleftStart as cells(4,9).left you have a starting left postion. It should be relatively simple for you from there.
Build a Gannt Chart Using VBA
Build a Gannt Chart Using VBA
Raymond Mills MBA, MS

Raymond Mills, M.B.A., M.S.  has spent over 20 years of his career as Accountant, Investment Bank and Credit Card Technical Auditor/ Data Analyst.  His specialty was using Excel to get Big Databases including Teradata, Oracle, Sequel Server and Sybase to give up their secrets.

Ray has said “I love nothing better than using VBA to unleash the power of Microsoft Office.” If you have a challenge with Excel, Access or Word and would like to speak with Ray, you can get his contact details by clicking here: Contact Me