change max scale in chart

[BNT1 via OfficeKB.com]

Hi

I am tring to get a macro to change the maximum scale value in a chart, based
on a cell in the data sheet. In this case G5 in data "sheet1". In cell G5 ,
I have the max formulaI have named the chart "test". I have pasted the code
in a module, and if, with help, it works, will paste the second code in "This
workbook". As you no doubt guessed, I am not too familar with VBA, and have
managed to get hold of this code, however, it is failing.

Help appriciated

Regards


Sub UpdateScale()
ActiveSheet.ChartObjects("test").Activate
With ActiveChart.Axes(xlValue)
.MinimumScale = 0
.MaximumScale = Range("G5").Value
End With
End Sub



Private Sub Worksheet_Change(ByVal Target As Range)
mySelection = ActiveWindow.RangeSelection.Address
UpdateScale
Range(mySelection).Select
End Sub

 

[Jon Peltier]

This page shows how to do what you're attempting.

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

- Jon

 

[BNT1 via OfficeKB.com]

Hi Jon

Thanks for your direction

Followed your link and recorded the macro, then tweeked with the
"Activesheet" etc
All worked ok, however, when moved graph to own sheet, instead of being
inserted in the data sheet, it keeps failing. I have named the chartsheet
"Chart 4".

Can this be amended further to change when the graph is in its own sheet?



Sub testscale()
'
' testscale Macro
' Macro recorded 21/09/2008 by Note
'

'
ActiveSheet.ChartObjects("Chart 4").Activate
ActiveChart.Axes(xlValue).Select
With ActiveChart.Axes(xlValue)
.MinimumScaleIsAuto = True
.MaximumScale = ActiveSheet.Range("o1").Value
.MinorUnitIsAuto = True
.MajorUnit = ActiveSheet.Range("o2").Value
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With
End Sub

Regards

Brian

This page shows how to do what you're attempting.

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

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

Hi

[quoted text clipped - 27 lines]

Range(mySelection).Select
End Sub

 

[Andy Pope]

Hi,

Now the ActiveSheet is the chart rather than the worksheet. You need to
modify the code so the range reference is to the worksheet containing the
values.
Something like this,

.MaximumScale = Worksheet("Sheet1").Range("o1").Value

Cheers
Andy
--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info

Hi Jon

Thanks for your direction

Followed your link and recorded the macro, then tweeked with the
"Activesheet" etc
All worked ok, however, when moved graph to own sheet, instead of being
inserted in the data sheet, it keeps failing. I have named the chartsheet
"Chart 4".

Can this be amended further to change when the graph is in its own sheet?



Sub testscale()
'
' testscale Macro
' Macro recorded 21/09/2008 by Note
'

'
ActiveSheet.ChartObjects("Chart 4").Activate
ActiveChart.Axes(xlValue).Select
With ActiveChart.Axes(xlValue)
.MinimumScaleIsAuto = True
.MaximumScale = ActiveSheet.Range("o1").Value
.MinorUnitIsAuto = True
.MajorUnit = ActiveSheet.Range("o2").Value
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With
End Sub

Regards

Brian

This page shows how to do what you're attempting.

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

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

Hi

[quoted text clipped - 27 lines]

Range(mySelection).Select
End Sub

 

[BNT1 via OfficeKB.com]

Hi Andy

Still getting Sub or function not defined. The word "orksheet" is
highlighted

Any ideas?
regards

Sub testscale()
' testscale Macro
' Macro recorded 21/09/2008 by Note
'

'
ActiveSheet.ChartObjects("Chart1").Activate
ActiveChart.Axes(xlValue).Select
With ActiveChart.Axes(xlValue)
.MinimumScaleIsAuto = True
.MaximumScale = Worksheet("summary.").Range("o1").Value
.MinorUnitIsAuto = True
.MajorUnit = Worksheet("summary.").Range("o2").Value
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With
End Sub

Hi,

Now the ActiveSheet is the chart rather than the worksheet. You need to
modify the code so the range reference is to the worksheet containing the
values.
Something like this,

.MaximumScale = Worksheet("Sheet1").Range("o1").Value

Cheers
Andy

Hi Jon

[quoted text clipped - 49 lines]

 

[Andy Pope]

Perhaps that should be Worksheet then.
Post the code you are now using and the name of the sheets involved.

Cheers
Andy

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info

Hi Andy

Still getting Sub or function not defined. The word "orksheet" is
highlighted

Any ideas?
regards

Sub testscale()
' testscale Macro
' Macro recorded 21/09/2008 by Note
'

'
ActiveSheet.ChartObjects("Chart1").Activate
ActiveChart.Axes(xlValue).Select
With ActiveChart.Axes(xlValue)
.MinimumScaleIsAuto = True
.MaximumScale = Worksheet("summary.").Range("o1").Value
.MinorUnitIsAuto = True
.MajorUnit = Worksheet("summary.").Range("o2").Value
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With
End Sub

Hi,

Now the ActiveSheet is the chart rather than the worksheet. You need to
modify the code so the range reference is to the worksheet containing the
values.
Something like this,

.MaximumScale = Worksheet("Sheet1").Range("o1").Value

Cheers
Andy

Hi Jon

