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(msoTextOrientationHo rizontal, 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" wrote in message
...
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(msoTextOrientationHo rizontal, 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(msoTextOrientationHo rizontal, 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(msoTextOrientationHo rizontal, 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"
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
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