谢谢大家的回答。
最后我只是删除了错误处理代码并添加了一个额外的If语句。

Sub ChangeSheetName()
    
    Dim Carryon As String
    
    Carryon = MsgBox("Change Worksheet Name?", vbYesNo)
    
    If Carryon = vbYes Then
        Dim shName As String
        Dim currentName As String
    
        currentName = ActiveSheet.Name
      
        shName = InputBox("Type new Worksheet name")
        If shName <> "" Then
            ThisWorkbook.Sheets(currentName).Name = shName
        End If
    End If
    
End Sub

您已经将Carryon声明为字符串变量- vbYes（和其他消息框结果）是数值常量。

如果用户按“取消”，则InputBox-Function返回空字符串（“”）。如果尝试使用该空字符串作为工作表名称，则会出现运行时错误（因为这不是有效的工作表名称），并触发错误处理程序。
要避免这种情况，只需在指定名称之前检查shName是否不是空字符串。

If MsgBox("Change Worksheet Name?", vbYesNo) <> vbYes Then Exit Sub

Dim currentSheet As Worksheet, shName As String
Set currentSheet = ActiveSheet
shName = InputBox("Type new Worksheet name")
If shName <> "" Then
    currentSheet.Name = shName
End If

可以使用StrPtr来处理InputBox。这是一个未公开的函数，用于获取变量的底层内存地址。
下面是一个例子

shName = InputBox("Type new Worksheet name")

If (StrPtr(shName) = 0) Or (shName = "") Or Len(Trim(shName)) = 0 Then
    '~~> StrPtr(shName) = 0 : User Pressed Cancel, or the X button
    '~~> shName = "" : User tried to pass a blank value
    '~~> Len(Trim(shName)) = 0 : User tried to pass space(s)
    
    Exit Sub ' Or do what you want
Else
    MsgBox "Worksheet Name: " & shName
End If

请尝试下一种方法：

Sub MsgBoxYesNoHandling()
   Dim Carryon As VbMsgBoxResult, shName As String
   
   Carryon = MsgBox("Change Worksheet Name?", vbYesNo)
   If Not Carryon = vbYes Then Exit Sub
   
   shName = InputBox("Type new Worksheet name")
   If Len(Trim(shName)) = 0 Then Exit Sub
   
   'do here whatever you need..
End Sub

重命名工作表

• 此操作将重命名所有活动工作表（工作表或图表），而不仅仅是包含此代码的工作簿中的活动工作表（ThisWorkbook）。在退出之前，仅当操作成功时才会显示消息框。

Sub RenameSheet()
    Const PROC_TITLE As String = "Rename Sheet"
    On Error GoTo ClearError ' start main error-handling routine
    
    Dim sh As Object: Set sh = ActiveSheet
    If sh Is Nothing Then
        MsgBox "No visible workbooks open.", vbExclamation, PROC_TITLE
        Exit Sub
    End If
    
    Dim OldName As String: OldName = sh.Name
    
    Dim NewName As String, MsgNumber As Long
    
    Do
        NewName = InputBox("Input the new sheet name:", PROC_TITLE, OldName)
        If Len(NewName) = 0 Then Exit Sub
        
        On Error GoTo RenameError ' start Rename error-handling routine
            sh.Name = NewName
        On Error GoTo ClearError ' restart main error-handling routine
        
        Select Case MsgNumber
            Case 0, vbNo: Exit Do
            Case vbYes: MsgNumber = 0 ' reset for the next iteration
        End Select
    Loop
            
    If MsgNumber = 0 Then
        If StrComp(OldName, NewName, vbBinaryCompare) = 0 Then Exit Sub
        MsgBox "Sheet renamed from '" & OldName & "' to '" & NewName & "'.", _
            vbInformation, PROC_TITLE
    End If
    
ProcExit:
    Exit Sub
RenameError: ' continue Rename error-handling routine
    MsgNumber = MsgBox("Could not rename from '" & OldName & "' to '" _
        & NewName & "'. Try again?" & vbLf & vbLf & "Run-time error '" _
        & Err.Number & "':" & vbLf & vbLf & Err.Description, _
        vbYesNo + vbQuestion, PROC_TITLE)
    Resume Next
ClearError: ' continue main error-handling routine
    MsgBox "An unexpected error occurred." _
        & vbLf & vbLf & "Run-time error '" & Err.Number & "':" & vbLf & vbLf _
        & Err.Description, vbCritical, PROC_TITLE
    Resume ProcExit
End Sub