Are These Characters In A Given String (VBA)

Terry_Orange

Board Regular
Joined
Jul 22, 2002
Messages
133
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.
:)
 

Some videos you may like

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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

Board Regular
Joined
Jul 22, 2002
Messages
133
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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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:

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

Board Regular
Joined
Jul 22, 2002
Messages
133
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,561
Messages
5,596,853
Members
414,107
Latest member
Tigretto

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