Check for invalid Worksheet name

T

tlawson1

Board Regular
Joined
Jun 15, 2003
Messages
69
Hi All

I am using an InputBox to collect a name from the user to create a new Worksheet.

I know the Name "History" or certain characters like ": / \ * ? [ ]" are not allowed.

Does anyone have a small VBA rountine that will loop through the InputBox answer and check for the presence of these characters?

In advance Many thanks
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
Sort by date Sort by votes
Code: 
Sub TestSheetname()

Dim mySheetName$
mySheetName = InputBox("Enter proposed sheet name:", "Sheet name")
If mySheetName = "" Then
MsgBox "You did not enter anything or you hit Cancel.", 64, "No sheet name was entered."
Exit Sub
End If
 
'If the length of the entry is greater than 31 characters, disallow the entry.
If Len(mySheetName) > 31 Then
MsgBox "Worksheet tab names cannot be greater than 31 characters in length." & vbCrLf & _
"You entered " & mySheetName & ", which has " & Len(mySheetName) & " characters.", , "Keep it under 31 characters"
Exit Sub
End If
 
'Sheet tab names cannot contain the characters /, \, [, ], *, ?, or :.
'Verify that none of these characters are present in the cell's entry.
Dim IllegalCharacter(1 To 7) As String, i As Integer
IllegalCharacter(1) = "/"
IllegalCharacter(2) = "\"
IllegalCharacter(3) = "["
IllegalCharacter(4) = "]"
IllegalCharacter(5) = "*"
IllegalCharacter(6) = "?"
IllegalCharacter(7) = ":"

For i = 1 To 7
If InStr(mySheetName, (IllegalCharacter(i))) > 0 Then
MsgBox "You used a character that violates sheet naming rules." & vbCrLf & vbCrLf & _
"Please re-enter a sheet name without the ''" & IllegalCharacter(i) & "'' character.", 48, "Not a possible sheet name !!"
Exit Sub
End If
Next i
 
'Verify that the proposed sheet name does not already exist in the workbook.
Dim strSheetName As String, wks As Worksheet, bln As Boolean
strSheetName = Trim(mySheetName)
On Error Resume Next
Set wks = ActiveWorkbook.Worksheets(strSheetName)
On Error Resume Next
If Not wks Is Nothing Then
bln = True
Else
bln = False
Err.Clear
End If

'History is a reserved word, so a sheet cannot be named History.
If UCase(mySheetName) = "HISTORY" Then
MsgBox "A sheet cannot be named History, which is a reserved word.", 48, "Not allowed"
Exit Sub
End If

'If the worksheet name does not already exist, name the active sheet as the InputBox entry.
'Otherwise, advise the user that duplicate sheet names are not allowed.
If bln = False Then
Worksheets.Add.Name = strSheetName
MsgBox "A new sheet named ''" & mySheetName & "'' has been added.", 64, "Done"
Else
MsgBox "There is already a sheet named " & strSheetName & "." & vbCrLf & _
"Please enter a unique name for this sheet.", 16, "Duplicate sheet names not allowed."
End If

End Sub
 
