Horizontal axis automatically starting with earliest date in Gannt chart

[mkuzner]

Hello lovely people of Excelguru forum,

I have a question regarding Gannt chart I created in Excel. As Gannt charts do, it shows progress of planned and actual tasks in a period of time.

The problem I have is that I have to manually set the date that horizontal axis starts with. My idea is that it should be depended on the earliest date I write in my data for the graph (first planned or actual start date). At the moment, when I change my data, horizontal axis doesn't change and it would be a fantasy to achieve that.

Hope I explained it well. I am also attaching an example called gannt chart.xlsx

Thank you for your anwsers in advance. Should there be any additional questions, don't hesitate to ask.

Marko

 

[p45cal]

You'll need vba (a macro) to do this:

Sub blah()
With Range("Tabela1").ListObject
  myMin = Application.Min(.ListColumns("start date - plan").DataBodyRange, .ListColumns("start date - actual").DataBodyRange)
  myMax = Application.Max(.ListColumns("end date - plan").DataBodyRange, .ListColumns("end date - actual").DataBodyRange)
End With
With ActiveSheet.Shapes("Grafikon 2").Chart
  .Axes(xlValue).MinimumScale = myMin
  .Axes(xlValue).MaximumScale = myMax
End With
End Sub

Button at cell K14 of the attached.

 

[mkuzner]

Hello p45cal,

thank you for your solution. It's working perfectly.

I do have an additional question, an upgrade you might say. Is it possible to tweak the code so it' doesn't need the button to be clicked for macro to run, rather it triggers by itself when I change the first planned or actual start date?

And if I have more worksheets, I probably need a separate macro for each one of them right? Since the graphs will not be connected, that would be wise, I guess.

Thank you.

Regards, Marko

 

[p45cal]

Is it possible to tweak the code so it' doesn't need the button to be clicked for macro to run, rather it triggers by itself when I change the first planned or actual start date?

And if I have more worksheets, I probably need a separate macro for each one of them right? Since the graphs will not be connected, that would be wise, I guess.

It's a bit more than a tweak. Both the above can be achieved in many ways; the way I've chosen may not suit.
There are assumtions made:
On sheets that you want this to work on:

• The table is at cell A1

It has 6 column headers exactly as follows:
​

start date - plan
start date - actual
no. Working days - plan
NO. Working days - actual
end date - plan
end date - actual
​

• The chart you want this to apply to on each sheet is the first chart on the sheet (if you have more charts on the sheet, chart no.1 is not necessarily the topmost/leftmost chart on the sheet). If this is not the case we'll have to think again.

If the above criteria are not met, the code should just silently do nothing.

There's code in two places: Module 1 and the ThisWorkbook module.