How to add a sheet with non-duplicate name consisting of valid characters?

Question

My code is as below:

Sub NewWorksheetTest()
    Dim wsname As String
    wsname = InputBox("Enter a name for the new worksheet")

    On Error GoTo BadEntry
    Sheets.Add
    ActiveSheet.Name = wsname
    Exit Sub

BadEntry:
    MsgBox Err.Number & " :" & Err.Description, vbInformation, "There is an error...."

End Sub

My understanding is if I input a bad name (e.g. duplicate or containing ?/), there is a message explaining the reasons and at the same time the system stops a new sheet from being added.

An error msg is there but a new sheet is added.

score 1 · Answer 1 · answered Mar 08 '20 at 00:39

1

If the rename fails then you need to remove the added sheet

Sub NewWorksheetTest()
    Dim wsname As String, ws As Worksheet
    wsname = InputBox("Enter a name for the new worksheet")

    On Error GoTo BadEntry
        Set ws = Sheets.Add()
        ws.Name = wsname
        Exit Sub

BadEntry:
    MsgBox Err.Number & " :" & Err.Description, vbInformation, "There is an error...."
    If Not ws Is Nothing Then
        Application.DisplayAlerts = False
        ws.Delete
        Application.DisplayAlerts = True
    End If

End Sub

answered Mar 08 '20 at 00:39

Tim Williams

154,628
8
97
125

My question is after On error Goto BadEntry. The systems goes to BadEntry label and display the msg. After the msg why the system goes up again to execute the “adding sheets”??? Shouldn’t it be ending sub straight away after the msg??? – Ms Chen Mar 08 '20 at 03:18
1

No error occurs until you try to rename the new sheet with an entry which is invalid as a sheet name. At that point the sheet has already been added, so there is no "going back" involved here. Execution doesn't "hold back" instructions in case a later one raises an error - they are executed in turn as they occur in the program. – Tim Williams Mar 08 '20 at 03:53

Dang D. Khanh · Accepted Answer · 2020-03-08T12:39:16.487

As Tim Williams said, On Error GoTo BadEntry only works when the error appears, and sheets.add has no error so it will run normally. This is another version you can use

vs1-no error checking

Option Compare Text
Sub NewWorksheetTest()
    Dim wsname As String
    wsname = InputBox("Enter a name for the new worksheet")
    If Not (Checks_Sheetname (wsname)) Then Exit Sub 'check correct name
    If Check_SheetExists(wsname) Then Exit Sub           'check dulicate

    Sheets.Add
    ActiveSheet.Name = wsname
End Sub

'https://learn.microsoft.com/en-us/office/vba/excel/concepts/workbooks-and-worksheets/name-a-worksheet-by-using-a-cell-value

Private Function Checks_Sheetname (wsname As String) As Boolean
    If Len(wsname) > 31 Then Checks_Sheetname = False:exit function 'check sheetname length
Dim lst_str As Variant, item As Variant
    lst_str = Array("/", "\", "[", "]", "*", "?", ":")
    For Each item In lst_str
        If InStr(wsname, item) > 0 Then
            '...
            Checks_Sheetname = False: Exit Function
        End If
    Next item
Checks_Sheetname = True
End Function

'https://stackoverflow.com/questions/6688131/test-or-check-if-sheet-exists

Private Function Check_SheetExists(wsname As String) As Boolean
    For Each ws In Worksheets
      If wsname = ws.Name Then
      MsgBox ("exist")
        Check_SheetExists = True
        Exit Function
      End If
    Next ws
End Function

vs2: error checking

Sub NewWorksheetTest()
 Dim wsname As String
     wsname = InputBox("Enter a name for the new worksheet")
 On Error GoTo BadEntry
 Dim Act_wsname As String: Act_wsname = ActiveSheet.Name
     ActiveSheet.Name = wsname: ActiveSheet.Name = Act_wsname   'checksyntax
 Dim ws As Worksheet: Set ws = Sheets(wsname)                   'check dulicate
     If Not (ws Is Nothing) Then Exit Sub
     Sheets.Add
     ActiveSheet.Name = wsname
     Exit Sub
BadEntry:
    MsgBox Err.Number & " :" & Err.Description, vbInformation, "There is an error...."
End Sub

How to add a sheet with non-duplicate name consisting of valid characters?

2 Answers2