Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
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
Posted to microsoft.public.excel.charting
|
|||
|
|||
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
Posted to microsoft.public.excel.charting
|
|||
|
|||
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
Posted to microsoft.public.excel.charting
|
|||
|
|||
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
Posted to microsoft.public.excel.charting
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Auto "copy and paste" individual cells from various sheets into one sheet ?? | Excel Discussion (Misc queries) | |||
Relative Sheet Reference (Summary Sheet) | Excel Discussion (Misc queries) | |||
Dynamic reference to a sheet | Excel Worksheet Functions | |||
how do i reference cells to create a chart? | Charts and Charting in Excel | |||
3 cells are named - how to refere to them in one reference field in a chart | Charts and Charting in Excel |