Hi,

I have written a VBA-code to apply custom formatting and user-defined
chart types to embedded charts. Now I'm trying to figure out how to
use it even if the chart is in chart sheet. Here is the code I'm
using:

Sub Line()
Dim shp As Shape
If Not ActiveChart Is Nothing Then
With ActiveChart.Parent
.Height = 252.75
.Width = 342.75
End With
ActiveChart.ApplyCustomType ChartType:=xlUserDefined, TypeName:= _
"Line"
ActiveChart.Legend.Left = 0
ActiveChart.Legend.Top = 250
ActiveChart.PlotArea.Left = 0
ActiveChart.PlotArea.Top = 25
ActiveChart.PlotArea.Height = 205
ActiveChart.PlotArea.Width = 340
On Error Resume Next
Set shp = ActiveChart.Shapes("Y-axis title")
If shp Is Nothing Then
ActiveChart.Shapes.AddTextbox(msoTextOrientationHorizontal, 0, 2,
0, 0).Select
Selection.Characters.Text = "Y-axis title"
With Selection.Font
.Name = "Arial"
.FontStyle = "Normal"
.Size = 10
.ColorIndex = xlAutomatic
.Background = xlTransparent
End With
With Selection
.AutoScaleFont = False
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlCenter
.ReadingOrder = xlContext
.Orientation = xlHorizontal
.AutoSize = True
.Placement = xlMove
.PrintObject = True
.Name = "Y-axis title"
End With
End If
ActiveChart.Shapes("X-axis title").Delete
Else
MsgBox "You have to select a chart before performing this
action.", _
vbExclamation, "No chart selected."
End If
End Sub

The problem here is the .Height and .Width properties, since they are
only supported in embedded charts obviously.

Any suggestions on how I should modify the code so it will skip the
..Height and .Width properties if the charts are in a chart sheet?

There are probably other things that should be done to clean up the
code too. I've worked it out through a lot of trial and error and my
current knowledge is too limited to understand all of it.

--
Fredrik E. Nilsen

Hi,

One way is to use the result of Typename.

msgbox typename( activechart.Parent)

Activechart being embedded on work/chart sheet returns - ChartObject
Activechart being a chart sheet returns -Workbook

Cheers
Andy

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
"Fredrik E. Nilsen" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Hi,
>
> I have written a VBA-code to apply custom formatting and user-defined
> chart types to embedded charts. Now I'm trying to figure out how to
> use it even if the chart is in chart sheet. Here is the code I'm
> using:
>
> Sub Line()
> Dim shp As Shape
> If Not ActiveChart Is Nothing Then
> With ActiveChart.Parent
> .Height = 252.75
> .Width = 342.75
> End With
> ActiveChart.ApplyCustomType ChartType:=xlUserDefined, TypeName:= _
> "Line"
> ActiveChart.Legend.Left = 0
> ActiveChart.Legend.Top = 250
> ActiveChart.PlotArea.Left = 0
> ActiveChart.PlotArea.Top = 25
> ActiveChart.PlotArea.Height = 205
> ActiveChart.PlotArea.Width = 340
> On Error Resume Next
> Set shp = ActiveChart.Shapes("Y-axis title")
> If shp Is Nothing Then
> ActiveChart.Shapes.AddTextbox(msoTextOrientationHorizontal, 0, 2,
> 0, 0).Select
> Selection.Characters.Text = "Y-axis title"
> With Selection.Font
> .Name = "Arial"
> .FontStyle = "Normal"
> .Size = 10
> .ColorIndex = xlAutomatic
> .Background = xlTransparent
> End With
> With Selection
> .AutoScaleFont = False
> .HorizontalAlignment = xlLeft
> .VerticalAlignment = xlCenter
> .ReadingOrder = xlContext
> .Orientation = xlHorizontal
> .AutoSize = True
> .Placement = xlMove
> .PrintObject = True
> .Name = "Y-axis title"
> End With
> End If
> ActiveChart.Shapes("X-axis title").Delete
> Else
> MsgBox "You have to select a chart before performing this
> action.", _
> vbExclamation, "No chart selected."
> End If
> End Sub
>
> The problem here is the .Height and .Width properties, since they are
> only supported in embedded charts obviously.
>
> Any suggestions on how I should modify the code so it will skip the
> .Height and .Width properties if the charts are in a chart sheet?
>
> There are probably other things that should be done to clean up the
> code too. I've worked it out through a lot of trial and error and my
> current knowledge is too limited to understand all of it.
>
> --
> Fredrik E. Nilsen

Hi Fredrik,

If the activechart parent's name is not equal to the workbook's name, then
the chart is on a worksheet. Otherwise, it's on a Chart Sheet.

With ActiveChart.Parent
If .Name <> ThisWorkbook.Name Then
.Height = 252.75
.Width = 342.75
End If
End With



--
Hope that helps.

Vergel Adriano


"Fredrik E. Nilsen" wrote:

