Results 1 to 9 of 9
  1. #1
    2 Star Lounger
    Join Date
    Oct 2008
    Posts
    141
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Min/Max Chart Scales (2002/SP3)

    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?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Min/Max Chart Scales (2002/SP3)

    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.

  3. #3
    2 Star Lounger
    Join Date
    Oct 2008
    Posts
    141
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Min/Max Chart Scales (2002/SP3)

    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.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Min/Max Chart Scales (2002/SP3)

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

  5. #5
    2 Star Lounger
    Join Date
    Oct 2008
    Posts
    141
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Min/Max Chart Scales (2002/SP3)

    From the Forms toolbar .

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Min/Max Chart Scales (2002/SP3)

    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).

  7. #7
    2 Star Lounger
    Join Date
    Oct 2008
    Posts
    141
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Min/Max Chart Scales (2002/SP3)

    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.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Min/Max Chart Scales (2002/SP3)

    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

  9. #9
    2 Star Lounger
    Join Date
    Oct 2008
    Posts
    141
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Min/Max Chart Scales (2002/SP3)

    Thanks. I'll give it a try.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •