Returning the name of an embedded chart?

[anthonyg]

I have a number of tables in a worksheet and need to generate a standard
graph beside each one. I can generate the graphs OK but my problem is by
default they always appear in the middle of the current view and are the
wrong size.

My ideal solution would be to be able to control where the chart first
appears, and what its dimensions are, when it is created. Is there anyway to
do this?
(I'm currently using
Charts.Add
ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"
in order to create the chart.)

At the moment, I am resizing each chart using the Shapes method, but first I
have to find out the name:

Dim ref As Variant
ref = Right(ActiveChart.Name, Len(ActiveChart.Name) - Len(ActiveSheet.Name &
" "))
ActiveSheet.Shapes(ref).ScaleHeight 0.6, msoFalse
ActiveSheet.Shapes(ref).ScaleWidth 1.2, msoFalse

The definition of "ref" is rather forced since ActiveChart.Name returns a
string such as "Sheet1 Chart 215" not the "Chart 215" bit, which is what
Shapes needs. I can't believe there isn't a more efficient way to do this.
Any suggestions?

Thanks

 

[Jim Cone]

I've always believed that MS set up chart nomenclature to weed out the
faint of heart. <g>
What you have been adding to the workbook is a chart sheet. (chart).
The Location method was then moving it to sheet1 where it became an
embedded chart. An embedded chart is contained in a ChartObject.
The ChartObject has the size and location properties.
The chart inside the ChartObject is what you access to change most of the
other properties.
Using object references eliminates the need to know the chart name...

Sub ChartMe()
Dim chtTable As Excel.Chart
Dim chtHolder As Excel.ChartObject '(the container)

With Worksheets("Sheet1")
'top, left,width,height
Set chtHolder = .ChartObjects.Add(.Range("B2").Left, .Range("B2").Top, 250, 125)

'Establish reference to the Chart in the ChartObject.
Set chtTable = chtHolder.Chart

'Use the ChartWizard property to customize the chart - see help for other options.
chtTable.ChartWizard Source:=.Range("G2:H10"), Gallery:=xlXYScatter
End With

'repeat above for each chart needed

Set chtTable = Nothing
Set chtHolder = Nothing
End Sub
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware




"anthonyg" <[email protected]>
wrote in message
I have a number of tables in a worksheet and need to generate a standard
graph beside each one. I can generate the graphs OK but my problem is by
default they always appear in the middle of the current view and are the
wrong size.

My ideal solution would be to be able to control where the chart first
appears, and what its dimensions are, when it is created. Is there anyway to
do this?
(I'm currently using
Charts.Add
ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"
in order to create the chart.)

At the moment, I am resizing each chart using the Shapes method, but first I
have to find out the name:
Dim ref As Variant
ref = Right(ActiveChart.Name, Len(ActiveChart.Name) - Len(ActiveSheet.Name & " "))
ActiveSheet.Shapes(ref).ScaleHeight 0.6, msoFalse
ActiveSheet.Shapes(ref).ScaleWidth 1.2, msoFalse
The definition of "ref" is rather forced since ActiveChart.Name returns a
string such as "Sheet1 Chart 215" not the "Chart 215" bit, which is what
Shapes needs. I can't believe there isn't a more efficient way to do this.
Any suggestions?
Thanks

 

[Jon Peltier]

If it's an active chart, use ActiveChart.Parent.Name to get the name to
insert into Shapes(). Or directly change the dimensions of the
ActiveChart.Parent (the ChartObject Jim refers to):

ActiveChart.Parent.Height = 200
ActiveChart.Parent.Width = 300

- Jon