software

Can anyone help me with this macro (my programming experience is very
limited).
I have a chart Chart4 on a sheet XY in the workbook erslOg_XxYx.
The same chart is in a separate sheet Chart2 on the same workbook (I
did that so I can print it easier).
The scale and the title is changed from reference cells from XY.
For the chart on the XY sheet is working fine, but is not for the chart
on separate sheet.
Last night I had the impression is working but now it is striking at:
..HasTitle = True
if I comment that it will execute (will change the title accordingly)
but it will strike at any row which makes reference to the cells in XY
sheet like:
..MinimumScale =
ThisWorkbook.Sheets(quot;XYquot;).Sheets(quot;XYquot;).Range(quot;$e$4 1quot;).Value

What is wrong with addressing those cells?
Thank you,
emil-----------------------------------------------this is
working---------------------------------------------------------
Sub scales2()
' change scales on chart on the current sheet Macro

ActiveSheet.ChartObjects(quot;Chart 4quot;).Activate
ActiveChart.Axes(xlValue).SelectWith ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = ActiveSheet.Range(quot;$c$1quot;).Text
End With

With ActiveChart.Axes(xlCategory)
.MinimumScale = ActiveSheet.Range(quot;$e$41quot;).Value
.MaximumScale = ActiveSheet.Range(quot;$e$42quot;).Value
.MinorUnit = ActiveSheet.Range(quot;$e$43quot;).Value
.MajorUnit = ActiveSheet.Range(quot;$e$44quot;).Value
.Crosses = xlCustom
.CrossesAt = ActiveSheet.Range(quot;$e$41quot;).Value
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With

With ActiveChart.Axes(xlValue)
.MinimumScale = ActiveSheet.Range(quot;$h$42quot;).Value
.MaximumScale = ActiveSheet.Range(quot;$h$41quot;).Value
.MinorUnit = ActiveSheet.Range(quot;$h$43quot;).Value
.MajorUnit = ActiveSheet.Range(quot;$h$44quot;).Value
.Crosses = xlCustom
.CrossesAt = ActiveSheet.Range(quot;$h$42quot;).Value
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With
------------------------------------------------------this is not
working-------------------------------------------------

' change scales to the same but for chart on separate sheet Macro

ActiveWindow.Visible = False

Windows(quot;erslOg_XxYy.XLSquot;).Activate
Sheets(quot;Chart2quot;).Select

ActiveChart.ChartArea.Select
ActiveChart.Axes(xlValue).SelectWith ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text =
ThisWorkbook.Sheets(quot;XYquot;).Range(quot;$c$1quot;).Text
End With

With ActiveChart.Axes(xlCategory)
.MinimumScale =
ThisWorkbook.Sheets(quot;XYquot;).Sheets(quot;XYquot;).Range(quot;$e$4 1quot;).Value
.MaximumScale = ThisWorkbook.Sheets(quot;XYquot;).Range(quot;$e$42quot;).Value
.MinorUnit = ThisWorkbook.Sheets(quot;XYquot;).Range(quot;$e$43quot;).Value
.MajorUnit = ThisWorkbook.Sheets(quot;XYquot;).Range(quot;$e$44quot;).Value
.Crosses = xlCustom
.CrossesAt = ThisWorkbook.Sheets(quot;XYquot;).Range(quot;$e$41quot;).Value
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With

With ActiveChart.Axes(xlValue)
.MinimumScale = ThisWorkbook.Sheets(quot;XYquot;).Range(quot;$h$42quot;).Value
.MaximumScale = ThisWorkbook.Sheets(quot;XYquot;).Range(quot;$h$41quot;).Value
.MinorUnit = ThisWorkbook.Sheets(quot;XYquot;).Range(quot;$h$43quot;).Value
.MajorUnit = ThisWorkbook.Sheets(quot;XYquot;).Range(quot;$h$44quot;).Value
.Crosses = xlCustom
.CrossesAt = ThisWorkbook.Sheets(quot;XYquot;).Range(quot;$h$42quot;).Value
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With

