asked on

Setting Chart Scales with VBA

I have a line type pivot chart I want to manipulate at runtime with code.
In the Chart Properties dialog, I can select "Value Axis 1" and change the scale from the "Scale" tab.
There are checkboxes for "Custom Max" and "Custom Min".
How can I change these values with VBA?

Mike

Rey Obrero (Capricorn1)

try this

me.[chartname].object.application.chart.axes(2).minimumscale=<some value>

me.[chartname].object.application.chart.axes(2).maximumscale=<some value>

me.[chartname].object.application.chart.axes(2).minorunit=<some value>

me.[chartname].object.application.chart.axes(2).majorunit=<some value>

shacho

ASKER

Not sure how to connect to the object in this way. It's a Pivot Chart. Currently I get at it via Me.ChartSpace.Charts(0), which apparently is not equivalent to me.[chartname].

Rey Obrero (Capricorn1)

try

Me.ChartSpace.Charts(0).object.application.chart.axes(2).minimumscale=<some value>

shacho

ASKER

Already tried it. No go.

shacho

ASKER

Object doesn't support this property or method

shacho

ASKER

Same eror for ? TypeName(Me.ChartSpace.Charts(0).object)

Rey Obrero (Capricorn1)

can you upload a copy of the db.. (.mdb version)

shacho

ASKER

Not as is. I'll put something together for the discussion tomorrow morning (in 9 hours or so).

shacho

ASKER

OK - let's play with this one. Double click on the chart triggers the code.
ChartStuff.mdb

Rey Obrero (Capricorn1)

looks like the pivotchart does not have those properties available in vba..

check this, created a new chart

ChartStuff.mdb

ASKER CERTIFIED SOLUTION

Rey Obrero (Capricorn1)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

shacho

ASKER

OK - this is pretty cool. Problem is the filters change the range on the axis. So setting the Minimum to "2", say might result in 2009-11 for one set of data, and 2010-5 for another, if you change the filters. Is there a way to grab the filtered data and find out where the bottom of the currently showing range is?

shacho

ASKER

Groovy. Thanks for your help.

Mike