咱们先来拆解下你代码里的几个核心问题：

• 循环对象错误：你写了For Each mySrs In ActiveChart.ChartObjects，但ActiveChart是单个激活的图表对象，它并没有ChartObjects集合——这个集合是工作表用来存放嵌入式图表的容器，所以这行代码会直接触发运行错误。
• 判断条件不合理：Len(OldString) > 1会把用户输入单个字符的替换需求直接过滤掉，改成Len(OldString) > 0才符合逻辑。
• 缺少标题存在性检查：如果目标图表没设置标题，代码访问ChartTitle.Text时会报错，得先判断标题是否存在。
• 冗余的函数调用：VBA本身自带Replace函数，比调用WorksheetFunction.Substitute更简洁高效。

下面是修正后的代码，还加了不少容错处理，让它更健壮：

Sub ChangeChartTitleText()
    ' 检查是否有激活的图表
    If ActiveChart Is Nothing Then
        MsgBox "Please select a chart and try again.", vbExclamation, "No Chart Selected"
        Exit Sub
    End If
    
    Dim OldString As String, NewString As String, updatedTitle As String
    OldString = InputBox("Enter the string to be replaced:", "Enter old string")
    
    ' 检查用户是否输入了有效内容
    If Len(Trim(OldString)) = 0 Then
        MsgBox "Nothing to be replaced.", vbInformation, "Nothing Entered"
        Exit Sub
    End If
    
    NewString = InputBox("Enter the string to replace """ & OldString & """:", "Enter new string")
    
    ' 检查图表是否有标题可修改
    If ActiveChart.HasTitle Then
        ' 使用VBA内置Replace函数替换文本
        updatedTitle = Replace(ActiveChart.ChartTitle.Text, OldString, NewString)
        ActiveChart.ChartTitle.Text = updatedTitle
        MsgBox "Chart title updated successfully!", vbInformation, "Success"
    Else
        MsgBox "The selected chart has no title to modify.", vbExclamation, "No Chart Title"
    End If
End Sub

如果你的需求是批量处理工作表中选中的多个图表，可以用下面这段代码，它会处理你按住Ctrl选中的所有图表：

Sub ChangeSelectedChartTitles()
    Dim selectedObj As Object
    Dim OldString As String, NewString As String, updatedTitle As String
    
    ' 检查是否选中了图表对象
    On Error Resume Next
    Set selectedObj = Selection
    On Error GoTo 0
    
    If selectedObj Is Nothing Or Not TypeName(selectedObj) = "ChartObjects" Then
        MsgBox "Please select one or more charts and try again.", vbExclamation, "No Charts Selected"
        Exit Sub
    End If
    
    OldString = InputBox("Enter the string to be replaced:", "Enter old string")
    If Len(Trim(OldString)) = 0 Then
        MsgBox "Nothing to be replaced.", vbInformation, "Nothing Entered"
        Exit Sub
    End If
    
    NewString = InputBox("Enter the string to replace """ & OldString & """:", "Enter new string")
    
    ' 遍历所有选中的图表
    Dim chartObj As ChartObject
    For Each chartObj In selectedObj
        If chartObj.Chart.HasTitle Then
            updatedTitle = Replace(chartObj.Chart.ChartTitle.Text, OldString, NewString)
            chartObj.Chart.ChartTitle.Text = updatedTitle
        End If
    Next chartObj
    
    MsgBox "Selected chart titles updated successfully!", vbInformation, "Success"
End Sub

使用小提示： #

• 单个图表：点击图表激活它，运行第一个宏，按提示输入替换内容即可。
• 多个图表：按住Ctrl选中工作表上的多个图表，运行第二个宏就能批量修改标题。

内容的提问来源于stack exchange，提问作者Mennatullah Hesham Noaman