Derek Smyth


Hello folks,

Wonder if anyone has the solution to the following problem.

Got an Excel addin that the user can use to generate Charts. The users are very lazy and want the addin to set the titles for the chart as well as for the axis. When I tested the code everything works fine and the charts including it's titles are set. Everyone is happy.

However in certain spreadsheets the code throws an error.

Method 'HasTitle' of object '_Chart' failed.

Here's the code that causes the error

Dim chrt As Chart
Set chrt = ActiveWorkbook.Charts.Add
chrt.ChartType = xlXYScatterSmoothNoMarkers
chrt.HasTitle = True 'error here
chrt.ChartTitle.Text = Me.State.ChartTitle

'chart has one series by default
chrt.PlotArea.Select
With Selection.Border
.ColorIndex = 16
.Weight = xlThin
.LineStyle = xlContinuous
End With
Selection.Interior.ColorIndex = xlNone

Sometimes it works and sometimes it doesn't.

Anyone





Re: Setting Chart Title through VBA sometimes works sometimes doesn't.

Derek Smyth


*nudge*





Re: Setting Chart Title through VBA sometimes works sometimes doesn't.

MS ISV Buddy Team

workin' on it

-brenda (ISV Buddy Team)







Re: Setting Chart Title through VBA sometimes works sometimes doesn't.

Jon Peltier

Wild guess, based on a dim recollection. Try this:

chrt.HasTitle = False
chrt.HasTitle = True

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






Re: Setting Chart Title through VBA sometimes works sometimes doesn't.

Derek Smyth

Hi Jon,

No luck with the suggestion, still doesn't work, but your sort of right. It's as though certain properties, or sub classes, of the chart object aren't getting instantiated.

It's rather annoying. Thanks for the suggestion.






Re: Setting Chart Title through VBA sometimes works sometimes doesn't.

Derek Smyth

Hello Brenda,

Sorry for the nudge, someone here is nipping my ear about the problem and they're just not interested in an alternative solution. If it would help I can send you the code and spreadsheets, let you see the error as it happens.






Re: Setting Chart Title through VBA sometimes works sometimes doesn't.

Jon Peltier

Derek -

I'll look at it if you'd like.

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

 

 






Re: Setting Chart Title through VBA sometimes works sometimes doesn't.

MS ISV Buddy Team

Per the support engineer:

When I run your code, I always get the same error as yours. Then I recorded a macro when creating chart and setting title, it worked. Here is the code I got. Can you try to record a macro.

The line in Bold and Italic letter is quite important. Without it, I get error.

===

Sub Macro1()
'
' Macro1 Macro
'

'
Charts.Add
ActiveChart.ChartType = xlColumnClustered
ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("A1:B3"), PlotBy:= _
xlColumns

ActiveChart.Location Where:=xlLocationAsNewSheet
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "a"
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = False
End With
With ActiveChart
.HasAxis(xlCategory, xlPrimary) = True
.HasAxis(xlValue, xlPrimary) = True
End With
ActiveChart.Axes(xlCategory, xlPrimary).CategoryType = xlAutomatic
End Sub

===

-brenda (ISV Buddy Team)






Re: Setting Chart Title through VBA sometimes works sometimes doesn't.

Jon Peltier

Derek sent me his code, and I found the same error. The problem and solution are more detailed:

In his workbooks which worked as expected, Excel found chartable data in the selected range of cells, so it created a chart series without benefit of SetSourceData.

In the workbooks which failed, Excel could not recognize chartable data, so it created the chart without any initial series.

You can see this behavior. The first case is like starting the chart wizard with data selected: Excel tries to fit the selected data somehow into a chart, so you're likely to end up with one or more series without specifying any data beyond what was initially selected. The second case is like, for example, activating a blank sheet and running the chart wizard: Excel can't find data to chart, so it makes a chart without a series.

Not only does the chart have no series, it has none of the other features: no plot area, no legend, no placeholders for titles. Naturally Derek's code fails while trying to insert a title where there is no infrastructure to support one.

