adding a string of text to a textbox

[dinotom]

Good Morning

I have a textbox on a chart which acts as a legend for the chart. Upon updating the data, the legend needs to be updated as well.

I have all the code to access the text box. The problem I'm having is replacing the text in the text box with the new string of text.

Sub AddTextBoxtoChart()
Dim objChart As Chart, strTemp As String, cCell As Range
Dim objTextBox As Shape, strTBName As String

With Sheets("P&LChart")
    Set objChart = ActiveChart
End With
strTemp = ""
With objChart
' add the code to delete the existing text box here or just replace the text?
   Set objTextBox = .Shapes.AddTextbox(msoTextOrientationHorizontal, 79.5, 43.5, 141.5, 57.8)
    objTextBox.Name = "PLChartLegend"
    For Each cCell In Sheets("ChartData").Range("$X$2:$X$11")
        strTemp = strTemp & " " & cCell.Value & "   " & Round((cCell.Offset(0, 1).Value * 100), 1) & " %" & vbCrLf
        'Debug.Print strTemp
    Next cCell
     [B]objTextBox.ShapeRange(1).TextFrame2.TextRange.Characters.Text = strTemp[/B]
    'Debug.Print strTemp
 End With
End Sub

The bolded line above is where it fails. That line was recorded from a macro where i added some text to a textbox so I'm not sure why it isn't working. My guess is that line might only enable 1 character?

 

[dinotom]

Well I found the answer. That line needs to be

objTextBox.DrawingObject.Text = strTemp

Now, on to how to format the font size, etc etc

 

[dinotom]

Below is all the working code to change the text in a text box on a chart OR if the text box isn't there, to add it.
You can substitute your own range name(where I get the text for the text box), the addText box parameters for location, the sheet names, shapenames etc etc.

Sub AddTextBoxtoChart()
'Note: GetTextbox function is required for this sub routine to work
Dim objChart As Chart, strTemp As String, cCell As Range
Dim objTextBox As Shape

With Sheets("P&LChart")
    Set objChart = ActiveChart
End With

strTemp = ""
With objChart
    If Not GetTextbox("P&LChart", "PLChartLegend") Is Nothing Then
       Set objTextBox = GetTextbox("P&LChart", "PLChartLegend")
    Else
       Set objTextBox = .Shapes.AddTextbox(msoTextOrientationHorizontal, 79.5, 45, 141.5, 109)
       objTextBox.Name = "PLChartLegend"
    End If
  
    With objTextBox
        For Each cCell In Range("LegendTable")
            strTemp = strTemp & " " & cCell.Value & "   " & Round((cCell.Offset(0, 1).Value * 100), 1) & " %" & vbCrLf
        Next cCell
        .DrawingObject.Text = strTemp
        .TextFrame2.TextRange.Font.Bold = msoTrue
        .TextFrame2.TextRange.Font.Size = 8
    End With  'objTextBox
 End With ' objChart
End Sub

Function GetTextbox(wsName As String, strShapeName As String) As Shape
Dim objShape As Shape
With Sheets(wsName)
    If Not .Shapes.Count = 0 Then
        For Each objShape In .Shapes
            If objShape.Name = strShapeName Then
               Set GetTextbox = objShape
            End If
        Next
    Else
    MsgBox "There are no text boxes on this chart!"
    End If
End With
End Function