Require a cancel option for code that copies a worksheet and renames it using Application.inputbox.

Question

I have the following code that copies a Master worksheet and renames it using Application.Inputbox.

'Generates input box to name the new Sheet and checks duplicate names

Set wks = ActiveSheet
    Do While sName <> wks.Name
        sName = Application.InputBox _
          (Prompt:="Enter New Year")
        On Error Resume Next
        wks.Name = sName
        On Error GoTo 0
    Loop
    Set wks = Nothing

This works fine except when the user clicks cancel. Current out comes are;

User inputs something and clicks 'ok' = master sheet copied and renamed to input value.

User inputs nothing and clicks 'ok' = Input box loops until value entered and 'ok' clicked or cancel clicked.

User clicks 'cancel' = master sheet copied and renamed to 'False'.

Desired Out come for user clicks 'cancel' = sub exited and nothing copied or altered.

Any help?

Answer 1

I would change your code in the following way.

Dim sname As Variant
Dim wks As Worksheet

    Set wks = ActiveSheet
    Do While sname <> wks.Name
        sname = Application.InputBox _
                (Prompt:="Enter New Year")
        If sname = vbFalse Then
            MsgBox "You pressed Cancel"
        Else
            On Error Resume Next
            wks.Name = sname
            On Error GoTo 0
        End If
    Loop
    Set wks = Nothing

In case user presses Cancel sname becomes a boolean with value False

Answer 2

You can detect a cancel by looking for an vbNullString (empty) string

    Set wks = ActiveSheet
        Do While sName <> wks.Name
            sName = Application.InputBox _
              (Prompt:="Enter New Year")
        If sName = vbNullString Then
                MsgBox ("Cancel button clicked!")
Exit Sub
            End If
            On Error Resume Next
            wks.Name = sName
            On Error GoTo 0
        Loop
        Set wks = Nothing

Answer 3

Using above help added this to my code;

'Generates input box to name the new Sheet and checks duplicate names


    Set wks = ActiveSheet
    Do While sname <> wks.Name
        sname = Application.InputBox _
                (Prompt:="Enter New Year")
        If sname = vbFalse Then
            MsgBox "You pressed Cancel"
            Application.DisplayAlerts = False
            Sheets("MASTER (2)").Delete
            Application.DisplayAlerts = True
            Sheets("MASTER").Visible = False

Exit Sub

        Else
            On Error Resume Next
            wks.Name = sname
            On Error GoTo 0
        End If
    Loop
    Set wks = Nothing

The help above allowed the code to accept a valid input, detect a duplicate input, check for no input and loop as needed, and if the 'cancel' button was clicked then it informed the user. I added further code to exit the sub after deleting the unwanted worksheet (copied from a master sheet earlier in the code).

Thank you all for your help.