I am embedding 4 charts into a worksheet and would like to position them into a nice 4 quadrant view. However, when recording a macro, the position of the chart at the end of Chart.Add, etc routine is arbitrary. Similarly, when I record manually repositioning the chart and then replay the macro, the results differ. Here is the code for creating 2 of the 4 charts. The recorder uses the ActiveSheet.Shaped("chart 1").IncrementLeft nnnn and .IncrementTop nnn statements to perform the repositioning.
Any suggestions for predicatably positioning embedded charts within a sheet
KC
Charts.Add
ActiveChart.ChartType = xlLineMarkers
ActiveChart.SetSourceData Source:=Sheets("Cancellation - Swap Analysis"). _
Range("A6:M8"), PlotBy:=xlRows
ActiveChart.Location Where:=xlLocationAsObject, Name:= _
"Cancellation - Swap Analysis"
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "Month to Month Cancel Count 2005 - 2006"
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = False
End With
' following is needed to make the chart name dynamic between calls of the macro.
' There should be an easier way to get the correct chart number but this works.
chartname = ActiveChart.Name
chart_name_num = Mid(chartname, InStrRev(chartname, "Chart"))
ActiveSheet.Shapes(chart_name_num).IncrementLeft -212.25
ActiveSheet.Shapes(chart_name_num).IncrementTop 170.25
ActiveChart.ChartTitle.Select
ActiveSheet.ChartObjects(chart_name_num).Activate
ActiveChart.ChartTitle.Select
Selection.Text = "='Cancellation - Swap Analysis'!R24C1"
ActiveWindow.Visible = False
Range("J26").Select
' create cum count chart
Charts.Add
ActiveChart.ChartType = xlLineMarkers
ActiveChart.SetSourceData Source:=Sheets("Cancellation - Swap Analysis"). _
Range("A11:M13"), PlotBy:=xlRows
ActiveChart.Location Where:=xlLocationAsObject, Name:= _
"Cancellation - Swap Analysis"
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "Cum Count"
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = False
End With
chartname = ActiveChart.Name
chart_name_num = Mid(chartname, InStrRev(chartname, "Chart"))
ActiveSheet.Shapes(chart_name_num).IncrementLeft 238.5
ActiveSheet.Shapes(chart_name_num).IncrementTop 135.75
ActiveChart.ChartTitle.Select
Selection.Text = "='Cancellation - Swap Analysis'!R24C4"
precisely position embedded charts in worksheet
Steve Strong
Hi KC
Since you haven't gotten a reply here, that I can see, try asking the
Excel specialists in the Excel programming newsgroup:
http://msdn.microsoft.com/newsgroups/default.aspx dg=microsoft.public.ex
cel.programming&lang=en&cr=US
<BLOCKED::http://msdn.microsoft.com/newsgroups/default.aspx dg=microsoft
.public.excel.programming&lang=en&cr=US>
-- Cindy
Anil Narayanan
The microsoft.public.excel.charting is probably even a better source of information on charts.
On this web page, I describe a little about chart positioning:
http://peltiertech.com/Excel/ChartsHowTo/ResizeAndMoveAChart.html
It's better to use the absolute Shape position properties .Top and .Left and size properties .Height and .Width than the relative Shape .IncrementXX and .ScaleXX properties. It's probably better to apply these position and size properties to the ChartObject than to the Shape.
It's probably easiest of all to add the chart as a ChartObject in the first place, since the ChartObjects.Add method includes the position and size properties as arguments:
http://peltiertech.com/Excel/ChartsHowTo/QuickChartVBA.html
- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______