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

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

• Rafer

  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/
  _______
  

  
  
  

• korenwolf

  workin' on it

  -brenda (ISV Buddy Team)

  
  
  

• Jeff Johnson

  *nudge*
  
  

• bloo

  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/
  _______
  

  
  
  

• bosox

  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.

  
  
  

• waynehans

  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
  _______

   

   

  
  
  

• 1234444

  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

  
  
  

• Preston Bostrom

  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.

  
  
  

• MichaelMCSE

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

  I really appreciate it.

  
  
  

• bgs264

  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)

  
  
  

• luckyco

  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
  _______

  
  
  
• Setting Chart Title through VBA sometimes works sometimes doesn't.