Removing Invalid characters in a sheet name

[schang_825]

Hi all,

I have a button in my excel workbook that prompts an input box and creates a new sheet with the text entered. If the user does not enter any text, a message box will appear that tells them they must enter a sheet name. If that name already exists, it tells the user this also.

What I can't seem to do is have a message box appear if the user enters an invalid character such as ? * [ ]. I have this code that only seems to work sometimes:

Dim vaIllegal As Variant
vaIllegal = Array(".", "?", "!", "*", "/", "", "[", "]", "'", ":")

If shtname = vaIllegal Then
InvalidName
Exit Sub
End If

Sub InvalidName()
MsgBox "This is an invalid sheet name! The text cannot contain . ? ! * / [ ] ' or :"
End Sub

Please help!!

 

[AlphaFrog]

There are a couple of ways you could do this.

You could use the Replace function to strip any invalid characters. Then you wouldn't have to notify the user to re-input a valid name e.g.

shtname  = Replace(Replace(Replace(Replace(Replace(Replace(Replace(shtname, ":", ""), "/", ""), "\", ""), "?", ""), "*", ""), "[", ""), "]", "")

You could create a new sheet and try to name it using the user input and trap the error if the name causes an error

Sub NewSheet()
    Dim wsNew As Worksheet, shtname  As String
    
    shtname = Application.InputBox("Enter sheet name", "Sheet Name...", Type:=2)
    If shtname = "False" Then Exit Sub 'User canceled
    
    Set wsNew = Worksheets.Add(After:=Sheets(Sheets.Count))
    [COLOR="red"]On Error Resume Next[/COLOR]
        wsNew.Name = shtname
    [COLOR="red"]On Error GoTo 0[/COLOR]
    
    [COLOR="red"]If wsNew.Name <> shtname Then[/COLOR]
        Application.DisplayAlerts = False
        wsNew.Delete
        Application.DisplayAlerts = True
        MsgBox "This is an invalid sheet name! The text cannot contain . ? ! * / [ ] ' or :"
    End If
        
End Sub

 

[schang_825]

Thanks so much for your help! I have another question, if you could help which might conflict with your second option, which I'm currently using.

The name of the sheets refer to sheet names in construction drawings (ie. E1.01 Electrical Demo Plan, etc.) which are sometimes longer than the 31 characters allowed. How can I make it so the tab name only shows the first word (ie. E1.01) and the tile of the sheet (A1) remains the same?

Thanks so much!

 

[AlphaFrog]

    On Error Resume Next
        wsNew.Range("A1") = shtname
        wsNew.Name = Trim(Left(shtname, InStr(shtname, " ")))
    On Error GoTo 0
    
    If wsNew.Name <> Trim(Left(shtname, InStr(shtname, " "))) Then

 

[schang_825]

thanks!

This works, but not if the inputbox text is only one word....Then the sheet name becomes "Master (2)" Any way to get around this???

 

[AlphaFrog]

    On Error Resume Next
        wsNew.Range("A1") = shtname
        wsNew.Name = Trim(Left(shtname & " ", InStr(shtname, " ")))
    On Error GoTo 0
    
    If wsNew.Name <> Trim(Left(shtname & " ", InStr(shtname, " "))) Then

 

[AlphaFrog]

Sorry. Ignore the last post. It should be this....

    On Error Resume Next
        wsNew.Range("A1") = shtname
        wsNew.Name = Trim(Left(shtname, InStr(shtname & " ", " ")))
    On Error GoTo 0
    
    If wsNew.Name <> Trim(Left(shtname, InStr(shtname & " ", " "))) Then

 

[schang_825]

Thanks works perfectly!!