Are These Characters In A Given String (VBA)

[Terry_Orange]

In Excel certain characters are not allowed to be used in the name of a worksheet.

for example
: / ? * [ or ]

I have text inputbox and this is used as the value for the worksheet that is created on the fly.

I was hoping to use something like INSTR ...

Dim ssstring As String
ssstring = "/"

If InStr(0, UserForm1.tBox1.Value, ssstring) = 0 Then
'do nothing
Else
MsgBox ("Please don't use / ? * [ or ]")
End If

But Excel doesn't like this. Has anyone had this problem before and solved it ??? What solutions can anyone suggest ??? How do you use INSTR (is it because I'm using a single character and not a string ?).

Thank you

TO.

 

[Andrew Poulsom]

The first argument for Instr should be 1 not 0.

Also have a look at the Like operator - you should be able to test for all the offending characters in one pass.

 

[Terry_Orange]

Thanks Andrew.

The instr works great, but I have taken your advice and tried the LIKE function.

Any ideas why the following does not work ?

If UserForm1.tBox1.Value Like "[(/)()(*)(?)([)](])" Then
MsgBox ("Please don't use / ? * [ or ]")
Else
MsgBox ["No Problems"]
End If

Thanks
TO

 

[Andrew Poulsom]

According to Help the right bracket (]) can't be used within a group to match itself, but it can be used outside a group as an individual character. I take "outside a group" to mean "without a group" because it wouldn't work unless I performed two tests.

This seems to work:

Private Sub CommandButton1_Click()
    If UserForm1.tBox1.Value Like "*[/(?)(*)([)]*" Then
        MsgBox "Please don't use  / ? * [ or ]"
        tBox1.SetFocus
        tBox1.SelStart = 0
        tBox1.SelLength = Len(tBox1.Text)
    ElseIf UserForm1.tBox1.Value Like "*]*" Then
        MsgBox "Please don't use  / ? * [ or ]"
        tBox1.SetFocus
        tBox1.SelStart = 0
        tBox1.SelLength = Len(tBox1.Text)
    Else
        MsgBox "No Problems"
    End If
End Sub

Note that you don't need brackets around or /.

 

[Terry_Orange]

Once again Andrew you are a STAR.

Works like a dream. Just needed to get my head around what brackets and what *s to use !

:wink:

Thanks.
TO

 

[Richie(UK)]

Hi Terry,

I know Andrew has helped solve your current problem, but just for future reference you may like to have a look at the following link. It contains a function that will enable you to 'disable' any keys you choose during textbox input. (Well I was impressed anyway :wink: )

http://www.scottm.dircon.co.uk/vbatips/vba/userform/restrict.htm