[quoted text clipped - 49 lines]

Range(mySelection).Select
End Sub

 

[BNT1 via OfficeKB.com]

Here is the code I am using

Chart1 is named on the sheet tab "Tacho Infring.
The cells o1 + o2 are in the summary sheet. Tab named "summary"
regards

Sub testscale()
' testscale Macro
' Macro recorded 21/09/2008 by Note
'

'
ActiveSheet.ChartObjects("Chart1").Activate
ActiveChart.Axes(xlValue).Select
With ActiveChart.Axes(xlValue)
.MinimumScaleIsAuto = True
.MaximumScale = Worksheet("summary.").Range("o1").Value
.MinorUnitIsAuto = True
.MajorUnit = Worksheet("summary.").Range("o2").Value
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With
End Sub

Perhaps that should be Worksheet then.
Post the code you are now using and the name of the sheets involved.

Cheers
Andy

Hi Andy

[quoted text clipped - 40 lines]

 

[Andy Pope]

Hi,

My bad I missed the s off of worksheets.

.MaximumScale = Worksheets("summary.").Range("o1").Value

Cheers
Andy
--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info

Here is the code I am using

Chart1 is named on the sheet tab "Tacho Infring.
The cells o1 + o2 are in the summary sheet. Tab named "summary"
regards

Sub testscale()
' testscale Macro
' Macro recorded 21/09/2008 by Note
'

'
ActiveSheet.ChartObjects("Chart1").Activate
ActiveChart.Axes(xlValue).Select
With ActiveChart.Axes(xlValue)
.MinimumScaleIsAuto = True
.MaximumScale = Worksheet("summary.").Range("o1").Value
.MinorUnitIsAuto = True
.MajorUnit = Worksheet("summary.").Range("o2").Value
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With
End Sub

Perhaps that should be Worksheet then.
Post the code you are now using and the name of the sheets involved.

Cheers
Andy

Hi Andy

[quoted text clipped - 40 lines]

Range(mySelection).Select
End Sub

 

[BNT1 via OfficeKB.com]

Hi Andy

When running this macro from tools/macro, I am still getting highlighted in
yellow - :
.MaximumScale = Worksheets("summary.").Range("o1").Value

Any other idea?

Eventually, I would like to place the fixed macro into a worksheet change
event, thereby, removing the need for the opertator to run the macro

Regards
Brian




Sheets("Tacho Infring.").Select
ActiveChart.Axes(xlValue).Select
With ActiveChart.Axes(xlValue)
.MinimumScaleIsAuto = True
.MaximumScale = .MaximumScale = Worksheets("summary.").Range("o1").
Value
.MinorUnitIsAuto = True
.MajorUnit = .MaximumScale = Worksheets("summary.").Range("o2").Value
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With
Sheets("Summary").Select
Range("A22").Select
End Sub

Hi,

My bad I missed the s off of worksheets.

.MaximumScale = Worksheets("summary.").Range("o1").Value

Cheers
Andy

Here is the code I am using

[quoted text clipped - 33 lines]

 

[Andy Pope]

Daft question but is your worksheet actually called "summary." , including
the full stop?

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info

Hi Andy

When running this macro from tools/macro, I am still getting highlighted
in
yellow - :
MaximumScale = Worksheets("summary.").Range("o1").Value

Any other idea?

Eventually, I would like to place the fixed macro into a worksheet change
event, thereby, removing the need for the opertator to run the macro

Regards
Brian




Sheets("Tacho Infring.").Select
ActiveChart.Axes(xlValue).Select
With ActiveChart.Axes(xlValue)
.MinimumScaleIsAuto = True
.MaximumScale = .MaximumScale = Worksheets("summary.").Range("o1").
Value
.MinorUnitIsAuto = True
.MajorUnit = .MaximumScale =
Worksheets("summary.").Range("o2").Value
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With
Sheets("Summary").Select
Range("A22").Select
End Sub

Hi,

My bad I missed the s off of worksheets.

.MaximumScale = Worksheets("summary.").Range("o1").Value

Cheers
Andy

Here is the code I am using

[quoted text clipped - 33 lines]

Range(mySelection).Select
End Sub

 

[BNT1 via OfficeKB.com]

nope, just "Summary"

Daft question but is your worksheet actually called "summary." , including
the full stop?

Hi Andy

[quoted text clipped - 42 lines]

 

[BNT1 via OfficeKB.com]

By the way, without the "" marks

nope, just "Summary"

Daft question but is your worksheet actually called "summary." , including
the full stop?

[quoted text clipped - 4 lines]

 

[Andy Pope]

Then change the references in the command to match the information you have
about your files.

.MaximumScale = Worksheets("summary").Range("o1").Value

Cheers
Andy
--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info

By the way, without the "" marks

nope, just "Summary"

Daft question but is your worksheet actually called "summary." ,
including
the full stop?

[quoted text clipped - 4 lines]

Range(mySelection).Select
End Sub

 

[BNT1 via OfficeKB.com]

Sorry Alan. got the picture, have removed the full stops and hey presto it
worked

Thanks for your patients

Regards

By the way, without the "" marks

nope, just "Summary"

[quoted text clipped - 3 lines]