Microsoft Office Tutorials and References
In Depth Information
Adding an Embedded Chart to a Worksheet
Adding An EMBEddEd cHArT To A WorksHEET
When you embed a chart onto a worksheet, there is more to consider than when you create a chart
for its own chart sheet. When you embed a chart, you need to specify which worksheet you want the
chart to be on (handled by the Location property), and where on the worksheet you want the chart
to be placed. The following macro is an example of how to place a column chart into range D3:J20
of the active worksheet, close to the source range as shown in Figure 14-3.
‘Declare an Object variable for the chart
‘and for the embedded ChartObject.
Dim myChart1 As Chart, cht1 As ChartObject
‘Declare a Range variable to specify what range
‘the chart will occupy, and on what worksheet.
Dim rngChart1 As Range, DestinationSheet As String
‘The chart will be placed on the active worksheet.
DestinationSheet = ActiveSheet.Name
‘Add a new chart
Set myChart1 = Charts.Add
‘Specify the chart’s location as the active worksheet.
Set myChart1 = _
‘Define the new chart’s source data
‘Define the type of chart, in this case, a Column chart.
myChart1.ChartType = xlColumnClustered
‘Activate the chart to identify its ChartObject.
‘The (1) assumes this is the first (index #1) chart object
‘on the worksheet.
Set cht1 = ActiveChart.Parent
‘Specify the range you want the chart to occupy.
Set rngChart1 = Range(“D3:J20”)
cht1.Left = rngChart1.Left
cht1.Width = rngChart1.Width
cht1.Top = rngChart1.Top
cht1.Height = rngChart1.Height
‘Deselect the chart by selecting a cell.