Cell value as chart scale maximum

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Phil Hageman - 13 Dec 2004 16:27 GMT

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?

Reply to this Message

Jon Peltier - 13 Dec 2004 18:38 GMT

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/
_______

> 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?

Reply to this Message

Phil Hageman - 13 Dec 2004 20:13 GMT

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

> Hi Phil -
>
[quoted text clipped - 16 lines]
> > 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?

Reply to this Message

JE McGimpsey - 13 Dec 2004 21:50 GMT

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...

> Jon,
> I ran the macro recorder as you suggest at your website, and added your
[quoted text clipped - 35 lines]
> > > 105% in
> > > the chart Y scale maximum. How would I do this?

Reply to this Message

Phil Hageman - 14 Dec 2004 12:23 GMT

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

> I think "AciveSheet" is highlighted, right?
>
[quoted text clipped - 55 lines]
> > > > 105% in
> > > > the chart Y scale maximum. How would I do this?

Reply to this Message

JE McGimpsey - 14 Dec 2004 12:51 GMT

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,
>
[quoted text clipped - 10 lines]
>
> What do you recommend?

Reply to this Message

Phil Hageman - 14 Dec 2004 13:43 GMT

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

> Are you putting it in the same place - i.e., the worksheet code module?
>
[quoted text clipped - 25 lines]
> >
> > What do you recommend?

Reply to this Message

JE McGimpsey - 14 Dec 2004 13:53 GMT

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

> 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.

Reply to this Message

Phil Hageman - 14 Dec 2004 14:47 GMT

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

> Is the chart named "Chart 12"? If not, change it to the proper chart
> name.
[quoted text clipped - 4 lines]
> > Run-time error 1004: Method 'ChartObjects' of object '_Worksheet' failed.
> > All three lines of the code are highlighted yellow.

Reply to this Message

Tushar Mehta - 17 Dec 2004 14:09 GMT

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

Signature

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

> 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?

Reply to this Message