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

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

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

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

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

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

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

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

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

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

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

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
"BNT1 via OfficeKB.com" <u19326@uwe wrote in message
news:8a939aa15833d@uwe...
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

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

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

nope, just "Summary"

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

Hi Andy

[quoted text clipped - 42 lines]
Range(mySelection).Select
End Sub

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

By the way, without the "" marks

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

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

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
"BNT1 via OfficeKB.com" <u19326@uwe wrote in message
news:8a941597684b1@uwe...
By the way, without the "" marks

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

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

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

Thanks for your patients

Regards

BNT1 wrote:
By the way, without the "" marks

nope, just "Summary"

[quoted text clipped - 3 lines]
Range(mySelection).Select
End Sub

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