End SubDid you get a specific error, or does it just quot;not workquot;?

Did the error occur on this line?

.MinimumScale =
ThisWorkbook.Sheets(quot;XYquot;).Sheets(quot;XYquot;).Range(quot;$e$4 1quot;).Value

You have a duplicate reference to Sheets(quot;XYquot;) in the statement.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services - Tutorials and Custom Solutions -
PeltierTech.com/
2006 Excel User Conference, 19-21 April, Atlantic City, NJ
peltiertech.com/Excel/ExcelUserConf06.html
_______

quot;ersquot; gt; wrote in message oups.com...
gt; Can anyone help me with this macro (my programming experience is very
gt; limited).
gt; I have a chart Chart4 on a sheet XY in the workbook erslOg_XxYx.
gt; The same chart is in a separate sheet Chart2 on the same workbook (I
gt; did that so I can print it easier).
gt; The scale and the title is changed from reference cells from XY.
gt; For the chart on the XY sheet is working fine, but is not for the chart
gt; on separate sheet.
gt; Last night I had the impression is working but now it is striking at:
gt; .HasTitle = True
gt; if I comment that it will execute (will change the title accordingly)
gt; but it will strike at any row which makes reference to the cells in XY
gt; sheet like:
gt; .MinimumScale =
gt; ThisWorkbook.Sheets(quot;XYquot;).Sheets(quot;XYquot;).Range(quot;$e$4 1quot;).Value
gt;
gt; What is wrong with addressing those cells?
gt; Thank you,
gt; emil
gt;
gt;
gt; -----------------------------------------------this is
gt; working---------------------------------------------------------
gt; Sub scales2()
gt; ' change scales on chart on the current sheet Macro
gt;
gt; ActiveSheet.ChartObjects(quot;Chart 4quot;).Activate
gt; ActiveChart.Axes(xlValue).Select
gt;
gt;
gt; With ActiveChart
gt; .HasTitle = True
gt; .ChartTitle.Characters.Text = ActiveSheet.Range(quot;$c$1quot;).Text
gt; End With
gt;
gt; With ActiveChart.Axes(xlCategory)
gt; .MinimumScale = ActiveSheet.Range(quot;$e$41quot;).Value
gt; .MaximumScale = ActiveSheet.Range(quot;$e$42quot;).Value
gt; .MinorUnit = ActiveSheet.Range(quot;$e$43quot;).Value
gt; .MajorUnit = ActiveSheet.Range(quot;$e$44quot;).Value
gt; .Crosses = xlCustom
gt; .CrossesAt = ActiveSheet.Range(quot;$e$41quot;).Value
gt; .ReversePlotOrder = False
gt; .ScaleType = xlLinear
gt; .DisplayUnit = xlNone
gt; End With
gt;
gt; With ActiveChart.Axes(xlValue)
gt; .MinimumScale = ActiveSheet.Range(quot;$h$42quot;).Value
gt; .MaximumScale = ActiveSheet.Range(quot;$h$41quot;).Value
gt; .MinorUnit = ActiveSheet.Range(quot;$h$43quot;).Value
gt; .MajorUnit = ActiveSheet.Range(quot;$h$44quot;).Value
gt; .Crosses = xlCustom
gt; .CrossesAt = ActiveSheet.Range(quot;$h$42quot;).Value
gt; .ReversePlotOrder = False
gt; .ScaleType = xlLinear
gt; .DisplayUnit = xlNone
gt; End With
gt; ------------------------------------------------------this is not
gt; working-------------------------------------------------
gt;
gt; ' change scales to the same but for chart on separate sheet Macro
gt;
gt; ActiveWindow.Visible = False
gt;
gt; Windows(quot;erslOg_XxYy.XLSquot;).Activate
gt; Sheets(quot;Chart2quot;).Select
gt;
gt; ActiveChart.ChartArea.Select
gt; ActiveChart.Axes(xlValue).Select
gt;
gt;
gt; With ActiveChart
gt; .HasTitle = True
gt; .ChartTitle.Characters.Text =
gt; ThisWorkbook.Sheets(quot;XYquot;).Range(quot;$c$1quot;).Text
gt; End With
gt;
gt; With ActiveChart.Axes(xlCategory)
gt; .MinimumScale =
gt; ThisWorkbook.Sheets(quot;XYquot;).Sheets(quot;XYquot;).Range(quot;$e$4 1quot;).Value
gt; .MaximumScale = ThisWorkbook.Sheets(quot;XYquot;).Range(quot;$e$42quot;).Value
gt; .MinorUnit = ThisWorkbook.Sheets(quot;XYquot;).Range(quot;$e$43quot;).Value
gt; .MajorUnit = ThisWorkbook.Sheets(quot;XYquot;).Range(quot;$e$44quot;).Value
gt; .Crosses = xlCustom
gt; .CrossesAt = ThisWorkbook.Sheets(quot;XYquot;).Range(quot;$e$41quot;).Value
gt; .ReversePlotOrder = False
gt; .ScaleType = xlLinear
gt; .DisplayUnit = xlNone
gt; End With
gt;
gt; With ActiveChart.Axes(xlValue)
gt; .MinimumScale = ThisWorkbook.Sheets(quot;XYquot;).Range(quot;$h$42quot;).Value
gt; .MaximumScale = ThisWorkbook.Sheets(quot;XYquot;).Range(quot;$h$41quot;).Value
gt; .MinorUnit = ThisWorkbook.Sheets(quot;XYquot;).Range(quot;$h$43quot;).Value
gt; .MajorUnit = ThisWorkbook.Sheets(quot;XYquot;).Range(quot;$h$44quot;).Value
gt; .Crosses = xlCustom
gt; .CrossesAt = ThisWorkbook.Sheets(quot;XYquot;).Range(quot;$h$42quot;).Value
gt; .ReversePlotOrder = False
gt; .ScaleType = xlLinear
gt; .DisplayUnit = xlNone
gt; End With
gt;
gt; End Sub
gt;
Jon,
the duplicate Sheets(quot;XYquot;) was indeed very silly! I took it out and the
debugger still strike that line.
The error I get is: Run-time error 438.
I change that line to ///
Windows(quot;erslOg_XxYy.XLSquot;).Sheets(quot;XYquot;).Range(quot;$e$ 41quot;).Value ////and
the probem persists...
It will be nice if I get it fixed since then I can create a kind of
zoom in the chart (using a second range of vaues for axes).

