How do you programatically format embedded charts

[jonefer]

Creating one programmatically is no problem....

It's making adjustments to it that I have a problem with...

I'm stuck with how to refer to a chart object that you just made.
I obviously can't refer to it as 'Chart 4' if I do this to twenty charts...

So I tried this but it doesn't work: (any tips?)


strCurrentChart = ActiveChart.Name
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "Call Access"
End With
ActiveChart.ChartArea.Select
ActiveChart.Location Where:=xlLocationAsObject, Name:="Multi"
ActiveSheet.Shapes(strCurrentChart).IncrementLeft -183.75
ActiveSheet.Shapes(strCurrentChart).IncrementTop -121.5
ActiveSheet.Shapes(strCurrentChart).ScaleWidth 0.52, msoFalse,
msoScaleFromTopLeft
ActiveSheet.Shapes(strCurrentChart).ScaleHeight 0.56, msoFalse,
msoScaleFromTopLeft
ActiveChart.Legend.Select
Selection.AutoScaleFont = True
With Selection.Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 8

End With

 

[Jon Peltier]

How are you creating the chart?

Define a variable:

Dim cht As Chart

Then refer to it as you create it:

Set cht = ActiveSheet.ChartObjects.Add(blah, blah, blah, blah).Chart

or

Charts.Add
ActiveChart.Location Where:=xlLocationAsObject, Name:="Blah"
Set cht = ActiveChart

Then replace ActiveChart in your recorded code with cht.

- Jon

 

[jonefer]

Ok, the chart variable seems to be fine, except for one specific detail...

when I refer to the embedded chart object on a worksheet it needs to be
referred to as follows: ActiveSheet.Shapes("Blah #").

assuming my chart variable is NewChart

I need to pass the string name that gets created for the chart
I tested it and it was 'Chart16'... Msgbox NewChart.Name produced 'Chart16'
but when I pass that (NewChart.Name) to the following... it doesn't have a
clue what to do.

Here's the code:

ActiveChart.Location Where:=xlLocationAsObject, Name:="Multi"
ActiveSheet.Shapes(NewChart.Name).IncrementLeft -183.75
ActiveSheet.Shapes(NewChart.Name).IncrementTop -121.5
ActiveSheet.Shapes(NewChart.Name).ScaleWidth 0.52, msoFalse,
msoScaleFromTopLeft
ActiveSheet.Shapes(NewChart.Name).ScaleHeight 0.56, msoFalse,
msoScaleFromTopLeft

=====================================

this step wil be critical, because i plan on embedding multiple charts,
size, space arrange them appropriately for a user so that they can print them.

 

[Jon Peltier]

IMO it's better to manipulate the size of the chart object using its Left,
Top, Width, and Height properties, rather than the IncrementXxx or ScaleXxx
methods of the shape.

With MyChart.Parent '' Note: The ChartObject is the Parent of the Chart
.Left = 100
.Top = 100
.Width = 350
.Height = 250
End With

If you want to retain your shape modifying code, use this:

ActiveSheet.Shapes(NewChart.Parent.Name)

because the chart object has the relevant name, not the chart.

- Jon