Need help with the Like Operator

D

DennisE

Excel gurus:

Users enter numerical data into input boxes in my UserForms
in text form like $1,345,561.50 or as arithmetic expressions
like $40,000*(1.03)^2.5, and to check that no extraneous
characters have been inadvertently keyed in, I do the following:

Sub MyTextBox_AfterUpdate
If MyTextBox.Text Like "*[a-zA-Z`~!@#&_=:;|\<>'?{}""]*" Then
MsgBox "Sorry, you have entered an illegal character."
End If
End Sub

Rather than doing it that way, is it possible to somehow use the
Like operator in a complementary manner, in which the arguments
enclosed in brackets are the symbols that are permitted;
that is, [0-9$,.+-*/^()]

-- Dennis Eisen
 
D

Doug Glancy

Dennis,

This is a technique from Harald Staff (via Tom Ogilvy) that I think would
apply. It basically ignores any keystrokes that aren't in the first Case
sections. I think I've included all the ones you listed:

Private Sub MyTextBox_KeyPress(ByVal keyascii As MSForms.ReturnInteger)
Select Case keyascii
Case 43, 45, 42, 47, 94, 40, 41 'operator, etc. characters
Case 48 To 57 'numbers
Case Else 'Discard anything else
keyascii = 0
End Select
End Sub

hth,

Doug Glancy
 
M

Melanie Breden

Hi Dennis,
DennisE wrote:
Users enter numerical data into input boxes in my UserForms
in text form like $1,345,561.50 or as arithmetic expressions
like $40,000*(1.03)^2.5, and to check that no extraneous
characters have been inadvertently keyed in, I do the following:

Sub MyTextBox_AfterUpdate
If MyTextBox.Text Like "*[a-zA-Z`~!@#&_=:;|\<>'?{}""]*" Then
MsgBox "Sorry, you have entered an illegal character."
End If
End Sub

Rather than doing it that way, is it possible to somehow use the
Like operator in a complementary manner, in which the arguments
enclosed in brackets are the symbols that are permitted;
that is, [0-9$,.+-*/^()]
Click to expand...

here is an alternative to the Like-Operator:

Private Sub MyTextBox_AfterUpdate()
If CheckText(MyTextBox.Text) Then _
MyTextBox.Text = Algebra(MyTextBox.Text)
End Sub

Public Function CheckText(strText As String) As Boolean
With CreateObject("VBScript.RegExp")
.Pattern = "^([\d\s|$|.|,|+|-|*|/|^|(|)|])*$"
CheckText = .Test(strText)
End With
End Function


--
Mit freundlichen Grüssen

Melanie Breden
- Microsoft MVP für Excel -

http://excel.codebooks.de (Das Excel-VBA Codebook)
 
D

DennisE

I want to thank Melanie Breden and Doug Glancy for their suggestions regarding
the Like operator. After much research I finally stumbled upon what to do. The
solution is in inserting the ! character right after the opening bracket. The !
character immediately to the right of the opening bracket acts like a NOT
operator. Thus,

Sub MyTextBox_AfterUpdate
If MyTextBox.Text Like "*[!0-9$,.+-*/^()]*"
MsgBox "Sorry, you have entered and illegal character."
End If
End Sub

This will accept entries like $12,345.67 or
$52.27*(1.03)^2.5 and will reject entries containing an alphabetic characters
or extraneous symbols such as @ # { } [ ] \ ~ ` | \ < >

Once again, thanks all.

-- Dennis Eisen.
 
Top