Change scales from reference cells value for a chart in a separate sheet

  • Thread starter Thread starter ers
  • Start date Start date
E

ers

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("XY").Sheets("XY").Range("$e$41").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("Chart 4").Activate
ActiveChart.Axes(xlValue).Select


With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = ActiveSheet.Range("$c$1").Text
End With

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

With ActiveChart.Axes(xlValue)
.MinimumScale = ActiveSheet.Range("$h$42").Value
.MaximumScale = ActiveSheet.Range("$h$41").Value
.MinorUnit = ActiveSheet.Range("$h$43").Value
.MajorUnit = ActiveSheet.Range("$h$44").Value
.Crosses = xlCustom
.CrossesAt = ActiveSheet.Range("$h$42").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("erslOg_XxYy.XLS").Activate
Sheets("Chart2").Select

ActiveChart.ChartArea.Select
ActiveChart.Axes(xlValue).Select


With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text =
ThisWorkbook.Sheets("XY").Range("$c$1").Text
End With

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

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

End Sub
 
Did you get a specific error, or does it just "not work"?

Did the error occur on this line?

.MinimumScale =
ThisWorkbook.Sheets("XY").Sheets("XY").Range("$e$41").Value

You have a duplicate reference to Sheets("XY") in the statement.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services - Tutorials and Custom Solutions -
http://PeltierTech.com/
2006 Excel User Conference, 19-21 April, Atlantic City, NJ
http://peltiertech.com/Excel/ExcelUserConf06.html
_______
 
Jon,
the duplicate Sheets("XY") 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("erslOg_XxYy.XLS").Sheets("XY").Range("$e$41").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,
Emil
 
How about Workbooks("blah") instead of Windows("blah")? 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
http://PeltierTech.com/
_______
 
Thank you Jon,

It works, as is soupossed to.
With .MinimumScale = ThisWorkbook.Sheets("XY").Range("$e$41").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
 
Back
Top