Dynamic Project Plan
Charts and Visualization Uncategorized VBA

Dynamic Project Planner (Gantt Chart) in Excel

Here we have created a beautiful and dynamic Project Plan (Gantt chart) in Excel. We have given a drop-down to change the weekly and daily view. There are 3 type of status has been given Pending, Completed and In-Progress. We have used VBA to change the View daily to weekly and visa-versa. This is password protect worksheet. The password is “1234

Below is the Weekly View-

Weekly View
Weekly View

 

Below is the daily View:

Daily View
Daily View

 

VBA Code which we have used to change the view-

 

Option Explicit

Sub Refresh_Data()

Application.ScreenUpdating = False

Dim sh As Worksheet

Set sh = ThisWorkbook.Sheets("Project_Plan")




sh.Unprotect "1234"




Dim i As Long




sh.Range("G3:XFD3").UnMerge

sh.Range("G1:XFD3").Clear

sh.Range("G1:XFD3").Orientation = 0


Dim lc, lr As Integer

For i = Application.WorksheetFunction.Min(sh.Range("C:C")) To Application.WorksheetFunction.Max(sh.Range("D:D"))

    If sh.Range("G1").Value = "" Then

        sh.Range("G1").Value = i   

    Else

        lc = sh.Range("XFD1").End(xlToLeft).Column

        sh.Cells(1, lc + 1).Value = i

    End If

Next i

lc = sh.Range("XFD1").End(xlToLeft).Column

If sh.Range("C1").Value = "Daily" Then

   sh.Range("G3").Value = "=G1"

   sh.Range("G3", sh.Cells(3, lc)).FillRight
  

   sh.Range("E3").Copy

   sh.Range("G3", sh.Cells(3, lc)).PasteSpecial xlPasteFormats

   sh.Range("G3", sh.Cells(3, lc)).NumberFormat = "D-MMM"

   sh.Range("G3", sh.Cells(3, lc)).Orientation = 90

   sh.Range("G3", sh.Cells(3, lc)).EntireColumn.ColumnWidth = 2.5

  

Else

      For i = 7 To lc Step 7

        sh.Cells(3, i).Value = "Week-" & i / 7

        sh.Range("E3").Copy

        sh.Range(sh.Cells(3, i), sh.Cells(3, i + 6)).PasteSpecial xlPasteFormats

        sh.Range(sh.Cells(3, i), sh.Cells(3, i + 6)).EntireColumn.ColumnWidth = 0.8

        sh.Range(sh.Cells(3, i), sh.Cells(3, i + 6)).Merge

        sh.Range(sh.Cells(3, i), sh.Cells(3, i + 6)).HorizontalAlignment = xlCenter

        sh.Range(sh.Cells(3, i), sh.Cells(3, i + 6)).VerticalAlignment = xlCenter

   Next i

      lc = sh.Range("XFD3").End(xlToLeft).Column + 6

End If

lr = sh.Range("B" & Application.Rows.Count).End(xlUp).Row

sh.Range("G1:XFD1").NumberFormat = "D-MMM-YY"

sh.Range("G1:XFD1").Font.Color = VBA.vbWhite

sh.Range("H4:XFD" & Application.Rows.Count).Clear

sh.Range("G5:G" & Application.Rows.Count).Clear

sh.Range("A" & lr + 1, "A" & Application.Rows.Count).EntireRow.Clear

sh.Range("B:F").Locked = False

sh.Range("G1:XFD3").Locked = True

sh.Range("G1:XFD3").FormulaHidden = True

sh.Range("G4:G" & sh.Range("B" & Application.Rows.Count).End(xlUp).Row).FillDown

sh.Range("G4", sh.Cells(lr, lc)).FillRight

With sh.Range("B3", sh.Cells(lr, lc))

    .Borders(xlEdgeBottom).LineStyle = xlDouble

    .Borders(xlEdgeBottom).Color = vbBlack




    .Borders(xlEdgeLeft).LineStyle = xlDouble

    .Borders(xlEdgeLeft).Color = vbBlack

   

    .Borders(xlEdgeRight).LineStyle = xlDouble

    .Borders(xlEdgeRight).Color = vbBlack

   

    .Borders(xlEdgeTop).LineStyle = xlDouble

    .Borders(xlEdgeTop).Color = vbBlack

End With

sh.Protect "1234"

End Sub

Click here to download this dynamic Project Plan.

Watch the step by step tutorial for Dynamic Project Planner:

PK
My Name is PK. I am founder of PK-AnExcelExpert.com. I am a Microsoft Certified Professional. I have more than 10 years of experience in Data Visualization, Excel Automation and dashboard creation. Excel is my passion and I like to do always something innovative in Excel and share it with other people.
https://www.pk-anexcelexpert.com

2 thoughts on “Dynamic Project Planner (Gantt Chart) in Excel

Leave a Reply

Your email address will not be published. Required fields are marked *