Cell value as chart scale maximum

  • Thread starter Thread starter Guest
  • Start date Start date
G

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,
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
 
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...
 
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
 
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
 
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
 
Okay - it works. The name requires a space between t and 1. Thanks JE,
appreciate your help.
 
Back
Top