> Hi,
>
> I have written a VBA-code to apply custom formatting and user-defined
> chart types to embedded charts. Now I'm trying to figure out how to
> use it even if the chart is in chart sheet. Here is the code I'm
> using:
>
> Sub Line()
> Dim shp As Shape
> If Not ActiveChart Is Nothing Then
> With ActiveChart.Parent
> .Height = 252.75
> .Width = 342.75
> End With
> ActiveChart.ApplyCustomType ChartType:=xlUserDefined, TypeName:= _
> "Line"
> ActiveChart.Legend.Left = 0
> ActiveChart.Legend.Top = 250
> ActiveChart.PlotArea.Left = 0
> ActiveChart.PlotArea.Top = 25
> ActiveChart.PlotArea.Height = 205
> ActiveChart.PlotArea.Width = 340
> On Error Resume Next
> Set shp = ActiveChart.Shapes("Y-axis title")
> If shp Is Nothing Then
> ActiveChart.Shapes.AddTextbox(msoTextOrientationHorizontal, 0, 2,
> 0, 0).Select
> Selection.Characters.Text = "Y-axis title"
> With Selection.Font
> .Name = "Arial"
> .FontStyle = "Normal"
> .Size = 10
> .ColorIndex = xlAutomatic
> .Background = xlTransparent
> End With
> With Selection
> .AutoScaleFont = False
> .HorizontalAlignment = xlLeft
> .VerticalAlignment = xlCenter
> .ReadingOrder = xlContext
> .Orientation = xlHorizontal
> .AutoSize = True
> .Placement = xlMove
> .PrintObject = True
> .Name = "Y-axis title"
> End With
> End If
> ActiveChart.Shapes("X-axis title").Delete
> Else
> MsgBox "You have to select a chart before performing this
> action.", _
> vbExclamation, "No chart selected."
> End If
> End Sub
>
> The problem here is the .Height and .Width properties, since they are
> only supported in embedded charts obviously.
>
> Any suggestions on how I should modify the code so it will skip the
> ..Height and .Width properties if the charts are in a chart sheet?
>
> There are probably other things that should be done to clean up the
> code too. I've worked it out through a lot of trial and error and my
> current knowledge is too limited to understand all of it.
>
> --
> Fredrik E. Nilsen
>

The code I gave wouldn't work if the code and chart are on different
workbooks. It should be like this:

With ActiveChart.Parent
If .Name <> ActiveWorkbook.Name Then
.Height = 252.75
.Width = 342.75
End If
End With



--
Hope that helps.

Vergel Adriano


"Vergel Adriano" wrote:

> Hi Fredrik,
>
> If the activechart parent's name is not equal to the workbook's name, then
> the chart is on a worksheet. Otherwise, it's on a Chart Sheet.
>
> With ActiveChart.Parent
> If .Name <> ThisWorkbook.Name Then
> .Height = 252.75
> .Width = 342.75
> End If
> End With
>
>
>
> --
> Hope that helps.
>
> Vergel Adriano
>
>
> "Fredrik E. Nilsen" wrote:
>
> > Hi,
> >
> > I have written a VBA-code to apply custom formatting and user-defined
> > chart types to embedded charts. Now I'm trying to figure out how to
> > use it even if the chart is in chart sheet. Here is the code I'm
> > using:
> >
> > Sub Line()
> > Dim shp As Shape
> > If Not ActiveChart Is Nothing Then
> > With ActiveChart.Parent
> > .Height = 252.75
> > .Width = 342.75
> > End With
> > ActiveChart.ApplyCustomType ChartType:=xlUserDefined, TypeName:= _
> > "Line"
> > ActiveChart.Legend.Left = 0
> > ActiveChart.Legend.Top = 250
> > ActiveChart.PlotArea.Left = 0
> > ActiveChart.PlotArea.Top = 25
> > ActiveChart.PlotArea.Height = 205
> > ActiveChart.PlotArea.Width = 340
> > On Error Resume Next
> > Set shp = ActiveChart.Shapes("Y-axis title")
> > If shp Is Nothing Then
> > ActiveChart.Shapes.AddTextbox(msoTextOrientationHorizontal, 0, 2,
> > 0, 0).Select
> > Selection.Characters.Text = "Y-axis title"
> > With Selection.Font
> > .Name = "Arial"
> > .FontStyle = "Normal"
> > .Size = 10
> > .ColorIndex = xlAutomatic
> > .Background = xlTransparent
> > End With
> > With Selection
> > .AutoScaleFont = False
> > .HorizontalAlignment = xlLeft
> > .VerticalAlignment = xlCenter
> > .ReadingOrder = xlContext
> > .Orientation = xlHorizontal
> > .AutoSize = True
> > .Placement = xlMove
> > .PrintObject = True
> > .Name = "Y-axis title"
> > End With
> > End If
> > ActiveChart.Shapes("X-axis title").Delete
> > Else
> > MsgBox "You have to select a chart before performing this
> > action.", _
> > vbExclamation, "No chart selected."
> > End If
> > End Sub
> >
> > The problem here is the .Height and .Width properties, since they are
> > only supported in embedded charts obviously.
> >
> > Any suggestions on how I should modify the code so it will skip the
> > ..Height and .Width properties if the charts are in a chart sheet?
> >
> > There are probably other things that should be done to clean up the
> > code too. I've worked it out through a lot of trial and error and my
> > current knowledge is too limited to understand all of it.
> >
> > --
> > Fredrik E. Nilsen
> >

On Thu, 19 Apr 2007 13:18:50 +0100, "Andy Pope" <(E-Mail Removed)>
wrote:

>Hi,
>
>One way is to use the result of Typename.
>
>msgbox typename( activechart.Parent)
>
>Activechart being embedded on work/chart sheet returns - ChartObject
>Activechart being a chart sheet returns -Workbook

Thanks for your reply Andy. My problem is: how do I use it? I have
limited understanding of Excel VBA but I'm working on it.

--
Fredrik E. Nilsen

On Thu, 19 Apr 2007 05:30:01 -0700, Vergel Adriano
<(E-Mail Removed)> wrote:

>The code I gave wouldn't work if the code and chart are on different
>workbooks. It should be like this:
>
>With ActiveChart.Parent
> If .Name <> ActiveWorkbook.Name Then
> .Height = 252.75
> .Width = 342.75
> End If
>End With

This seems to work great, thank you very much!

--
Fredrik E. Nilsen