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

Question

Hello all!&nbsp;I found this code online that will let me do a find and replace on text that is in a chart title.&nbsp;How can I run it on the whole workbook, and not just the active sheet?&nbsp;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&nbsp;

hansvogelaar · Accepted Answer

BDCanuck&nbsp;
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

Forum Discussion

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

2 Replies

Resources