CODEWITHSUNDEEP
excelvba
Horby
Horby

Reputation: 45

Make new with copy sheet

This code to create a new sheet of sheet copied.

Sub MakeNewSheet()

    Dim ShtName$

    Sheets("New").Copy After:=Sheets(Sheets.Count)

    ShtName = InputBox("Enter Sheet Name")

    Sheets(Sheets.Count).Name = ShtName

    Sheets(ShtName).Visible = True

End Sub

When you see the message sheet name, if you enter the name is copying without any problems. But if click to cancel or is blank Name, this message come.

exampel

How do I remove this message? show any other message like alert!

Upvotes: 0

Views: 125

Answers (1)

user4039065
user4039065

Reputation:

The process you are following is generating the error. The code creates a new copy of the New worksheet and then asks for a name. If no name is supplied or Cancel is clicked, the ShtName variable will be blank but the process still attempts to rename the newly created worksheet to a blank Worksheet .Name property and this is where the error is occurring. You can either keep the newly copied worksheet with the name automatically generated during the copy process or not create the worksheet at all unless a name is provided.

Option 1 - Only create the worksheet if a name is provided.

Sub MakeNewSheet1()

    Dim ShtName$

    On Error GoTo bm_Err_Out
    ShtName = InputBox("Enter Sheet Name")

    If CBool(Len(ShtName)) Then
        Sheets("New").Copy After:=Sheets(Sheets.Count)
        Sheets(Sheets.Count).Name = ShtName
        Sheets(ShtName).Visible = True
    End If

    Exit Sub
bm_Err_Out:
    MsgBox Err.Number & ": " & Err.Description, vbCritical
    Application.DisplayAlerts = False
    'Sheets(Sheets.Count).Delete
    Application.DisplayAlerts = True
End Sub

Option 2 - Create the worksheet but only rename it if a name is provided.

Sub MakeNewSheet()
    Dim ShtName$

    On Error GoTo bm_Err_Out
    ShtName = InputBox("Enter Sheet Name")

    Sheets("New").Copy After:=Sheets(Sheets.Count)
    If CBool(Len(ShtName)) Then
        Sheets(Sheets.Count).Name = ShtName
    End If
    Sheets(Sheets.Count).Visible = True

    Exit Sub
bm_Err_Out:
    MsgBox Err.Number & ": " & Err.Description, vbCritical
    Application.DisplayAlerts = False
    'Sheets(Sheets.Count).Delete
    Application.DisplayAlerts = True
End Sub

Upvotes: 1

Related Questions