Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.charting
ers
 
Posts: n/a
Default Change scales from reference cells value for a chart in a separate sheet

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$4 1").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$4 1").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

  #2   Report Post  
Posted to microsoft.public.excel.charting
Jon Peltier
 
Posts: n/a
Default Change scales from reference cells value for a chart in a separate sheet

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$4 1").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
_______

"ers" wrote in message
oups.com...
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$4 1").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$4 1").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



  #3   Report Post  
Posted to microsoft.public.excel.charting
ers
 
Posts: n/a
Default Change scales from reference cells value for a chart in a separate sheet

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

  #4   Report Post  
Posted to microsoft.public.excel.charting
Jon Peltier
 
Posts: n/a
Default Change scales from reference cells value for a chart in a separate sheet

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/
_______

"ers" wrote in message
oups.com...
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



  #5   Report Post  
Posted to microsoft.public.excel.charting
ers
 
Posts: n/a
Default Change scales from reference cells value for a chart in a separate sheet

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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Auto "copy and paste" individual cells from various sheets into one sheet ?? [email protected] Excel Discussion (Misc queries) 2 March 1st 06 04:19 AM
Relative Sheet Reference (Summary Sheet) [email protected] Excel Discussion (Misc queries) 2 October 1st 05 10:42 AM
Dynamic reference to a sheet xisque Excel Worksheet Functions 4 June 20th 05 09:04 PM
how do i reference cells to create a chart? Nick M Charts and Charting in Excel 1 December 15th 04 04:50 AM
3 cells are named - how to refere to them in one reference field in a chart Marie J-son Charts and Charting in Excel 2 December 2nd 04 05:52 PM


All times are GMT +1. The time now is 01:37 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"