Code Contribution

[DennisE]

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

 

[Tom Ogilvy]

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.

 

[DennisE]

Tom,
The reason I strip the formatting, even
where there is no algebraic formula
being supplied, is that it often happens
that users input a value such as
$10,00 or the like. In such cases the Algebra function will strip it down and
return the value 1000 to the After_Update routine that invoked it, which will
in
turn reformat and present the result as $1,000 in that particular case. Seeing
that change take place in the textbox, users either thank the program for
correcting their syntax, or will say, "oops, I meant
$10,000 and will adjust the input
accordingly."

I do appreciate your taking the time to test the Algebra function portion of
the overall procedure, but don't overlook the formatting instructions within
the After_Update portion that are part of it all as well.

And, yes, my programs from the outset
state that they run only under Excel
2000+

-- Dennis Eisen

 

[Bob Phillips]

And, yes, my programs from the outset
state that they run only under Excel
2000+

Then you are cutting yourself off from the majority of Excel users.

 

[Tom Ogilvy]

Your still have inconsistently used the Like operator

if your intent to consider , and . in the first instance is accomplished
with "*[!0-9$.,+-/*^()]*" using redundant characters

then why in the second do you use "*[+-/*^()]*"

and their you unnecessarily process a simple number for no apparent purpose.
It clearly looks like you are getting unintended results to me.

I didn't find any mention of only running in xl2000+ in your posting.

Changing interpretation of a user entry without specific notification
wouldn't appear as a positive attribute to me. If there is a question about
the validity of an entry, it would be my opinion that the user should be
consulted.

 

[Mike]

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

Dennis,

The first thought that crossed my mind while reading this was to
create a new class to handle the event so that you don't have to
maintain code for each textbox. John Walkenbach's site contains an
example of this: http://j-walk.com/ss/excel/tips/tip44.htm.

My original intent was to modify this example to apply to textboxes,
use the AfterUpdate event, and include a NumberFormat property. I
soon discovered (as many people already had) that we cannot handle the
AfterUpdate, BeforeUpdate, Enter or Exit events in this way. These
events are inherited from the Control object. Does anyone know if
this has changed with the newer (>2000) versions? or is there another
workaround?

However, while searching for information I stumbled onto a post from
Colo that referred to this thread: http://tinyurl.com/2mhko. I
modified Colo's first example (which works with Excel 2000) a little
bit and came up with this:


Create a class module named clsForm and add this code

'¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤

Option Explicit

Public Event GetFocus()
Public Event LostFocus(ByVal ControlName As String)
Private PreviousControl As String

Public Sub CheckActiveControl(objForm As MSForms.UserForm)
With objForm
PreviousControl = .ActiveControl.Name
RaiseEvent GetFocus
On Error GoTo Terminate
Do
DoEvents
If .ActiveControl.Name <> PreviousControl Then
RaiseEvent LostFocus(PreviousControl)
PreviousControl = .ActiveControl.Name
RaiseEvent GetFocus
End If
Loop
End With
Terminate:
Exit Sub
End Sub

'¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤




Create a userform, add a few textboxes, change the tag property of
each textbox to reflect the desired number format, and add the
following code:

'¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤

Option Explicit

Private WithEvents objForm As clsForm

Private Sub UserForm_Initialize()
Set objForm = New clsForm
End Sub

Private Sub UserForm_Activate()
objForm.CheckActiveControl Me
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As
Integer)
Set objForm = Nothing
End Sub


Private Sub objForm_GetFocus()
ActiveControl.BackColor = &HC0E0FF
End Sub

Private Sub objForm_LostFocus(ByVal ControlName As String)
Dim ctl As Control
Set ctl = Me.Controls(ControlName)
ctl.BackColor = &HFFFFFF
If TypeName(ctl) = "TextBox" Then _
ctl.Text = Format(Algebra(ctl.Text), ctl.Tag)
Set ctl = Nothing
End Sub

'¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤


Finally add a module to hold your Algebra function. (You might want
to add some additional error handling to this function. The user
could still get errors if they enter things like "1++1" or "1/0".)



Hope this helps,

Mike