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

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 Cha...

excel

Macros and VBA

HansVogelaar

Jan 09, 2023

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

HansVogelaar

MVP

Jan 09, 2023

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

BDCanuck

Copper Contributor

Jan 09, 2023

You're a wizard! Thanks!!

Forum Discussion

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

Resources