|
Excel inconsistency
After a bunch of fiddling, i got a macro to duplicate a chart below a
single one on a worksheet. But there was a runtime error as noted near the end of the macro. So i did a lot of more fiddling and was able to get a number of copies, one after the other by calling them all "Chart 1". Nasty. Did other fiddling in attempts to change series ranges and title on the fly. Things got worse, so i tried to go back to the original macro as seen below. Even deleting the XLSTART folder and re-trying the macro did no good. **HELP** Sub Macro2() ' Macro2 Macro ' Macro recorded 4/15/2009 by Robert Baer Range("A1").Select SheetColumn = 1 ActiveSheet.ChartObjects("Chart 1").Activate '*NOW FAILS-------------------^---NOW arg needs to be 1 not "Chart 1" For ChartNum = 1 To 20 ActiveChart.ChartArea.Select ActiveChart.ChartArea.Copy ActiveChart.SeriesCollection(1).Select ActiveChart.SeriesCollection(2).Select ActiveChart.Axes(xlCategory).MajorGridlines.Select ActiveChart.Axes(xlValue, xlSecondary).AxisTitle.Select '*NOW FAILS------------------------------------^ ActiveChart.Axes(xlValue, xlSecondary).Select ActiveChart.PlotArea.Select ActiveChart.ChartArea.Select '*NOTE redundant and useless duplication below* ActiveChart.SeriesCollection(1).Select ActiveChart.SeriesCollection(2).Select ActiveChart.Axes(xlCategory).MajorGridlines.Select ActiveChart.Axes(xlValue, xlSecondary).AxisTitle.Select ActiveChart.Axes(xlValue, xlSecondary).Select ActiveChart.PlotArea.Select ActiveChart.ChartArea.Select ActiveChart.SeriesCollection(1).Select ActiveChart.SeriesCollection(2).Select ActiveChart.Axes(xlCategory).MajorGridlines.Select ActiveChart.Axes(xlValue, xlSecondary).AxisTitle.Select ActiveChart.Axes(xlValue, xlSecondary).Select ActiveChart.PlotArea.Select ActiveChart.ChartArea.Select ActiveChart.SeriesCollection(1).Select ActiveChart.SeriesCollection(2).Select ActiveChart.Axes(xlCategory).MajorGridlines.Select ActiveChart.Axes(xlValue, xlSecondary).AxisTitle.Select ActiveChart.Axes(xlValue, xlSecondary).Select ActiveChart.PlotArea.Select ActiveChart.ChartArea.Select ActiveChart.SeriesCollection(1).Select ActiveChart.SeriesCollection(2).Select ActiveChart.Axes(xlCategory).MajorGridlines.Select ActiveChart.Axes(xlValue, xlSecondary).AxisTitle.Select ActiveChart.Axes(xlValue, xlSecondary).Select ActiveChart.PlotArea.Select ActiveChart.ChartArea.Select ActiveChart.SeriesCollection(1).Select ActiveChart.SeriesCollection(2).Select ActiveChart.Axes(xlCategory).MajorGridlines.Select ActiveChart.Axes(xlValue, xlSecondary).AxisTitle.Select ActiveChart.Axes(xlValue, xlSecondary).Select ActiveChart.PlotArea.Select ActiveChart.ChartArea.Select ActiveChart.SeriesCollection(1).Select ActiveChart.SeriesCollection(2).Points(67).Select ActiveChart.Axes(xlCategory).MajorGridlines.Select ActiveChart.Axes(xlValue, xlSecondary).AxisTitle.Select ActiveChart.Axes(xlValue, xlSecondary).Select ActiveChart.PlotArea.Select ActiveChart.ChartArea.Select ActiveWindow.Visible = False Windows("StripperWells.xls").Activate SheetColumn = SheetColumn + 21 TextSheetColumn = "A" + LTrim(Str$(SheetColumn)) ChartNum = ChartNum + 1 TextChartNum = "Chart" + Str$(ChartNum) Range(TextSheetColumn).Select ActiveSheet.Paste ActiveSheet.ChartObjects(TextChartNum).Activate ' ** Runtime error 1004 - unable to get the ChartObjects property ' of the Worksheet class ' Note: second chart has been created where i wanted it, and ' it is selected. '*That is to say, when it worked..i did a lot of mods and ' (as above) it is all messed up Next ChartNum End Sub |
Excel inconsistency
Can you paste this in immediate window and check whether the name is "Chart
1" itself... ?ActiveSheet.ChartObjects(1).name -- If this post helps click Yes --------------- Jacob Skaria "Robert Baer" wrote: After a bunch of fiddling, i got a macro to duplicate a chart below a single one on a worksheet. But there was a runtime error as noted near the end of the macro. So i did a lot of more fiddling and was able to get a number of copies, one after the other by calling them all "Chart 1". Nasty. Did other fiddling in attempts to change series ranges and title on the fly. Things got worse, so i tried to go back to the original macro as seen below. Even deleting the XLSTART folder and re-trying the macro did no good. **HELP** Sub Macro2() ' Macro2 Macro ' Macro recorded 4/15/2009 by Robert Baer Range("A1").Select SheetColumn = 1 ActiveSheet.ChartObjects("Chart 1").Activate '*NOW FAILS-------------------^---NOW arg needs to be 1 not "Chart 1" For ChartNum = 1 To 20 ActiveChart.ChartArea.Select ActiveChart.ChartArea.Copy ActiveChart.SeriesCollection(1).Select ActiveChart.SeriesCollection(2).Select ActiveChart.Axes(xlCategory).MajorGridlines.Select ActiveChart.Axes(xlValue, xlSecondary).AxisTitle.Select '*NOW FAILS------------------------------------^ ActiveChart.Axes(xlValue, xlSecondary).Select ActiveChart.PlotArea.Select ActiveChart.ChartArea.Select '*NOTE redundant and useless duplication below* ActiveChart.SeriesCollection(1).Select ActiveChart.SeriesCollection(2).Select ActiveChart.Axes(xlCategory).MajorGridlines.Select ActiveChart.Axes(xlValue, xlSecondary).AxisTitle.Select ActiveChart.Axes(xlValue, xlSecondary).Select ActiveChart.PlotArea.Select ActiveChart.ChartArea.Select ActiveChart.SeriesCollection(1).Select ActiveChart.SeriesCollection(2).Select ActiveChart.Axes(xlCategory).MajorGridlines.Select ActiveChart.Axes(xlValue, xlSecondary).AxisTitle.Select ActiveChart.Axes(xlValue, xlSecondary).Select ActiveChart.PlotArea.Select ActiveChart.ChartArea.Select ActiveChart.SeriesCollection(1).Select ActiveChart.SeriesCollection(2).Select ActiveChart.Axes(xlCategory).MajorGridlines.Select ActiveChart.Axes(xlValue, xlSecondary).AxisTitle.Select ActiveChart.Axes(xlValue, xlSecondary).Select ActiveChart.PlotArea.Select ActiveChart.ChartArea.Select ActiveChart.SeriesCollection(1).Select ActiveChart.SeriesCollection(2).Select ActiveChart.Axes(xlCategory).MajorGridlines.Select ActiveChart.Axes(xlValue, xlSecondary).AxisTitle.Select ActiveChart.Axes(xlValue, xlSecondary).Select ActiveChart.PlotArea.Select ActiveChart.ChartArea.Select ActiveChart.SeriesCollection(1).Select ActiveChart.SeriesCollection(2).Select ActiveChart.Axes(xlCategory).MajorGridlines.Select ActiveChart.Axes(xlValue, xlSecondary).AxisTitle.Select ActiveChart.Axes(xlValue, xlSecondary).Select ActiveChart.PlotArea.Select ActiveChart.ChartArea.Select ActiveChart.SeriesCollection(1).Select ActiveChart.SeriesCollection(2).Points(67).Select ActiveChart.Axes(xlCategory).MajorGridlines.Select ActiveChart.Axes(xlValue, xlSecondary).AxisTitle.Select ActiveChart.Axes(xlValue, xlSecondary).Select ActiveChart.PlotArea.Select ActiveChart.ChartArea.Select ActiveWindow.Visible = False Windows("StripperWells.xls").Activate SheetColumn = SheetColumn + 21 TextSheetColumn = "A" + LTrim(Str$(SheetColumn)) ChartNum = ChartNum + 1 TextChartNum = "Chart" + Str$(ChartNum) Range(TextSheetColumn).Select ActiveSheet.Paste ActiveSheet.ChartObjects(TextChartNum).Activate ' ** Runtime error 1004 - unable to get the ChartObjects property ' of the Worksheet class ' Note: second chart has been created where i wanted it, and ' it is selected. '*That is to say, when it worked..i did a lot of mods and ' (as above) it is all messed up Next ChartNum End Sub |
Excel inconsistency
Jacob Skaria wrote:
Can you paste this in immediate window and check whether the name is "Chart 1" itself... ?ActiveSheet.ChartObjects(1).name What is an "immediate window", how can i find it? How does one go about finding the name of a chart, changing it and "resetting" that count (assuming ther is one)? |
Excel inconsistency
The immediate window is in the VBE.
Hit alt-f11 to enter the VBE (where macros live) Hit ctrl-g to see the immediate window You can also see the name of the chart by ctrl-clicking on its edge (in excel) and looking at the namebox (to the left of the formulabar). And after it's selected, you can also change the name by typing the new name in that namebox and hitting enter. Robert Baer wrote: Jacob Skaria wrote: Can you paste this in immediate window and check whether the name is "Chart 1" itself... ?ActiveSheet.ChartObjects(1).name What is an "immediate window", how can i find it? How does one go about finding the name of a chart, changing it and "resetting" that count (assuming ther is one)? -- Dave Peterson |
Excel inconsistency
Dave Peterson wrote:
The immediate window is in the VBE. Hit alt-f11 to enter the VBE (where macros live) Hit ctrl-g to see the immediate window You can also see the name of the chart by ctrl-clicking on its edge (in excel) and looking at the namebox (to the left of the formulabar). And after it's selected, you can also change the name by typing the new name in that namebox and hitting enter. Robert Baer wrote: Jacob Skaria wrote: Can you paste this in immediate window and check whether the name is "Chart 1" itself... ?ActiveSheet.ChartObjects(1).name What is an "immediate window", how can i find it? How does one go about finding the name of a chart, changing it and "resetting" that count (assuming ther is one)? Thanks! Isn't it wonderful that there are all of these obscure, undocumented tricks from M$? |
| All times are GMT +1. The time now is 12:25 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com