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

--
Message posted via http://www.officekb.com

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

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

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


"BNT1 via OfficeKB.com" <u19326@uwe wrote in message
news:8a7d839afd6c5@uwe...
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

--
Message posted via http://www.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

Jon Peltier wrote:
This page shows how to do what you're attempting.

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

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...chart/200809/1

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
"BNT1 via OfficeKB.com" <u19326@uwe wrote in message
news:8a8913faf173d@uwe...
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

Jon Peltier wrote:
This page shows how to do what you're attempting.

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

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...chart/200809/1

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


Andy Pope wrote:
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

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...chart/200809/1

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
"BNT1 via OfficeKB.com" <u19326@uwe wrote in message
news:8a9264e4dde0d@uwe...
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


Andy Pope wrote:
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

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...chart/200809/1