Hi I have a text box in a form.. i want to ensure no special characters can be put in especially "|" just accept letters and numbers, full stops and comas.
Hi I have a text box in a form.. i want to ensure no special characters can be put in especially "|" just accept letters and numbers, full stops and comas.
here is a function that I just cobbled up that you can use in an if statement like :
[vba]
if not Check_Special(me.txtText.value) then
MsgBox "You have entered a special character" ' or whatever you want if it has a special character
end if
[/vba]
here is the function:
[vba]
Public Function Check_Special(strTemp As String) As Boolean
Dim strStrings As String
Dim x As Integer
strStrings = "~`|@#$%^&*()_+!<>?:;'""" 'put in whatever special characters you don't want in the string
For x = 1 To Len(strTemp)
If InStr(1, strStrings, Mid(strTemp, x, 1)) > 0 Then
Check_Special = False
Exit Function
End If
Next
Check_Special = True
End Function
[/vba]
GComyn
Just another possibility:
You might want to look at post #5 of: http://www.vbaexpress.com/forum/showthread.php?t=35742Option Explicit Private Sub TextBox1_Change() Static REX As Object If REX Is Nothing Then Set REX = CreateObject("VBScript.RegExp") With REX .Global = True .Pattern = "[^a-zA-Z0-9\.\,]" End With End If TextBox1.Value = REX.Replace(TextBox1.Value, vbNullString) End Sub
...as well.
Hope that helps,
Mark
Wouldn't it be better to simply alert the user that they've entered invalid characters (perhaps when clicking "Submit", change the background of the textbox to red and pop up a msgbox), instead of making them disappear as they're typed? Just saying.
Regards,
JP
Read the FAQ
Getting free help on the web
My website
Please use [vba][/vba] tags when posting code
A mixture of the above to advise & remove illegals
[vba]
Private Sub TextBox1_Change()
Dim strStrings As String, LastLetter As String
Application.EnableEvents = False
LastLetter = Right(TextBox1, 1)
strStrings = "\/~`|@#$%^&*()_+!<>?:;'"""
If InStr(1, strStrings, LastLetter) > 0 Then
MsgBox LastLetter & " not allowed"
TextBox1 = Left(TextBox1, Len(TextBox1) - 1)
End If
Application.EnableEvents = True
End Sub
[/vba]
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
Excellent thanks