Removing Invalid characters in a sheet name

schang_825

Board Regular
Joined
Feb 19, 2010
Messages
66
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!!
 

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,400
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.

Code:
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
Code:
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

Board Regular
Joined
Feb 19, 2010
Messages
66
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

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,400
Code:
    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

Board Regular
Joined
Feb 19, 2010
Messages
66

ADVERTISEMENT

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

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,400
Code:
    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
 
Last edited:

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,400
Sorry. Ignore the last post. It should be this....
Code:
    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
 

Watch MrExcel Video

Forum statistics

Threads
1,127,205
Messages
5,623,364
Members
415,969
Latest member
Rey99

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top