Checking characters

[Mats Samson]

RI've a Userform where a textbox picks its value as a proposal from a
worksheet cell when initiating.
This cell may contain invalid filename characters like %&/.
I want the user alerted so they may change the characters in the textbox
before proceeding. The corrected textbox string will be saved in another cell.
regards
Mats

 

[Jacob Skaria]

Try this Textbox1 Exit event...code...You can add more invalid characters to
the variable strInvalid...

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim strInvalid As String, intTemp As Integer
strInvalid = "%&/."
For intTemp = 1 To Len(strInvalid)
If InStr(Me.TextBox1.Text, Mid(strInvalid, intTemp, 1)) > 0 Then
MsgBox "Invalid character present " & strInvalid
Cancel = True
End If
Next
End Sub

If this post helps click Yes

 

[Mats Samson]

Thank you Jakob,
it worked fine!
Cheers
Mats

Try this Textbox1 Exit event...code...You can add more invalid characters to
the variable strInvalid...

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim strInvalid As String, intTemp As Integer
strInvalid = "%&/."
For intTemp = 1 To Len(strInvalid)
If InStr(Me.TextBox1.Text, Mid(strInvalid, intTemp, 1)) > 0 Then
MsgBox "Invalid character present " & strInvalid
Cancel = True
End If
Next
End Sub

If this post helps click Yes

 

[Rick Rothstein]

You don't need to loop through all the characters in the TextBox to do this;
you can make use of the Like operator instead...

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If Me.TextBox1.Text Like "*[%&/.]*" Then
MsgBox "One of these Invalid character are present: %&/."
Cancel = True
End If
End Sub

However, for a general solution, I think there are too many invalid
characters to list. Using the valid character list at this link...

http://support.microsoft.com/kb/177506

Here is a function that can be called to test if a text string is composed
of valid characters or not...

Function IsValidFileName(FileName As String) As Boolean
IsValidFileName = InStr(FileName, "]") > 0 Or Not FileName _
Like "*[! 0-9A-Za-z^&'@{}[,$=!#()%.+~_-]*"
End Function

The OP can then use this function like this...

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If Not IsValidFileName(Me.TextBox1.Text) Then
MsgBox "Invalid character present!"
Cancel = True
End If
End Sub

 

[Mats Samson]

Thank you Rick,
that works well too.
It's no big deal in my case. The value picked to the textbox is a customer
order number that will be used later on in the filename for reference. In
most cases the invalid character is a : or a /. Will keep your solutions in
mind, new things happens
all the time!
Regards
Mats

You don't need to loop through all the characters in the TextBox to do this;
you can make use of the Like operator instead...

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If Me.TextBox1.Text Like "*[%&/.]*" Then
MsgBox "One of these Invalid character are present: %&/."
Cancel = True
End If
End Sub

However, for a general solution, I think there are too many invalid
characters to list. Using the valid character list at this link...

http://support.microsoft.com/kb/177506

Here is a function that can be called to test if a text string is composed
of valid characters or not...

Function IsValidFileName(FileName As String) As Boolean
IsValidFileName = InStr(FileName, "]") > 0 Or Not FileName _
Like "*[! 0-9A-Za-z^&'@{}[,$=!#()%.+~_-]*"
End Function

The OP can then use this function like this...

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If Not IsValidFileName(Me.TextBox1.Text) Then
MsgBox "Invalid character present!"
Cancel = True
End If
End Sub