Author: Oscar Cronquist Article last updated on February 20, 2012

Todays template is a holiday planner. It is a stacked bar chart and the worksheet contains a few lines of vba code to self adjust min and max date in the chart. The chart adds new names as you type them.

VBA code in sheet1

Private Sub Worksheet_Change(ByVal Target As Range)

ActiveSheet.ChartObjects("Chart 2").Activate
ActiveChart.Axes(xlValue).MinimumScale = _
Application.WorksheetFunction.Min(Range("Start")) - 7

ActiveChart.Axes(xlValue).MaximumScale = _
Application.WorksheetFunction.Max(Range("End")) + 7


End Sub

The workbook contains dynamic named ranges, you can find instructions in this post:
Dynamic Gantt charts in excel 2007

Download excel *.xlsm file

Excel planner.xlsm

Here is another version that allows you to enter two date ranges.

Download excel *.xlsm file

Excel planner v2.xlsm