How do you programatically format embedded charts
 

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 Whe=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

How do you programatically format embedded charts
 

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 Whe=xlLocationAsObject, Name:="Blah"
Set cht = ActiveChart

Then replace ActiveChart in your recorded code with cht.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"jonefer" wrote in message
...

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 Whe=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

How do you programatically format embedded charts
 

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 Whe=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" wrote:

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 Whe=xlLocationAsObject, Name:="Blah"
Set cht = ActiveChart

Then replace ActiveChart in your recorded code with cht.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"jonefer" wrote in message
...

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 Whe=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

How do you programatically format embedded charts
 

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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"jonefer" wrote in message
...
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 Whe=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" wrote:

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 Whe=xlLocationAsObject, Name:="Blah"
Set cht = ActiveChart

Then replace ActiveChart in your recorded code with cht.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"jonefer" wrote in message
...

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 Whe=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