Cell value as chart scale maximum

[Guest]

I have a chart on a worksheet and want to make the Y scale maximum value
equal the value of cell Y3 (this is a merged cell range Y3:Y5), rounded up to
the next “5†increment. For example, 101% in merged cell Y5 would be 105% in
the chart Y scale maximum. How would I do this?

 

[Jon Peltier]

Hi Phil -

Use the approach I describe here:

http://peltiertech.com/Excel/Charts/AxisScaleLinkToSheet.html

but link to CEIL(Y3,0.05) instead of Y3.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

 

[Guest]

Jon,
I ran the macro recorder as you suggest at your website, and added your
lines too, but get the following: Compile error: Variable not defined. The
Private Sub line is highlighted yellow, and “ActiveSheet†is blue highlighted
at the beginning of the second line. Can you help me straighten this out?
Appreciate your help.
Phil

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
AciveSheet.ChartObjects("Chart 12").Active
With ActiveChart.Axes(xlValue, xlPrimary)
.MaximumScale = ActiveSheet.Range("Y3", 0.05)
End With
End Sub

 

[JE McGimpsey]

I think "AciveSheet" is highlighted, right?

Try:

Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.ChartObjects("Chart12").Activate
ActiveChart.Axes(xlValue, xlPrimary).MaximumScale = _
Application.Ceiling(ActiveSheet.Range("Y3").Value, 0.05)
End Sub


Or just

Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.ChartObjects("Chart12").Chart.Axes( _
xlValue, xlPrimary).MaximumScale = _
Application.Ceiling(ActiveSheet.Range("Y3").Value, 0.05)
End Sub

Which won't activate your chart...

 

[Guest]

JE,

Entered the non-chart-activation code (your second recommendation) and get
no response when the value in cell Y3 changes. I also get no error messages.
In way of further information, the Value in Y3 is created by the formula:
=(V10*W10)+(V11*Y11)+(V12*AA12). These cell values are brought forward by
links to worksheets in other workbooks.

What further information could I not be telling you?

When I was trying to work out Jon's code, I received my error messages any
time a change occured anywhere on the worksheet - which is not the case now.

What do you recommend?

Thanks, Phil

 

[JE McGimpsey]

Are you putting it in the same place - i.e., the worksheet code module?

http://www.mcgimpsey.com/excel/modules.html

If you set a breakpoint in the code, does the code fire?

If Y3 is calculated, you should probably use the _Calculate() event
instead:

Private Sub Worksheet_Calculate()
Me.ChartObjects("Chart12").Chart.Axes( _
xlValue, xlPrimary).MaximumScale = _
Application.Ceiling(Me.Range("Y3").Value, 0.05)
End Sub

 

[Guest]

JE,
One problem resolved - code in the wrong place. I copied your 'Calculate'
macro into Sheet 1 (Dashboard) and received the following error message when
I made a change that gave a new value in Y3:
Run-time error 1004: Method 'ChartObjects' of object '_Worksheet' failed.
All three lines of the code are highlighted yellow.
Phil

 

[JE McGimpsey]

Is the chart named "Chart 12"? If not, change it to the proper chart
name.

 

[Guest]

Okay - it works. The name requires a space between t and 1. Thanks JE,
appreciate your help.

 

[Tushar Mehta]

For a ready-made solution see AutoChart Manager
http://www.tushar-mehta.com/excel/software/autochart/index.html

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Multi-disciplinary business expertise
+ Technology skills
= Optimal solution to your business problem
Recipient Microsoft MVP award 2000-2005