don't think you are quite there yet Dennis - not if you want it to actually
work the way you have designed.

To Illustrate:

? "123.56" Like "*[+-/*^()]*"
True

Should 123.56 be evaluated? (as it is now)
Also
It is unclear what your intent is with $12,345,678

Should that be stripped of the the $ and , and placed back as an unformatted
number. (which is what it does now)

? algebra("$12,345,678.25")
12345678.25


You have a similar problem in your first Like statement although because the
characters between + and / are repeated in your list, it doesn't cause a
problem. You need to read the considerations for use of hypen in a
character list for the Like operator.


Also, use of Replace restricts its use to xl2000 or later. You might want
to point that out.

--
Regards,
Tom Ogilvy



"DennisE" wrote in message
...
I use UserForms almost exclusively for supplying input data to my
Excel programs. I permit users to enter the numerical data in the
respective textboxes in virtually any formatted or algebraic form,
such as $12,345,678 or $12.345*(1.035)^2.5-10500/2

The ability to modify the data algebraically lets users pose
"What If?" questions without having to find their hand
calculators and avoids transcription errors. I also wanted
to trap an incorrect symbol that is inadvertently entered
by the user.

Several MVP's (particularly Melanie B.) supplied insightful
help, and I would like to post the finished procedure for
anyone who's interested. If any of you MVP's believe it
worthy enough, you can pass it up to one of the Excel
web sites that catalog novel procedures.

-- Dennis Eisen

In the code for each texbox insert:

Private Sub MyTextBox_AfterUpdate()
Dim ResultString As Variant
ResultString = Algebra(MyUserForm.MyTextBox.Text)
MyUserForm.MyTextBox.Text = Format(ResultString, "$###,###,##0")
'Replace $###,###,###0 by whatever format each input requires
End Sub


In the main module code insert:

Function Algebra(InputString As String) As Variant
If InputString Like "*[!0-9$.,+-/*^()]*" Then
'The ! right after [ serves as the negation operator
Dim Message As String
Message = "Sorry, the input field you just left contains one or more
illegal
characters."
MsgBox Message, 48, "Data Entry Error"
Algebra = InputString
Exit Function
End If
Algebra = InputString
If InputString Like "*[+-/*^()]*" Then
InputString = Replace(InputString, "$", "")
InputString = Replace(InputString, ",", "")
Algebra = Evaluate(InputString)
End If
If Algebra = "" Then Algebra = 0
End Function