special characters

V

Valeria

Dear experts,
I am working on a userform where I have a textbox - the
user should input a name, and as this is used afterwards
to name a file, it should not contain any "Special"
characters (like \, /, etc).
Now, I have written some code to try and spot when the
user inputs such characters, but 1) I have a problem with
the " character (Excel does not accept textbox.value = """
and not even Msgbox = "|<>/"?*\" )and 2)I am pretty sure
there must be a simpler way to do this!
Here is my code:

For i = 1 To Len((TextBox33.Value))

If Mid(TextBox33.Value, i, 1) = "\" _
Or Mid(TextBox33.Value, i, 1) = "|" _
Or Mid(TextBox33.Value, i, 1) = "/" _
Or Mid(TextBox33.Value, i, 1) = "*" _
Or Mid(TextBox33.Value, i, 1) = "?" _
Or Mid(TextBox33.Value, i, 1) = "<" _
Or Mid(TextBox33.Value, i, 1) = ">" _
Or Mid(TextBox33.Value, i, 1) = ":" Then

MsgBox "The product name cannot contain any of the
following characters:" & vbNewLine & "\/:*?<>|"
Exit Sub
End If
Next i

Can you please help me?
Many thanks in advance!
Best regards,
Valeria
 
B

Bob Phillips

Valeria,

Try this. It is more easily extendible than yours, just add more characters
to the string sChars

Dim i As Long, j As Long
Dim sChars As String

sChars = "\/*?<>"":"
With TextBox33
For i = 1 To Len((.Value))
For j = 1 To Len(sChars)
If Mid(.Value, i, 1) = Mid(sChars, j, 1) Then
MsgBox "The product name cannot contain any of the
following characters:" & _
vbNewLine & sChars
Exit Sub
End If
Next j
Next i
End With


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
R

Rob van Gelder

Valeria,

Pretty much the same as yours, but replaced the If .. Or .. Or .. Then with
a Select Case statement.

To represent a " inside a string, use ""
It looks strange when all you want to do is show a single " inside a
string - it ends up with 4 of them looking like MsgBox """"
I've seen a lot of code written as MsgBox Chr(34) by people who aren't aware
of the double-quote concept.


Private Sub CommandButton1_Click()
Dim i As Long

For i = 1 To Len(TextBox1.Text)
Select Case Mid(TextBox1.Text, i, 1)
Case "\", "|", "/", "*", """", "?", "<", ">", ":"
MsgBox "The product name cannot contain any of the following
characters:" & vbNewLine & "\ / : * "" ? < > |"
Exit Sub
End Select
Next
End Sub


Rob
 
Top