I am automating a spreadsheet, designed by someone else, which has 18 graphs on a worksheet. Most of these graphs have a text box on them which indicates the week commencing that the graph refers to. The text box appears to be a shape object on the chart.
I want to loop through each chart on the sheet, then loop through each shape on the chart, and change the text if the shape is a text box.
Can anyone put me on the right track, I did try using the macro recorder to get me started but this code does not work in my VBA module.
Updating text in a text box on an Excel Graph
madsurfman
Then this should do it:
Option Explicit
Private Sub Workbook_Open()
Dim ws As Worksheet, o As ChartObject, c As Chart, s As Shape
Set ws = Me.Worksheets("Sheet1")
For Each o In ws.ChartObjects
Set c = o.Chart
For Each s In c.Shapes
If InStr(1, s.Name, "Text Box") Then
s.TextFrame.Characters.Text = "TEST"
End If
Next s
Next o
End Sub
jcarlos.net
Hi
Thanks for the post, the text I want to update is a text box which the user has placed on the charts. It appears to be a shape object. The code that I had from macro recorder after the shape was selected on the graph was something like
Selection.characters.text="Week Comm"
But put into my module this code just failed.
canadian_coder
If the text you want to update is the title of the graph, it's pretty simple... here's a quick piece of code that does it:
Option Explicit
Public Sub main()
Dim o As ChartObject
For Each o In Sheet1.ChartObjects
o.Chart.ChartTitle.Text = "TEST"
Next o
End Sub
Mike Strobel