Changing the Maximum scale for an excel  graph as the values change

Posted on 2003-03-06
Medium Priority
Last Modified: 2013-11-22
How to change the maximum Range of a column graph depending on a maximumvalue in a Range of Values.
The problem is sometimes the values are as low as 1000
and sometimes the values are 50,000. When we set the Maximum scale to 50000 the columns are near to zero if the value is 500 or 1000.and the graph doesnt look good.
Can we change this scale depending on the values in a given Range.
Question by:santhakumari
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

Accepted Solution

Mike-Kieswetter earned 200 total points
ID: 8087007
Have you got the maximum scale set to Auto in the 'Format Axis','Scale' tab?
This should adjust the scale to suit the data.

If not the code to adjust it would be:

    ActiveSheet.ChartObjects("Chart 1").Select
    ActiveChart.Axes(xlValue).MaximumScale = Range("B30")

Where "Chart 1" is the first chart on the sheet and call B30 contains the max value to use.
To get the code to run, you will need to add a button to the sheet and insert the above. (To do this from the menu select 'View' 'Toolbars' 'Visual Basic'
Click the 'contol toolbox' icon, select the button icon and add to the sheet. Double click the button to open up the code window.


Author Comment

ID: 8087057
Thank you for the answer . I tried and  it is working.

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will inform Clients about common and important expectations from the freelancers (Experts) who are looking at your Gig.
Q&A with Course Creator, Mark Lassoff, on the importance of HTML5 in the career of a modern-day developer.
An introduction to basic programming syntax in Java by creating a simple program. Viewers can follow the tutorial as they create their first class in Java. Definitions and explanations about each element are given to help prepare viewers for future …
Introduction to Processes
Suggested Courses

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question