title: VBA Code Error: Chart Title in Excel
body: <p>I have a sas code that writes text (Lets call is "sas text") in an excel file and then VBA creates the graph in excel and copy the "sas text" in the title. 
  Whenver the "sas text" length is samll the VBA works fine, but whenever the "SAS text" length is long, VBA gives a run time error - "VBA, method "text" of object error.</p>
  
  <p>My code is:</p>
  
  <pre><code>Sub FormatChart()
  Let Title = Sheets("Sheet1").Cells(2, 1)
  Let Title1 = Sheets("Sheet1").Cells(2, 2)
  Let Title2 = Sheets("Sheet1").Cells(2, 3)
  Let Title3 = Sheets("Sheet1").Cells(2, 4)
      ActiveSheet.ChartObjects("Chart 1").Activate
      ActiveChart.SeriesCollection(1).Select
      ActiveSheet.ChartObjects("Chart 1").Activate
      ActiveChart.Axes(xlCategory).Select
      ActiveChart.Axes(xlCategory).ReversePlotOrder = True
      ActiveSheet.ChartObjects("Chart 1").Activate
      ActiveChart.ChartTitle.Select
      ActiveSheet.ChartObjects("Chart 1").Activate
      ActiveChart.ChartTitle.Text = Title &amp; Title3 &amp; Chr(10) &amp; Title1 &amp; "to " &amp; Title2 &amp; ": People with 25 or more visits" &amp; Chr(10) &amp; "Source: www.xxxxxxxxxxx.xxx.xxx"
      With ActiveChart.ChartTitle.Font
      .Name = "Arial"
      .FontStyle = "Bold"
      .Size = 8
      End With
      With ActiveChart.Axes(xlCategory).TickLabels.Font
      .Name = "Arial"
      .FontStyle = "Regular"
      .Size = 7
      End With
      With ActiveChart.Axes(xlValue).TickLabels.Font
      .Name = "Arial"
      .FontStyle = "Regular"
      .Size = 7
      End With
      With ActiveChart.PlotArea.Interior
      .ColorIndex = 2
      .PatternColorIndex = 1
      .Pattern = xlSolid
      End With
      Range("S4").Select
      ActiveWindow.SmallScroll Down:=48
      ActiveSheet.ChartObjects("Chart 1").Activate
      ActiveChart.Legend.Select
      Selection.delete
      ActiveWindow.SmallScroll Down:=45
      ActiveSheet.ChartObjects("Chart 1").Activate
      ActiveChart.PlotArea.Select
      ActiveSheet.ChartObjects("Chart 1").Activate
      ActiveWindow.SmallScroll Down:=-45
      ActiveSheet.ChartObjects("Chart 1").Activate
      ActiveChart.Axes(xlValue).MajorGridlines.Select
      ActiveSheet.ChartObjects("Chart 1").Activate
      ActiveWindow.SmallScroll Down:=-54
      ActiveSheet.ChartObjects("Chart 1").Activate
      ActiveChart.ChartArea.Select
      ActiveChart.ChartArea.Width = 500
      ActiveChart.ChartArea.Height = 1000
      ActiveSheet.ChartObjects("Chart 1").Activate
      ActiveSheet.ChartObjects("Chart 1").Activate
      ActiveChart.Axes(xlValue).MajorGridlines.Select
      ActiveSheet.ChartObjects("Chart 1").Activate
      ActiveSheet.ChartObjects("Chart 1").Activate
      ActiveSheet.ChartObjects("Chart 1").Activate
      ActiveSheet.ChartObjects("Chart 1").Activate
      ActiveSheet.ChartObjects("Chart 1").Activate
      ActiveChart.Axes(xlValue).Select
      ActiveSheet.ChartObjects("Chart 1").Activate
      ActiveChart.Axes(xlValue).MajorGridlines.Select
      ActiveSheet.ChartObjects("Chart 1").Activate
      ActiveSheet.ChartObjects("Chart 1").Activate
      ActiveChart.Axes(xlValue).Select
      ActiveChart.Axes(xlValue).MinimumScale = 0
      ActiveChart.Axes(xlValue).MaximumScale = 100
      ActiveChart.Axes(xlValue).MajorUnit = 20
      ActiveChart.Axes(xlValue).MajorUnit = 10
          With ActiveChart.SeriesCollection(1)
          .Interior.Color = RGB(0, 51, 153)
      End With
          ActiveSheet.ChartObjects("Chart 1").Activate
          Dim c As Chart
  Dim s As Series
  Dim iPoint As Long
  Dim nPoint As Long
  
  Set c = ActiveChart
  Set s = c.SeriesCollection(1)
  
  nPoint = s.Points.Count
  For iPoint = 1 To nPoint
      If s.XValues(iPoint) = "MINNESOTA STATE AVERAGE " Then
          s.Points(iPoint).Interior.Color = RGB(80, 116, 77)
      End If
          If s.XValues(iPoint) = "NATIONAL AVERAGE " Then
          s.Points(iPoint).Interior.Color = RGB(80, 116, 77)
      End If
  Next iPoint
  
          ActiveSheet.ChartObjects("Chart 1").Activate
      Selection.Cut
      Sheets("Chart1").Select
      ActiveChart.Paste
  End Sub
  </code></pre>
  
  <p>On debugging, it highlights this line of the code</p>
  
  <pre><code>"        ActiveChart.ChartTitle.Text = Title &amp; Title3 &amp; Chr(10) &amp; Title1 &amp; "to " &amp; Title2 &amp; ": People with 25 or more visits" &amp; Chr(10) &amp; "Source: www.xxxxxxxxxxx.xxx.xxx"
  "
  </code></pre>
  
  <p>Thanks much!</p>