Excel Chart.Export Doesn't Export Text Box

[AGlazer]

Hi, everyone.

Question. I've got a macro that creates a text box in a chart, then exports that chart. The text box doesn't appear in the export if I export it during the macro. However, if I export the graph after the macro has finished running, the text box is included. Everything else exports fine. Any ideas?

Code is below:

Sub Test()

Charts.Add
Set wkgCht = ActiveChart
With wkgCht
.SetSourceData Source:=wkgSht.Range("A1:B" & LastinColumn(wkgSht.Range("B1")) - 1), PlotBy:=xlRows
.ApplyCustomType ChartType:=xlUserDefined, TypeName:="pbs_ttp"
.Location Where:=xlLocationAsNewSheet, Name:="(c)" & wkgSht.Name
.HasTitle = True
.ChartTitle.Characters.Text = _
"Time-To-Productivity" & Chr(10) & toplevelFullName & ", " & secondLevelFullName
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Working Days"
End With
wkgCht.Shapes.AddTextbox(msoTextOrientationHorizontal, 408#, 153.75, _
71.25, 38.25).Select
boxText = "Median Time-To-Productivity: " & MedianTTP & " days"
lenBoxText = Len(boxText)
Selection.Characters.Text = boxText
Selection.AutoScaleFont = False
With Selection.Characters(Start:=1, Length:=lenBoxText).Font
.Name = "GillSans"
.FontStyle = "Regular"
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.ReadingOrder = xlContext
.Orientation = xlHorizontal
.AutoSize = False
End With
Selection.ShapeRange.Fill.Visible = msoTrue
Selection.ShapeRange.Fill.Solid
Selection.ShapeRange.Fill.ForeColor.SchemeColor = 41
Selection.ShapeRange.Fill.Transparency = 0#
Selection.ShapeRange.Line.Weight = 0.75
Selection.ShapeRange.Line.DashStyle = msoLineSolid
Selection.ShapeRange.Line.Style = msoLineSingle
Selection.ShapeRange.Line.Transparency = 0#
Selection.ShapeRange.Line.Visible = msoTrue
Selection.ShapeRange.Line.ForeColor.SchemeColor = 64
Selection.ShapeRange.Line.BackColor.RGB = RGB(255, 255, 255)
wkgCht.Shapes.AddLine(137.5, 21.21, 344.2, 21.21).Select

Call ExportChart(wkgSht.Name & ".gif")

End Sub

--
Function ExportChart(ExportName As String)

With wkgCht
.Export Filename:=outputFileLoc & ExportName, FilterName:="GIF"

End With

End Function

 

[AGlazer]

Here's an update for you -- the charts export properly in Excel 2003 but not in Excel 2002. Odd, I know, and I can't just use Excel 2003, because the client I'm writing this for only has Excel 2002 on their local machines.