VBA - Testing sheet names for invalid characters before creating

[GingerStepChild]

I have a wee macro for creating a new sheet based on the value of a textbox.

I want to test that the textbox value does not contain any of the prohibited characters for sheetnames, namely : \ / ? * [ ].

I'm guessing that I could probably use a big NOT LIKE statement but is there a neater way to check for these characters.

I have tried writing an error handler for it but it creates a sheet then jumps to the handler when trying to name it rather than going to the handler before trying to name the sheet.

Some code

Sub AddPlayer()
'
' This sub will copy the games sheet into a new worksheet and call it after the new player.
' Each player must be unique so it will check if the name has already been taken
Dim ws1 As Worksheet




On Error Resume Next
Set ws1 = Sheets(OpenUserForm.TB_AddPlayer.Value)


On Error GoTo 0
If Not ws1 Is Nothing Then
    MsgBox "Already existing, choose another name"
Else
    On Error GoTo invalidchar:
    If OpenUserForm.TB_AddPlayer.Value > "" Then 
        Worksheets("NewPlayerSheet").Copy after:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
        ActiveSheet.Name = OpenUserForm.TB_AddPlayer.Value
        MsgBox "Player called " & OpenUserForm.TB_AddPlayer.Value & " has been added"
        MakePlayerList  ' create an A-Z ordered player list in the drop down box.
    Else
RestartHere:
    
    End If
End If
Exit Sub
invalidchar: 'Error handling for an invalid sheetname.
' message box then change the text box value to 'add player'
    MsgBox "Cannot use that name"
    OpenUserForm.TB_AddPlayer.Value = "Add Player"
    Resume RestartHere:
 
End Sub

Thanks in advance.

 

[dmt32]

Hi,
create a function to manage the illegal characters.

Try this update to your forms code:

Sub AddPlayer()
'
' This sub will copy the games sheet into a new worksheet and call it after the new player.
' Each player must be unique so it will check if the name has already been taken
    Dim ws1 As Worksheet
    Dim Player As String
    
    Player = Me.TB_AddPlayer.Value
    
    If Len(Player) = 0 Then Exit Sub
    
    On Error Resume Next
    Set ws1 = Sheets(Player)
    On Error GoTo 0
    
    If Not ws1 Is Nothing Then
        MsgBox "Player; " & Player & Chr(10) & "Already exists, please choose another name", 48, "Player Exists"
    Else
        On Error GoTo exitsub
        
        With ThisWorkbook
            .Worksheets("NewPlayerSheet").Copy after:=.Sheets(.Sheets.Count)
            .ActiveSheet.Name = IsValidSheetName(Player)
        End With
        
        MsgBox "Player called " & Player & " & chr(10) & has been added.", 48, "New Player Added"
        MakePlayerList  ' create an A-Z ordered player list in the drop down box.
    End If
    
    
exitsub:
    If Err > 0 Then MsgBox (Error(Err)), 48, "Error"
End Sub

following Function code can be placed in Standard module or your forms code page:

Function IsValidSheetName(ByVal SheetName As String) As String
    Dim InvalidChars As Variant
    Dim x As Integer
    
'Sheet names must:
'name len 31 characters max
'name must not contain any of the following characters: / \ * ? [ ]


'check for illegal characters
    InvalidChars = Array("[", "]", "/", "*", "\", "?")
    For x = LBound(InvalidChars) To UBound(InvalidChars)
'replace with underscore
        SheetName = Replace(SheetName, InvalidChars(x), "_", 1)
    Next x
'check sheet name char len
    SheetName = RTrim(Left(SheetName, 31))
'return valid sheet name
    IsValidSheetName = SheetName
End Function

Codes are untested but hopefully will do what you want.

Dave

 

[GingerStepChild]

thanks Dave,

I'll give it go a bit later.

 

[Rick Rothstein]

I want to test that the textbox value does not contain any of the prohibited characters for sheetnames, namely : \ / ? * [ ].

I'm guessing that I could probably use a big NOT LIKE statement but is there a neater way to check for these characters.

No, the Like statement is reasonably small. Let's say your TextBox is named TextBox1, then you can user this test...

[table="width: 500"]
[tr]
	[td]If TextBox1.Value Like "*[[/*\?]*" or TextBox1.Value like "*[]]*" Then
  ' TextBox contains a bad character
Else
  ' TextBox contains all valid characters
End If[/td]
[/tr]
[/table]

 

[GingerStepChild]

Hi,
create a function to manage the illegal characters.
...

MsgBox "Player called " & Player & " & chr(10) & has been added.", 48, "New Player Added"

Hi Dave,

This worked except there was a misplaced quote in this line and I've used the modified name rather than the original

MsgBox "Player called " & IsValidSheetName(Player) & Chr(10) & " has been added.", 48, "New Player Added"

Thanks a lot for your help.

 

[GingerStepChild]

No, the Like statement is reasonably small. Let's say your TextBox is named TextBox1, then you can user this test...

[TABLE="width: 500"]
<tbody>[TR]
[TD]If TextBox1.Value Like "*[[/*\?]*" or TextBox1.Value like "*[]]*" Then
  ' TextBox contains a bad character
Else
  ' TextBox contains all valid characters
End If[/TD]
[/TR]
</tbody>[/TABLE]

Hi Rick,

I thought I would need to create a LIKE statement for each of the invalid characters. Ok there is only 7 but wouldn't I need to separate into 7 different OR LIKE statements? I'm confused as to the *[[/*?]* or *[]]* you are testing for.

 

[Rick Rothstein]

Hi Rick,

I thought I would need to create a LIKE statement for each of the invalid characters. Ok there is only 7 but wouldn't I need to separate into 7 different OR LIKE statements? I'm confused as to the *[[/*?]* or *[]]* you are testing for.

A square bracket pair stands for a single character and that single character can be any one of the characters inside the square brackets. There are some options and limitation associated with them though. Go into the VB editor and type the work Like in any code window or in the Immediate window, then click inside the word and press the F1 key... that should take you to the help page for the Like operator with you can read all about this marvelous item.

 

[GingerStepChild]

Very interesting, thanks Rick. A very powerful operator indeed.

I'll have another look and play about with it tomorrow.