Changing the scale major unit in a graph

I have a graph which is linked to a range which changes (through data
validation and SUMIF's). There are several Countries which have varying data
totals which the graph picks up. ie for Country 1 the scale maximum is 100
and for Country 2 the scale maximum is 20. What does not automatically change
however is the scale major unit. If this is manually changed to 20 for
Country 1 it remains that way for Country 2 which is of no use as with a
scale maximum of 20, I need the scale major unit to be say, 2. I am looking
for a way to change the scale major unit automatically. I would like to use
similar code below attached to a data validation cell;

ActiveChart.Axes(xlValue).MajorUnit = 20

Is it somehow possible to link the number 20 above to a cell? That way I can
have a simple formula which can be linked to the range total eg 100 and
divide it by 5. This would give me 20 for Country 1 and 2 for Country 2.

Hi,

You can not directly link the scales properties to a cell.
What you could do with VBA is to add code to a worksheet event that
updates the scale property.

For example this assumes the scale value is in E1. E1 contains a formula
that outputs 20 or 100 dependent on some test related to country selection.

Private Sub Worksheet_Calculate()

ActiveSheet.ChartObjects(1).Chart.Axes(xlValue).MajorUnit = _
Range("E1").Value

End Sub

Cheers
Andy

Ant wrote:
> I have a graph which is linked to a range which changes (through data
> validation and SUMIF's). There are several Countries which have varying data
> totals which the graph picks up. ie for Country 1 the scale maximum is 100
> and for Country 2 the scale maximum is 20. What does not automatically change
> however is the scale major unit. If this is manually changed to 20 for
> Country 1 it remains that way for Country 2 which is of no use as with a
> scale maximum of 20, I need the scale major unit to be say, 2. I am looking
> for a way to change the scale major unit automatically. I would like to use
> similar code below attached to a data validation cell;
>
> ActiveChart.Axes(xlValue).MajorUnit = 20
>
> Is it somehow possible to link the number 20 above to a cell? That way I can
> have a simple formula which can be linked to the range total eg 100 and
> divide it by 5. This would give me 20 for Country 1 and 2 for Country 2.

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info

That works perfectly - thanks Andy.

"Andy Pope" wrote:

> Hi,
>
> You can not directly link the scales properties to a cell.
> What you could do with VBA is to add code to a worksheet event that
> updates the scale property.
>
> For example this assumes the scale value is in E1. E1 contains a formula
> that outputs 20 or 100 dependent on some test related to country selection.
>
> Private Sub Worksheet_Calculate()
>
> ActiveSheet.ChartObjects(1).Chart.Axes(xlValue).MajorUnit = _
> Range("E1").Value
>
> End Sub
>
> Cheers
> Andy
>
> Ant wrote:
> > I have a graph which is linked to a range which changes (through data
> > validation and SUMIF's). There are several Countries which have varying data
> > totals which the graph picks up. ie for Country 1 the scale maximum is 100
> > and for Country 2 the scale maximum is 20. What does not automatically change
> > however is the scale major unit. If this is manually changed to 20 for
> > Country 1 it remains that way for Country 2 which is of no use as with a
> > scale maximum of 20, I need the scale major unit to be say, 2. I am looking
> > for a way to change the scale major unit automatically. I would like to use
> > similar code below attached to a data validation cell;
> >
> > ActiveChart.Axes(xlValue).MajorUnit = 20
> >
> > Is it somehow possible to link the number 20 above to a cell? That way I can
> > have a simple formula which can be linked to the range total eg 100 and
> > divide it by 5. This would give me 20 for Country 1 and 2 for Country 2.
>
> --
>
> Andy Pope, Microsoft MVP - Excel
> http://www.andypope.info
>