Controlling Minimum and Maximum Axes

**opsinmena09** · 02-14-2010, 05:52 AM

I was told in a previous question that it was not hard to ensure that Excel would automatically set the minimum and maximum of a chart axis range to given percentages less and more than the observed minimum and maximum values in the range the chart is looking to plot.

But it was mentioned that I would need to use code or macros. I am not in anyway programer and rarely understand Excel macros and how they work

Despite this can someone help with me what that code might look like?

And how I might run the code on individual charts?

**6StringJazzer** · 02-14-2010, 03:18 PM

Are you asking the question generically, or do you want to apply this to a specific chart in a specific worksheet? If the latter, then if you attach the workbook it will be a lot quicker to get you a solution rather than starting from scratch. That means that you are more likely to get a response.

**teylyn** · 02-14-2010, 05:16 PM

Hi opsinmena,

The following code will set the primary axis of Chart 1 to the values defined in the named ranges Ymax, Ymin, Ymajor and Yminor. If you assign that code to a button, you can specify or calculate your axis settings in the respective named ranges, then click the button to apply these numbers to both axes in one click.

Please Login or Register  to view this content.

If you have several charts on the sheet, you may need to adjust the macro to pick the correct chart. To find out the chart name, select a cell in your worksheet, then hold down the Ctrl key and select the chart. You will now see the chart name in the name box above cell A1.

The code goes into a regular code module.

**opsinmena09** · 02-15-2010, 05:44 AM

Thanks very much for your input. I think I understand the code and I will try to act on it.

The question was generic since I would like all charts in the sheets I am constructing to adjust their Y axis ranges to be either at the maximum and minium values found in thier y-value ranges (Or better 20% above and below the observed max and min values).

(does anyone know the basis on which the Auto feature on axes settings

I have about 45 charts in the sheet each of extracts data from ranges spread across more than one table of data.

If I run into problems I will develop a model sheet to illustrate what I am trying to do, and attach it to this thread at a later date.

Thanks again.