Thought I'd add my 2 cents worth. Avoid magic numbers, especially
when you can use builtin VBA constants. The following is a little
more intuitive to read and a future developer does not have to lookup
48 and 57 to see exactly what is being blocked.
Private Sub Text2_KeyPress(KeyAscii As Integer)
'Cancel any numeric keys
If KeyAscii >= vbKey0 And KeyAscii <= vbKey9 Then
KeyAscii = 0
End If
End Sub
The above solution does not stop the user from cutting and pasting a
numeric character. Its often the small things that get you. <g> I have
a couple of generic functions, which do stop the user from pasting
banned characters and it also allows you to add a series of non
consecutive banned characters such as spaces or quotes or commas etc.
Both functions could be modified for speed but I'm lazy and they work
so I've never bothered tweaking them. Pieter's solution is much faster
but does not allow you to easily add a newly banned character for
numerous text boxes by modifying one constant in one place. I couldn't
find the functions, so I rewrote them. You should test them if you use
them and limit the length of the string to the maximum length of the
field. This will slow down enough to be painful with a huge field so
use it sparingly. You can also trap the "paste" using API calls and
check the string at this point, which will speed things up
considerably but all of this is overkill for 99% of applications.
Have fun. Can you tell I'm bored? I go sailing in two days for four
weeks so I'm just killing time at work.
'String of banned characters for text boxes
Const fstrBANNED = "0123456789"
'Maximum text box field length
Const flngMAX_LENGTH As Long = 255
Private Sub Text0_Change()
StripBannedChars Text0
End Sub
Private Sub Text0_KeyPress(KeyAscii As Integer)
CancelBannedKey KeyAscii, fstrBANNED
End Sub
Public Sub CancelBannedKey( _
ByRef rintKeyASCII As Integer, _
ByVal vstrBanned As String)
'************************************************* *******
'Purpose: Cancels the key press for all banned
' characters specified in the supplied string
'************************************************* *******
'Loop through every character in the supplied string
While Len(vstrBanned) > 0
'If the key pressed is the specified key, cancel it
If rintKeyASCII = _
Asc(Left(vstrBanned, 1)) Then rintKeyASCII = 0
'Strip the leading character
vstrBanned = Right(vstrBanned, Len(vstrBanned) - 1)
Wend 'more characters
End Sub
Private Function strStripBannedChars( _
ByVal vstrSource As String, _
ByVal vstrBanned As String) As String
'************************************************* *******
'Purpose: Strips all banned characters from the
' supplied source string
'************************************************* *******
'Loop through every character in the supplied string
While Len(vstrBanned) > 0
'Replace any occurrence of each banned
'character with an empty string
vstrSource = Replace(vstrSource, _
Left(vstrBanned, 1), vbNullString)
'Strip the leading character from the banned characters
vstrBanned = Right(vstrBanned, Len(vstrBanned) - 1)
Wend 'more characters
strStripBannedChars = vstrSource
End Function
Public Sub StripBannedChars(ByVal vtxt As TextBox, _
Optional ByVal vlngMaxLength As Long = flngMAX_LENGTH)
'************************************************* *******
'Purpose: Strips all banned characters from the supplied
' textbox. The cursor returns to the start of
' the textbox if banned characters are found.
'************************************************* *******
Dim strSource As String
Dim strStripped As String
'The source string to be tested for banned characters
strSource = vtxt.Text
'The source string with banned characters removed
strStripped = strStripBannedChars(strSource, fstrBANNED)
'If the source string has any banned characters
'remove them. This will trigger this function
'again. Beware of recursive event triggers!
If strSource <> strStripped Then
vtxt.Text = Left(strStripped, vlngMaxLength)
End If
'Finished with the local pointer
Set vtxt = Nothing
End Sub