The working code I took it from your website and I like to thank you
very much for that,
EmilHow about Workbooks(quot;blahquot;) instead of Windows(quot;blahquot;)? A Window doesn't
have a sheet or a range.

The number of the Run Time Error is not nearly as instructive as the error
message description.

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

quot;ersquot; gt; wrote in message oups.com...
gt; Jon,
gt; the duplicate Sheets(quot;XYquot;) was indeed very silly! I took it out and the
gt; debugger still strike that line.
gt; The error I get is: Run-time error 438.
gt; I change that line to ///
gt; Windows(quot;erslOg_XxYy.XLSquot;).Sheets(quot;XYquot;).Range(quot;$e$ 41quot;).Value ////and
gt; the probem persists...
gt; It will be nice if I get it fixed since then I can create a kind of
gt; zoom in the chart (using a second range of vaues for axes).
gt;
gt; The working code I took it from your website and I like to thank you
gt; very much for that,
gt; Emil
gt;
Thank you Jon,

It works, as is soupossed to.
With .MinimumScale = ThisWorkbook.Sheets(quot;XYquot;).Range(quot;$e$41quot;).Value is
ok.

I found where was the mistake: I had protected before the worksheets
(context object scenarios).
I unprotect it being on XY spreadsheet where was the first chart ( but
never thought I should go in Chart-sheet (second chart) and unprotect
that one too separately!!!! Silly me.

Next step: I had to find a way to protect the chart (series) but leave
the scale free for change. Also to make the print to not stretch the
chart and destroys square-grid (thank you for that too).

I wish you a great weekend,
Emil