Cell value as chart scale maximum
 

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?

Hi Phil -

Use the approach I describe he

http://peltiertech.com/Excel/Charts/...nkToSheet.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/
_______

Phil Hageman wrote:

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

"Jon Peltier" wrote:

Hi Phil -

Use the approach I describe he

http://peltiertech.com/Excel/Charts/...nkToSheet.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/
_______

Phil Hageman wrote:

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?

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


In article ,
Phil Hageman wrote:

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

"Jon Peltier" wrote:

Hi Phil -

Use the approach I describe he

http://peltiertech.com/Excel/Charts/...nkToSheet.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/
_______

Phil Hageman wrote:

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?

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" wrote:

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


In article ,
Phil Hageman wrote:

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

"Jon Peltier" wrote:

Hi Phil -

Use the approach I describe he

http://peltiertech.com/Excel/Charts/...nkToSheet.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/
_______

Phil Hageman wrote:

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?

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


In article ,
Phil Hageman wrote:

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?

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" wrote:

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


In article ,
Phil Hageman wrote:

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?

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

In article ,
Phil Hageman wrote:

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.

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

"JE McGimpsey" wrote:

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

In article ,
Phil Hageman wrote:

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.

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

--=20
Regards,

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

In article ,=20
says...
I have a chart on a worksheet and want to make the Y scale maximum value=
=20
equal the value of cell Y3 (this is a merged cell range Y3:Y5), rounded u=
p to=20
the next =E2=A4=BD5=E2=A4=9D increment. For example, 101% in merged cell=
Y5 would be 105% in=20
the chart Y scale maximum. How would I do this?
=20