Apply custom chart type - VBA

[Fredrik E. Nilsen]

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.

 

[Andy Pope]

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

 

[Vergel Adriano]

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

 

[Vergel Adriano]

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

 

[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

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]

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!