How to name a chart for referencing in VBA? And move it.

[Toby Erkson]

I'm using the macro recorder to create a pivot chart. I want to do some of my own stuff like move it to a certain location, however, the macro recorder
classifies it as a Shape, for example:

...chart creation goes here...
ActiveChart.ChartArea.Select
ActiveSheet.Shapes("Chart30").IncrementLeft -229.5
ActiveSheet.Shapes("Chart30").IncrementTop -154.5
...

I would like to give the chart a name instead of a generic number like the macro recorder assignees (and increments :-( ). Also, how would I take that named
chart and move it to a specific location, like a cell reference ("A1" or "P30")?

I looked to Chip's page http://www.cpearson.com/excel/codemods.htm but it didn't help me for naming a chart.
Toby Erkson
Oregon, USA

 

[Toby Erkson]

I'm trying to modify the macro recording by making it an object, as such (ignore the leading numbers, I'll explain in a second):

Dim chartAvgCalls As Chart
Set chartAvgCalls = Charts.Add
chartAvgCalls.SetSourceData Source:=Sheets("Pivots").Range("B7")
chartAvgCalls.Location Where:=xlLocationAsObject, Name:="Charts"
1 With chartAvgCalls
1 .HasPivotFields = False
1 .PlotArea.Select
1 End With
2 With Selection.Border
2 .ColorIndex = 16
2 .Weight = xlThin
2 .LineStyle = xlContinuous
2 End With
...
Okay, the statements prefixed with number 1 don't work, they give me an Automation error. The statements prefixed with the number 2 do work. Excel's Help
wasn't very helpful about the Automation error.

Suggestions?
TIA,
Toby Erkson
Oregon, USA

 

[Toby Erkson]

What's working so far...
Dim chartAvgCalls As Chart
Set chartAvgCalls = Charts.Add
chartAvgCalls.SetSourceData Source:=Sheets("Pivots").Range("B7")
ActiveChart.HasPivotFields = False
With chartAvgCalls
.HasPivotFields = False
.PlotArea.Select
End With
...
Now, this line takes the chart (which is currently it's own sheet) and places it in an existing sheet named "Charts":
ActiveChart.Location where:=xlLocationAsObject, Name:="Charts"
This, too, works. But after that I can't figure out how to select the chart and move it. Using the macro recorder isn't helpful because this is what it
produces:
ActiveSheet.ChartObjects("Chart 62").Activate
ActiveChart.ChartArea.Select
ActiveSheet.Shapes("Chart 62").IncrementLeft -168#
ActiveSheet.Shapes("Chart 62").IncrementTop -107.25
Notice that it specifically selects "Chart 62". Well, that chart won't exist the next time this is run because it increments i.e. Chart 63. I need a way to
programmatically specify the selected chart (object?) so I can move it.

Additional info: The "Charts" sheet starts out blank but in the end will have three different charts placed in it -- which is why I need to be able to select
the specific chart currently being created so it can be moved.

Toby Erkson
Oregon, USA

 

[Toby Erkson]

Well, once again I am able to stump people...

Google produces far too many -- or too few -- results and search criteria for this particular question is hit or miss. Painfully crawling thru my Excel
bookmarks I came across David McRitchie's page http://www.mvps.org/dmcritchie/excel/xlindex.htm and in it a chart link to Jon Peltier's page
http://peltiertech.com/Excel/ChartsHowTo/ResizeAndMoveAChart.html

Bingo!

I replaced my trouble code with a snip from his page for testing:
ActiveChart.Location where:=xlLocationAsObject, Name:="Charts"
With ActiveChart.Parent
.Height = 500
.Width = 600
.Top = 100
.Left = 100
End With

and stepped thru the code and IT WORKED! (adding web page bookmark now...) So now I have a better idea (but still working on the understanding of it ;-) of
what to do.

Thanks to David for the index and to Jon for explaining a basic Excel function that nobody seems to understand. You know, Excel does more than spreadsheet
work, folks """
Toby Erkson
Oregon, USA