Upvote 0
Or :-
Returns Msg if Non Alphanumeric Characters found
Code: 
[COLOR="Navy"]Sub[/COLOR] MG23Mar22
[COLOR="Navy"]Dim[/COLOR] n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Txt [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
Txt = Application.InputBox(prompt:="Please [COLOR="Navy"]Select[/COLOR] ", Title:="Insert Brackets", Type:=2)
[COLOR="Navy"]If[/COLOR] Txt = "" Or Txt = "False" [COLOR="Navy"]Then[/COLOR] [COLOR="Navy"]Exit[/COLOR] [COLOR="Navy"]Sub[/COLOR]
    [COLOR="Navy"]For[/COLOR] n = 1 To Len(Txt)
        [COLOR="Navy"]If[/COLOR] Not Mid(Txt, n, 1) Like "[^a-zA-Z0-9]" [COLOR="Navy"]Then[/COLOR]
                MsgBox "Non Acceptable Character found"
                [COLOR="Navy"]Exit[/COLOR] For
        [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] n
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 
Upvote 0
Thanks Tom you answered the exact question that I asked and I have chosen to use your code.

Thanks for your input as well Mick. I will take a note of your little routine as I can see myself being in a situation where I may need to limit the input to just letters. This may have saved me a future Post.

Hope this also helps others
 
Upvote 0
Tom Urtis said:
Code: 
Sub TestSheetname()

Dim mySheetName$
mySheetName = InputBox("Enter proposed sheet name:", "Sheet name")
If mySheetName = "" Then
MsgBox "You did not enter anything or you hit Cancel.", 64, "No sheet name was entered."
Exit Sub
End If
 
'If the length of the entry is greater than 31 characters, disallow the entry.
If Len(mySheetName) > 31 Then
MsgBox "Worksheet tab names cannot be greater than 31 characters in length." & vbCrLf & _
"You entered " & mySheetName & ", which has " & Len(mySheetName) & " characters.", , "Keep it under 31 characters"
Exit Sub
End If
 
'Sheet tab names cannot contain the characters /, \, [, ], *, ?, or :.
'Verify that none of these characters are present in the cell's entry.
Dim IllegalCharacter(1 To 7) As String, i As Integer
IllegalCharacter(1) = "/"
IllegalCharacter(2) = "\"
IllegalCharacter(3) = "["
IllegalCharacter(4) = "]"
IllegalCharacter(5) = "*"
IllegalCharacter(6) = "?"
IllegalCharacter(7) = ":"

For i = 1 To 7
If InStr(mySheetName, (IllegalCharacter(i))) > 0 Then
MsgBox "You used a character that violates sheet naming rules." & vbCrLf & vbCrLf & _
"Please re-enter a sheet name without the ''" & IllegalCharacter(i) & "'' character.", 48, "Not a possible sheet name !!"
Exit Sub
End If
Next i
 
'Verify that the proposed sheet name does not already exist in the workbook.
Dim strSheetName As String, wks As Worksheet, bln As Boolean
strSheetName = Trim(mySheetName)
On Error Resume Next
Set wks = ActiveWorkbook.Worksheets(strSheetName)
On Error Resume Next
If Not wks Is Nothing Then
bln = True
Else
bln = False
Err.Clear
End If

'History is a reserved word, so a sheet cannot be named History.
If UCase(mySheetName) = "HISTORY" Then
MsgBox "A sheet cannot be named History, which is a reserved word.", 48, "Not allowed"
Exit Sub
End If

'If the worksheet name does not already exist, name the active sheet as the InputBox entry.
'Otherwise, advise the user that duplicate sheet names are not allowed.
If bln = False Then
Worksheets.Add.Name = strSheetName
MsgBox "A new sheet named ''" & mySheetName & "'' has been added.", 64, "Done"
Else
MsgBox "There is already a sheet named " & strSheetName & "." & vbCrLf & _
"Please enter a unique name for this sheet.", 16, "Duplicate sheet names not allowed."
End If

End Sub
Click to expand...

Hi, I realise this originates from a few years back but I stumbled on it and it's exactly what I need as part of another script I've written and I hope you don't mind me using it with a couple of modifications. My issue is that if any of the rules are broken (over an amount of characters, no name given, illegal characters etc) I need it to return to the "Enter new sheet name" input box. To accomplish this I've added "LoopOne:" before the first line then replaced the "Exit Sub"s with "GoTo LoopOne". This works perfectly for all the rules EXCEPT if there is a tab with a duplicate name. If for example there is one tab "abc" and another "def", I can get it to return to the Input box if I try to name a third sheet "abc" or "def" but if i then continue in that loop I can't call the new sheet anything else. Say for example on my 3rd attempt I try to call the new sheet "ghi" it will think there's already a sheet called that even if there isn't.

So... am I looping to/from the wrong place or should I be adding a command to clear the memory (if you will) of the error?

Thanks in advance
 
Upvote 0
Hi, adiereeves

I have revised Tom's original solution to my question to include a Do loop which should solve you question

Code: 
Sub NewWorksheet()

Dim sNewName   As String
Dim IBadCharacter(1 To 7) As String, i As Integer
Dim sTrimedName As String, wks As Worksheet
Dim bExit      As Boolean
Dim bStopChecks As Boolean

Do
'1) reset variables
bExit = False 'set to true if worksheet already exists
bStopChecks = False 'used to skip If routines
Set wks = Nothing

'2) Show InputBox
sNewName = InputBox("Enter proposed sheet name:", "Sheet name")
If sNewName = "" Then
MsgBox "You did not enter anything or you hit Cancel.", 64, "No sheet name was entered."
Exit Do
End If

'3) If the length of the entry is greater than 31 characters, disallow the entry.
If Len(sNewName) > 31 Then
MsgBox "Worksheet tab names cannot be greater than 31 characters in length." & vbCrLf & _
"You entered " & sNewName & ", which has " & Len(sNewName) & " characters.", , "Keep it under 31 characters"
bStopChecks = True
End If

'4) Sheet tab names cannot contain the characters /, \, [, ], *, ?, or :
'Verify that none of these characters are present in the InputBox entry

If Not bStopChecks = True Then
IBadCharacter(1) = "/"
IBadCharacter(2) = "\"
IBadCharacter(3) = "["
IBadCharacter(4) = "]"
IBadCharacter(5) = "*"
IBadCharacter(6) = "?"
IBadCharacter(7) = ":"

For i = 1 To 7
If InStr(sNewName, (IBadCharacter(i))) > 0 Then
MsgBox "You used a character that violates sheet naming rules." & vbCrLf & vbCrLf & _
    "Please re-enter a sheet name without the ''" & IBadCharacter(i) & "'' character.", 48, "Not a possible sheet name !!"
bStopChecks = True
Exit For
End If
Next i
End If

'5) History is a reserved word, so a sheet cannot be named History.
If Not bStopChecks = True Then
If UCase(sNewName) = "HISTORY" Then
MsgBox "A sheet cannot be named History, which is a reserved word.", 48, "Not allowed"
bStopChecks = True
End If
End If

'6) Verify that the proposed sheet name does not already exist in the workbook.
If Not bStopChecks = True Then
sTrimedName = Trim(sNewName)
On Error Resume Next
Set wks = ActiveWorkbook.Worksheets(sTrimedName)
If Not wks Is Nothing Then 'worksheet exists
bExit = True
End If
On Error GoTo 0
End If

'7) If the worksheet name does not already exist, create new worksheet
'Otherwise, advise the user that duplicate sheet names are not allowed.
If Not bStopChecks = True Then
If bExit = False Then
Worksheets.Add.Name = sTrimedName
MsgBox "A new sheet named ''" & sNewName & "'' has been added.", 64, "Done"
Exit Do
Else
MsgBox "There is already a sheet named " & sTrimedName & "." & vbCrLf & _
"Please enter a unique name for this sheet.", 16, "Duplicate sheet names not allowed."
bExit = False
End If
End If

bStopChecks = False
Loop Until bExit = True

End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Solved
Macro to Select and Print Worksheets Based on Unique Identifier in Worksheet Name
Replies
7
Views
110
JHCali
J
E
  • Solved
Enable sheet tab right click
Replies
6
Views
903
Eric Carolus
E
C
  • Question Question
username access to different worksheet VBA
Replies
4
Views
274
cilantro00
C
R
  • Question Question
How to find worksheet that contains specific table
Replies
2
Views
76
radek
R
A
  • Question Question
Ideas how to display a filtered list …
Replies
8
Views
485
igold
igold

Forum statistics

Threads
1,223,099
Messages
6,170,114
Members
452,302
Latest member
TaMere

Share this page

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
Back
Top