SOLVED

Modify VBA code to work on whole workbook instead of just active sheet

Occasional Contributor

Hello all!

 

I found this code online that will let me do a find and replace on text that is in a chart title.

 

How can I run it on the whole workbook, and not just the active sheet?

 

Sub ChartLabelReplace()
'Update 20140603
Dim xWs As Worksheet
Dim xFindStr As String
Dim xReplace As String
xFindStr = Application.InputBox("Find:", xTitleId, "", Type:=2)
xReplace = Application.InputBox("Replace:", xTitleId, "", Type:=2)
Set xWs = Application.ActiveSheet
For Each ch In xWs.ChartObjects
    If ch.Chart.HasTitle Then
        ch.Chart.ChartTitle.Text = VBA.Replace(ch.Chart.ChartTitle.Text, xFindStr, xReplace, 1)
    End If
Next
End Sub

 

2 Replies
best response confirmed by BDCanuck (Occasional Contributor)
Solution

@BDCanuck 

Try this version:

Sub ChartLabelReplace()
    'Updated by Microsoft Tech Community
    Dim xWs As Worksheet
    Dim xFindStr As String
    Dim xReplace As String
    Dim xCht As ChartObject
    xFindStr = InputBox(Prompt:="Find:")
    xReplace = InputBox(Prompt:="Replace:")
    For Each xWs In Worksheets
        For Each xCht In xWs.ChartObjects
            If xCht.Chart.HasTitle Then
                xCht.Chart.ChartTitle.Text = Replace(xCht.Chart.ChartTitle.Text, xFindStr, xReplace)
            End If
        Next xCht
    Next xWs
End Sub