Setting the minimum/maximum display range of a chart

[Babymech]

I thought I posted this question yesterday, but can't find it today so I'll
repost and hope for the best.

Basically I'm using a stacked bar chart as a GANTT schedule, and want to
ensure that the minimum and maximum display ranges are set to something
relevant... What I want to do then is, ideally, to enter =TODAY()-30 (for
example) as the minimum range and =TODAY()+30 as the maximum, but obviously
this doesn't work in a straightforward way... does anybody know of a
workaround for this, so that I can set the minimum range as an equation, or a
reference?

All suggestions are appreciated - thanks!

 

[Shane Devenshire]

Hi,

You need to use code, here is a sample you will need to modify for you
situation:

ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.Axes(xlValue).Select
With ActiveChart.Axes(xlValue)
.MinimumScale = Date - 30
.MaximumScale = Date + 30
End With

 

[Babymech]

Hi Shane,

Thanks - that opened up a lot of possibilities for me, since I haven't
thought of doing anything with VBA to help the chart, but how do you
recommend I use the code? All I've done so far with VBA has been to make
buttons, and I wanted to avoid buttons in this sheet because I know the
person filling it in will never manage to figure out when he should or
shouldn't be pressing buttons, or enabling macros, etc. I guess I'll have to
accept that the sheet will be macro-enabled, and try to teach him to enable
macros when it asks him to, but is there some way I can make the code that
adjusts the chart range run automatically? Say, either when he opens the
sheet or when he enters a new task (ie when for example a new cell in column
D goes from empty to actually containing something)?

Thanks for your help so far!

 

[Babymech]

Never mind - I think I found it. Thanks!

Hi Shane,

Thanks - that opened up a lot of possibilities for me, since I haven't
thought of doing anything with VBA to help the chart, but how do you
recommend I use the code? All I've done so far with VBA has been to make
buttons, and I wanted to avoid buttons in this sheet because I know the
person filling it in will never manage to figure out when he should or
shouldn't be pressing buttons, or enabling macros, etc. I guess I'll have to
accept that the sheet will be macro-enabled, and try to teach him to enable
macros when it asks him to, but is there some way I can make the code that
adjusts the chart range run automatically? Say, either when he opens the
sheet or when he enters a new task (ie when for example a new cell in column
D goes from empty to actually containing something)?

Thanks for your help so far!