Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Changing the scale major unit in a graph
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).Ma jorUnit = _ 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Changing the scale major unit in a graph
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).Ma jorUnit = _ 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Automatically changing the range in a graph | Charts and Charting in Excel | |||
changing the color of a single bar on a graph | Charts and Charting in Excel | |||
Graph on Inverted Scale | Charts and Charting in Excel | |||
Changing Graph Title W/ Pivot | Excel Discussion (Misc queries) | |||
Bar graph values keep changing | Charts and Charting in Excel |