Like Operator in Simple Macro

[boldcode]

Hi,

I want to use the Like Operator within an IF Statement. I created some code below that if a user tries to name a new worksheet using the these characters

:\/?*[]

then my message custom error message will come up.

Here is my code below:

If strNewWSName Like.........Then 'this is where I want to add those specail characters
                MsgBox Prompt:="Special Characters are not allowed as part of a worksheet name.", _
                Buttons:=vbExclamation, Title:=""
                GoTo ShowInputBoxEnterNewWSName
            End If

Thank you,

BC

[JapanDave]

This maybe,

If strNewWSName Like "*:*" Or strNewWSName Like "*/*" Or strNewWSName Like "*\*" Or strNewWSName Like "*?*" Or strNewWSName Like "***" Or strNewWSName Like "*]*" Or strNewWSName Like "*[*" Then
                MsgBox Prompt:="Special Characters are not allowed as part of a worksheet name.", _
                Buttons:=vbExclamation, Title:=""
                GoTo ShowInputBoxEnterNewWSName
            End If

[boldcode]

JapanDave,

Thank you very much, your code works!

- BC

[MarvinP]

Hi,

See if http://www.bettersolutions.com/vba/V...T218312322.htm explains how to do individual character matches using VBA.

[boldcode]

MarvinP,

Thank you, I will check out the link.

- BC

[Leith Ross]

Hello boldcode,

The best method is to use Regular Expressions when checking for multiple characters. This macro will return True Or False after testing the file name for special characters. True if there are no invalid characters or False if there are.

Function IsFilenameValid(ByVal Filename As String)

    Dim RegExp As Object
    
        Set RegExp = CreateObject("VBScript.RegExp")
        RegExp.Pattern = "[\:\\\/\?\*\[\]]"
        
        IsFilenameValid = Not RegExp.test(Filename)
        
End Function

[boldcode]

Hi Leith,

How do I use your function within my if statement? The reason I ask is because I expanded my macro into multiple if statements as shown below:

Do
ShowInputBoxEnterNewWSName:
        strNewWSName = Application.InputBox(Prompt:="Enter a name for the New Worksheet...", Title:="", Type:=2)
            If strNewWSName = "False" Then Exit Sub
            If IsFilenameValid(strNewWSName) ....Then 'I want to use it here if possible
                MsgBox Prompt:="Special Characters are not allowed as part of a worksheet name.", _
                Buttons:=vbExclamation, Title:=""
                GoTo ShowInputBoxEnterNewWSName
            End If
            If strNewWSName = "" Then
                MsgBox Prompt:="You must enter a Worksheet Name...", Buttons:=vbExclamation, Title:=""
            ElseIf WorksheetExists(strNewWSName) Then
                MsgBox Prompt:="Worksheet " & Chr(34) & strNewWSName & Chr(34) & " already exists! ", _
                Buttons:=vbExclamation, Title:=""
            strNewWSName = ""
            End If
    Loop Until strNewWSName <> ""

I appreciate the help!

[boldcode]

Leith Ross,

Thank you again, I marked the post as Solved.

- BC

[jindon]

Just change the line

If (strNewWSName Like "*[:\/?[*[[]*") + (strNewWSName Like "*]*") Then

[boldcode]

jindon,

Awesome, thank you!

- BC