Loop over Error in the InputBox for sheet creation or copied sheet

Cooper 0
Viewed 33

I have a code to copy and name worksheets, this code warns me when I have a worksheet with the same name and request another name, but I can make it request this only once before copying a worksheet, I would like it I got the name wrong again he would ask until the name was accepted before copying the worksheet. But I'm not able to do the loop, and whenever he asks the second time, he already copies the worksheet even without having typed the name yet.

Sub CopySheet()


    Dim i As Integer, x As Integer
    Dim shtname As String
    
    shtname = InputBox("Worksheet Name?", "Name?")
    If StrPtr(shtname) = 0 Then
        Exit Sub
    Else
    On Error GoTo errhandler
    End If
errhandler:
    shtname = InputBox("Name taken!", "Name?")
    If StrPtr(shtname) = 0 Then
        Exit Sub
    Else
    On Error GoTo errhandler
    End If
    
    Sheets("MODELO").Copy Before:=Sheets("CC")
    ActiveSheet.Name = shtname

    
End Sub
vba excel
1 Answers

Explanation
There are a couple of things to be addressed aside from checking if the sheet exists, like length of the name given and if special characters are written as well (some may be accepted but some others not). I appended those, you were right on getting labels, just a quick change on how to do it
Demo Solution enter image description here

Code

Sub CopySheet()
Const SpecialCharacters As String = " ||!||@||#||$||%||^||&||*||,||(||)||{||[||]||}||?||¿||¡||.||;||:||/||\||=||-||_||'||<||>||+||»||ï|||"
Dim Varchar As Variant
    Dim i As Integer, x As Integer
    Dim shtname As String
    Dim WSDummy As Worksheet
    Dim TxtError As String
Loop01ValidName:
    TxtError = ""
    shtname = InputBox("Worksheet Name?", "Name?")
    If StrPtr(shtname) = 0 Then Exit Sub
    For Each Varchar In Split(SpecialCharacters, "||"): If InStr(shtname, Varchar) > 0 Then TxtError = IIf(TxtError = "", "No special characters allowed!", TxtError & Chr(10) & "No special characters allowed!"): Exit For
    Next Varchar
    If Len(shtname) > 31 Then TxtError = IIf(TxtError = "", "No special characters allowed!", TxtError & Chr(10) & "Maximum characters for sheet are 31!")
    On Error Resume Next
    Set WSDummy = Sheets(shtname)
    If Not (WSDummy Is Nothing) Then TxtError = IIf(TxtError = "", "Name taken!", TxtError & Chr(10) & "Maximum characters for sheet are 31!")
    If TxtError <> "" Then MsgBox "Error(s)" & TxtError: GoTo Loop01ValidName
    'Be aware if sheets are very hidden will result in error, just be aware of that
    'Sheets("MODELO").Visible= False:Sheets("CC").Visible= False
    Sheets("MODELO").Copy Before:=Sheets("CC"): ActiveSheet.Name = shtname
End Sub
Sgdva0
Related