Using MsgBox and get error msg 1004 when I select "Cancel" - Need the macro to just end normally

Question

First I selected "Yes" to the question "Change Worksheet Name?". Then the message "Type new Worksheet Name" appears. Instead of typing in a new name and selecting "OK", I select the "cancel" button and my error messages are displayed. How do I avoid seeing the error messages and just let the macro end "quietly"?

Option Explicit ' Force explicit variable declaration.


Sub ChangeSheetName()

Dim Carryon As String

On Error GoTo eh

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")
    ThisWorkbook.Sheets(currentName).Name = shName
End If
Exit Sub

eh:
    MsgBox "The following error occured." _
        & vbCrLf & "" _
        & vbCrLf & "Error Number is: " & Err.Number _
        & vbCrLf & "" _
        & vbCrLf & "Error Description is: " & Err.Description _
        & vbCrLf & "" _
        & vbCrLf & "You likely hit the Esc key to stop renaming the Worksheet." _
        & vbCrLf & "" _
        & vbCrLf & "No worries.  You can try again to rename or leave it as is." _
        & vbCrLf & "" _
        & vbCrLf & "No harm done."

End Sub

Answer 1

You've declared Carryon as a string variable - vbYes (and other messagebox results) are numeric constants. Change Dim Carryon As String to Dim Carryon As Long

Answer 2

If the user presses "Cancel", the InputBox-Function returns an empty string (""). If you try to use that empty string as a worksheet name, you will get an runtime error (as this is not a valid sheet name) and your error handler is triggered.

To avoid this, simply check if shName is not the empty string before assigning the name.

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

Answer 3

You can use StrPtr to handle InputBoxes. This is an undocumented function that is used to get the underlying memory address of variable.

Here is an example

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

Answer 4

Please, try the next way:

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

Answer 5

Rename Sheet

• This will rename any active sheet (worksheet or chart), not just if it's the active sheet in the workbook containing this code (ThisWorkbook). Before exiting, it will show a message box only if it was successful.

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

Answer 6

Thank you all for your answers.

I ended up just removing the error handling code and adding an extra If statement.

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