Derek's problem is solved by moving his code that manipulates title and plot area until after he uses his SetSourceData statement.

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






Re: Setting Chart Title through VBA sometimes works sometimes doesn't.

Derek Smyth

Many many thanks guys for the time you spent in working that problem out !!!!!

I really appreciate it.






Re: Setting Chart Title through VBA sometimes works sometimes doesn't.

arianraj

try this out..

Public Sub AddSeriesToChart()

Dim counter As Integer
Dim index As Integer
Dim columnPosition As Integer
Dim curveLength As Long
Dim seriesCount As Integer
Dim removeData As Boolean
Dim myChart As Chart

shForwardCurves.Activate

'On Error Resume Next

ActiveSheet.ChartObjects(1).Delete

' getting the length of the curves and number of series
Dim curveRange As Variant
'curveRannge = shForwardCurves.Range(shForwardCurves.Cells(23, 2), shForwardCurves.Cells(23, 2).End(xlDown).End(xlToRight))
curveRange = shForwardCurves.Range(shForwardCurves.Cells(21, 3), shForwardCurves.Cells(21, 3).End(xlToRight))
'curveLength = UBound(curveRange, 1) - 1
seriesCount = UBound(curveRange, 2)
curveLength = 365

Range("B2").Select
Set myChart = Charts.Add
Set myChart = myChart.Location(Where:=xlLocationAsObject, Name:="Forward Curves")
With myChart

.ChartType = xlLine
.SeriesCollection(1).XValues = "='Forward Curves'!R23C2:R" & curveLength + 23 & "C2"
.SeriesCollection(1).values = "='Forward Curves'!R23C3:R" & curveLength + 23 & "C3"
.HasTitle = True
.ChartTitle.Text = "Forward Curve"

' check if the data is present or not; based on that put one dummy data and after remove that
If shForwardCurves.Cells(23, 3).Value = "" Then
shForwardCurves.Cells(23, 3).Value = 11
removeData = True
End If
.SeriesCollection(1).Name = "='Forward Curves'!R21C3"
If removeData Then
removeData = False
shForwardCurves.Cells(23, 3).Value = ""
End If
' .Location Where:=xlLocationAsObject, Name:="Forward Curves"

With .Parent
.Top = Range("b2").Top
.Left = Range("b2").Left
.Name = "My Chart"
'.Title = True
'.Text.Characters = "Forward Curve"
End With
For index = 2 To seriesCount
ActiveChart.SeriesCollection.NewSeries
columnPosition = index + 2
ActiveChart.SeriesCollection(index).values = "='Forward Curves'!R23C" & CStr(columnPosition) & ":R" & curveLength + 23 & "C" & CStr(columnPosition)
' check if the data is present or not; based on that put one dummy data and after remove that
If shForwardCurves.Cells(23, columnPosition).Value = "" Then
shForwardCurves.Cells(23, columnPosition).Value = 11
removeData = True
End If
ActiveChart.SeriesCollection(index).Name = "='Forward Curves'!R21C" & CStr(columnPosition)
If removeData Then
removeData = False
shForwardCurves.Cells(23, columnPosition).Value = ""
End If
Next

With .PlotArea
.Top = 19
.Height = 229
End With
End With

With shForwardCurves.Shapes("My Chart")
.ScaleWidth 1.92, msoFalse, msoScaleFromTopLeft
.ScaleHeight 1.05, msoFalse, msoScaleFromTopLeft
End With
End Sub






Re: Setting Chart Title through VBA sometimes works sometimes doesn't.

Jon Peltier

If you hunt through the proffered code, you will see that the lines assigning data to the chart come before the lines editing chart elements which are not present until after data has been assigned to the chart. This was the gist of the earlier answer which solved the problem. The rest of the code is, of course, too narrowly associated with a specific situation and too loaded with extraneous statements to help the OP.

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