How to create a macro to automatically setup maximumscales for multiple sheets

[kevinkits]

I am having trouble creating a macro to automatically adjust maximumscales for multiple sheets on dynamic basis.

Basically the Axis X is a date. It ends with last data's date which is today. I am trying to make it today()+5, so the end of graph could display better(leaving a space between the edge of graph and the line.

I am doing currently using

Sub adjustscales()

'Worksheet Name".Chartobjects("Chart 2").Chart.Axes(xIvalue).MaximumScale = today() + 5

End Sub

It gets s compile, which as a starter, I am not sure how to resolve, not to mention it is probably not working.

[AlphaFrog]

Try something like this. Change the sheet name to suit. Date is the VBA equivalent to the TODAY() formula function.

Please Login or Register  to view this content.

[kevinkits]

Thank you AlphaFrog for your reply.

I changed to

Sub adjustscales()

Sheets("Sheet1").ChartObjects("Chart 2").Chart.Axes(xIvalue).MaximumScale = Date + 5

End Sub

Get a method 'Axes' of object'_Chart' failed error.

Actually, can I reference the "Date + 5" to a cell in another sheet Sheet2!M255

[AlphaFrog]

xIvalue should be xlValue

[kevinkits]

Thanks AlphaFrog.

I think the script works, but the chart becomes a flat line. Is that because inconsistency on the Data range(the graph data is linked to two columns in another spreadsheet.)

Where do you suggest a good VBA chart section to look if I want to explore some possibilities

Thanks!

[AlphaFrog]

I think the script works, but the chart becomes a flat line. Is that because inconsistency on the Data range(the graph data is linked to two columns in another spreadsheet.)

Hard to say without seeing what you're doing.

Where do you suggest a good VBA chart section to look if I want to explore some possibilities

Quick Excel Chart VBA Examples



A non-VBA method could be to add a data point to the bottom of your chart's data range
Date =TODAY()+5
Data Point Value =NA()

The chart will include that date as the max date but it wont have any value.

[kevinkits]

Thanks AlphaFrog. The non-VBA method solved the problem!