Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to name a chart for referencing in VBA? And move it.
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to name a chart for referencing in VBA? And move it.
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 Whe=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 On Tue, 27 Apr 2004 16:09:40 -0700, Toby Erkson < wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to name a chart for referencing in VBA? And move it. UPDATED.
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 whe=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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to name a chart for referencing in VBA? And move it. UPDATED.
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/ChartsH...oveAChart.html Bingo! I replaced my trouble code with a snip from his page for testing: ActiveChart.Location whe=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 :-p""" Toby Erkson Oregon, USA On Wed, 28 Apr 2004 08:26:39 -0700, Toby Erkson < wrote: 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 whe=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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
chart referencing | Excel Discussion (Misc queries) | |||
referencing a value from a chart with 3 variables | New Users to Excel | |||
How to draw a line on a chart, and have it move with the chart? | Charts and Charting in Excel | |||
Referencing chart sheets in vba | Charts and Charting in Excel | |||
Formula referencing deleted row (move up automatically) | Excel Worksheet Functions |