Min/Max Chart Scales (2002/SP3)

Topic

New Reply

I have a scroll bar set up in a worksheet that controls the min/max scales of XY scatter chart. I know how to link the min/max scale values to the scroll bar. What I don’t know is how to get the original scale values from the chart so that I can go back to the defaulted chart scales (the ones which are automatically scaled). Any ideas?

Reply | Quote

Replies

You’d have to set the MinimumScaleIsAuto and MaximumScaleIsAuto properties of the axis to True, so that Excel computes the minimum and maximum automatically again, then retrieve the MinimumScale and MaximumScale properties.
There is no way to get at the automatically calculated minimum and maximum while they are overridden.

Reply | Quote

Thanks Hans.

How do I set the scroll bar’s minimum to be -100? I’d like to set up min/max values in the range of -100 to +100.

Reply | Quote

Is this a scroll bar from the Forms toolbar or from the Control Toolbox?

Reply | Quote

From the Forms toolbar .

Reply | Quote

The value of a Forms scroll bar has to be between 0 and 30,000. Negative values are not allowed.
You can use formulas or code to translate the scroll bar value to the value you want to use. For example, if you set the minimum to 0 and the maximum to 200, and make A1 the linked cell, you can use =A1-100 to obtain a value between -100 and 100.

(The values for a Control Toolbox scroll bar are between 0 and 65,535 – negative values aren’t allowed there either).

Reply | Quote

Thanks. And how do you deal with decimal places? For instance, in my chart the min/max scale for the X-axis values are 0.0 and 0.4, respectively. When I bring over these values to the scroll bar, the min/max values change to 0, most probably since they are converted to integers.

Reply | Quote

Yes, the value of a scroll bar (whether from the Forms toolbar or from the Control Toolbox) is always a whole number.
Again, you can use formulas to convert. Say, for example, that you want to be able to vary the value from -2.5 to +1.5 in steps of 0.1. The range is 40 steps of 0.1.
Set the minimum of the scroll bar to 0, and the maximum to 40.
If A1 is the linked cell, the formula = A1*0.1-2.5 or =(A1-25)/10will translate the scrollbar value in the range 0 … 40 to a value in the range -2.5 … +1.5

Reply | Quote

Thanks. I’ll give it a try.

Reply | Quote