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

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

CodePudding user response：

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

CodePudding user response：

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

CodePudding user response：

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

CodePudding user response：

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

CodePudding user response：

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