ONE textbox to act like a calculator

ps...
For multiplication use the asterick (*)
For division use the forward slash (/)

:-)
ah

I have made an improvement so that it can be used as a function.  That way you can put it in any textboxes change event (or whatever event you want) without having to use the entire code each time.
Instead of all that code in each textbox event, you would just put:
TextBoxName.Text = GetVal(TextBoxName.Text)

But let me know if that link is what you want first, and I will upload the improved one for you if you want this to work with multiple text boxes.

:-)
ah

Very good!

Can the file handle multiple numbers?

Eg: 12+12-3+4=

And can the file handle two place decimals?

Eg 12.12+12.12=

I like the multiple text box idea.

No, I never thought about that.
Will you be mixing operators like + and * .  If so then do you want the operations done in the order they are entered, or the proper order of operations (multiplication and division first, then addition and subtraction)
This can get very complicated.  It may take me awhile depending on what you want, If I can even do it...:o)
But if I think I can do it, I will try.

:-)
ah

Actually, this turned out to be very simple.
You can enter anything in the text box that you could enter into a cell.  You can use complex combinations using parenthisis if you want.  The order of operation will done in the proper order of operations.

Enter it the same as before...with the = sign at the end.
Here is the link:

http://www.ee-stuff.com/Expert/Upload/getFile.php?fid=2762

Enter the function the same as is the one behind the change event of the textbox for every textbox you want to use.  Just change "TextBox1" to the name of the textbox you are putting it behind.

Another nice thing about this, is you should be able to use the function in a cell also if you change "Dim entStr as String" to "Dim entStr as Variant" in the function, but I did not test it to see for sure if everything still worked.

:-)

oops...wasn't thinking. You cannot use my second upload in a cell the way I uploaded it.  The function would have to moved to a general module, then you could.

:-)
ah

Give me a day or so to integrate it into my current setup.  

But I like the way you put the textbox value into a cell, let Excel do the figuring and then copy the result back into the text box.

Ok...no problem.
Also, as far as trying to use the function in a cell...there is no point.  You could do any calculation that the function would do, better without the function...

:-)
ah

Ok...here is the latest...
I see from your other question that you might be trying to make this work by hitting enter, instead of the "=" sign.  If that is true, then this will require a bit of change.
Right now, it tests the characters in the entry to make sure they are ok.  If not, a message is returned.
Also, the way it is written right now, you can copy what is in the text box's change event and paste it into any other text box's event and change nothing.  That is because I used "Me.ActiveControl" instead of the name of the text box.  If you decide you want to convert this over to hitting the enter key, then you will probably have to change that back to the name of the text box.

Check it out and see what you think...
Here is the link:
http://www.ee-stuff.com/Expert/Upload/getFile.php?fid=2765

:-)
ah

I'll get this right eventually...
After more testing, I see I have to add the period to the allowed characters in the Select Case statement of the TestChrs function, so when you download that last file I uploaded, add the period to the line of characters to allow.

Sorry...

:-)
ah

I amended your code by taking out the TextBox1_Change and replacing it with this to detect the Enter key:

Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    If KeyCode = 13 Then
        If TestChrs(Me.ActiveControl.Text) = False Then
            MsgBox ("You have entered an invalid character. Cannot continue")
        Else
        TextBox1.Text = GetVal(TextBox1.Text)
        End If
    End If
End Sub


Also, one more change is format the result to the desired rounding off to two decimal points:

GetVal = Format(Range("IV65536").Value, "Standard")


============= The Entire Code ======================
Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    If KeyCode = 13 Then
        If TestChrs(Me.ActiveControl.Text) = False Then
            MsgBox ("You have entered an invalid character. Cannot continue")
        Else
        TextBox1.Text = GetVal(TextBox1.Text)
        End If
    End If
End Sub

Function GetVal(entStr As String)
On Error GoTo errhandler
   
ActiveSheet.Range("IV65536").Formula = "=" & entStr
GetVal = Format(Range("IV65536").Value, "Standard")
done:
    Exit Function
errhandler:
    MsgBox Err.Number
    Resume done
End Function

Function TestChrs(strChrs As String) As Boolean
    strChrs = Trim(Replace(strChrs, " ", ""))
    Dim x As Integer
    For x = 1 To Len(strChrs)
        Select Case Mid(strChrs, x, 1)
            Case "1", "2", "3", "4", "5", "6", "7", "8", "9", "0", "+", "-", "*", "/", "=", "."
                TestChrs = True
            Case Else
                TestChrs = False
                Exit Function
        End Select
    Next x
End Function

Is there an easy method of using the PressDown event to multiple textboxes without having to open a sub for each textbox?

also...Just so you'll know, I chose to put the error handler in with the event code instead of the function.  This prevents the the contents of the text box from being deleted.

ah

Your new code seems to work well.  2 things I noticed though..which may be fine for you, that's up to you...but with the error handler in the function, the textbox clears itself completely on an error, and where you are using err.Number instead of err.description could possibly misleading to the user.  It brings up a number of 1004 if the cell cannot accept the formula as intered.
But as I said, that may be fine.  I don't know your situation.
As far as having code run without putting it behind every textbox, You can't do that without using an API and create a hook to watch for all keystrokes (now you are bordering on a keylogger).  If you are interested in using an API, then I will try to figure it out.
But your changed code seems to work well...

:-)
ah

I just thought of something else...be back in a bit

ah

Never mind...that idea didn't work.
Let me know if you want to try the API idea

:-)
ah

I want to implement your code into my project tomorrow and see if it works.  I'm now trying to change the background color of the text box if any of the arithmetic characters is pressed to let the user know they are in 'calculator' mode.

I just came across a little quirk.  Formatting to standard puts a comma into the box which needs to be added as a safe character but then also pulls up a '1004' error.  I may take it out.

API would definitely put me on a new learning track but you've already spent enough time on this question and helped tremendously!  If you want to go further, then by all means lets do it.  I can open another question if you'd like.

Try moving your formatting to the text boxes event when you set the text box's value and use this instead:

Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    If KeyCode = 13 Then
        If TestChrs(Me.ActiveControl.Text) = False Then
            MsgBox ("You have entered an invalid character. Cannot continue")
        Else
        TextBox1.Text = Format(GetVal(TextBox1.Text), "0.00")
        End If
    End If
End Sub


Don't forget to take the format out of the GetVal function that you put in there before...

I will check into the API, but I may not get that done until tomorrow.  It will be a little more complicated for me to figure out.  I don't normally use api's.  (although I have written a good all out keylogger for my own backup use, but it's been awhile).
The way it will work, is if the enter key gets pressed, than all the text boxes will be evaluated if any of the 4 operators are present in them.

:-)
ah

You can trap the events of a text box by setting up a class with a variable of type MSForms.Textbox declared Public WithEvents. You don't need any API calls and you certainly don't need a low-level keyboard hook - though I do have code for one! ;)
Unless you have a lot of textboxes though, it's simpler to just call the same function from each textbox, or just use one commandbutton that validates each textbox in turn. If you were to use Application.Evaluate(textbox.text) for all textboxes on the form, it should work - if an error occurs, you know the syntax is wrong in that textbox.
Of course, given that your users are sitting in front of a computer with a copy of Excel loaded, it seems a little like reinventing the wheel...
Regards,
Rory

I agree with Rory that simpler is better as far as implementation and user experience.  However, I do have 7 textboxes that need this 'calculator' functionality.

So far my code just for ONE textbox is as follows:

Private Sub TextBox13_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
    If Highlight(Chr(KeyAscii)) = True Then
        'Once the + - * / key is pressed, set the bgcolor and caption
        TextBox13.BackColor = RGB(255, 255, 0) 'yellow
        Label5.Caption = "**Formula Mode**"
    End If
End Sub

Private Sub TextBox13_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    If KeyCode = 13 Then
        If TestChrs(Me.ActiveControl.Text) = False Then
            'set the back color, set the focus
            TextBox13.BackColor = RGB(255, 255, 0) 'yellow
            KeyCode = 0
        Else
            'Get the final value, reset the bgcolor & label to default
            TextBox13.Text = GetVal(TextBox13.Text)
            TextBox13.BackColor = RGB(255, 255, 255) 'white
            Label5.Caption = "Amount"
        End If
    End If
End Sub


So if I duplicate this code for all 7 textboxes, it will make for some long code and laborious editing since when one sub gets editing, all subs get editing.  Now, if there were a way to shorten this down....

you are really coming along on this.  I do agree with simpler being better also.  I have created one that will only require code in the form activate event using the GetAsnycKeyState api, and it works, but it still needs a hook added to it because it is in a Do Loop and uses up way to many system resources...like 98% of my cpu usage.
One thing you can do is replace all you textbox names with :  Me.ActiveControl because in the keypress, and the keydown events, that textbox will be the active control.  
Then when you copy and paste, you won't have to go through and change all the textbox names, and with only 7 text boxes, that won't be to much code.

If you want to post your Highlight function code, I will see if I can shorten up the whole thing.
No guarantees...but I'll check it out.

Here's the code for the functions:

I amended the TestChrs function by adding in a nested Select Case.  It tests whether or not the formula ends with a mathmatical symbol.  I did this because it returned a '1004' error.

Function TestChrs(strChrs As String) As Boolean
    strChrs = Trim(Replace(strChrs, " ", ""))
    Dim x As Integer
    For x = 1 To Len(strChrs)
        Select Case Mid(strChrs, x, 1)
            Case "1", "2", "3", "4", "5", "6", "7", "8", "9", "0", "+", "-", "*", "/", "."
                Select Case Right(strChrs, 1)
                    Case "+", "-", "*", "/", "."
                        MsgBox "Don't end your entry with a mathmatical symbol."
                        TestChrs = False
                        Exit Function
                    Case Else
                        TestChrs = True
                End Select
            Case Else
                TestChrs = False
                MsgBox ("You have entered an invalid character. Please correct.")
                Exit Function   'Exit once it finds a match
        End Select
    Next x
End Function

---

Here's the Highlight function that tests if any of the mathmatical symbols are pressed and immediately highlights the textbox.  This won't work in the KeyDown event because it is always one keystroke too late.  So it has to reside in the KeyPress event:

Function Highlight(stChrs As String) As Boolean
    stChrs = Trim(Replace(stChrs, " ", ""))
    Dim x As Integer
    For x = 1 To Len(stChrs)
        Select Case Mid(stChrs, x, 1)
            Case "+", "-", "*", "/"
                Highlight = True
                Exit Function   'Exit once it finds a match
            Case Else
                Highlight = False
        End Select
    Next x
End Function

---

This error handler message should never appear due to the redone TestChrs function weeding out the illegal characters and mathmatical symbol at the end.

Function GetVal(entStr As String)
On Error GoTo errhandler
   
ActiveSheet.Range("IV65536").Formula = "=" & entStr
GetVal = Range("IV65536").Value
done:
    Exit Function
errhandler:
    If Not Err.Number = 0 Then MsgBox "Spreadsheet Error.  Please correct."
    Resume done
End Function

The only other thing I can think that would give this 'calculating' textbox an extra 'wow' is a 'remembering' feature.

Similar to when you edit an excel cell, the formula bar displays exactly what you had entered.  Well, lets say the user wanted to see what they had entered in the textbox to come up to the displayed result:

They originally enter: 1+1+1+1+2
Textbox will read: 6
Now suppose they want to 'edit' the '2' to a '1'?  Double click the textbox?

Do you see where I'm going with this?  

I know the users will be very happy with the current 'calculating' functioning so maybe I'm trying to make this more of a project than it should be.

I should add that a possible double clicking the textbox will bring up '1+1+1+2' into the textbox - not the formula bar.  

But thinking further, how will the Hightlight function react to this since the populating of the textbox is not being done via a keypress?

Lets keep it simple.  I'd say that this project is DONE!

I really like the features you added, Very good job!  Here is one more attempt at making it easier with shorter code in the textbox events.  There are 2 or 3 more functions added, but they all utilize the functions you are using.  Maybe if you haven't got to far along, you might want to intigrate it into your app.  It also brings the formula back if you double click on the text box.  It does this by setting the controls tag = to it's value just before it calculates.  If you double click on it, it sets it's value back to  what is in it's tag, runs a check (one of the added functions) to see if it needs to change back to formula mode (which it probably always will) and sets the color and label accordingly.


http://www.ee-stuff.com/Expert/Upload/getFile.php?fid=2778

I think you are probably pretty much to the point where there are no more improvements to make whether you use some of this new one or not.  But it did turn out to be a very cool little app that I am going to keep.  Good Idea!

:-)
ah

I haven't tested your new upload but I discovered a quirk in the process.

Because the textbox will be formatted as standard or currency and will result in comma separations (eg: 9,000.00), you would have to tweak the code as follows:

Amend the TestChrs function:

- add a line to replace the comma:

    strChrs = Trim(Replace(strChrs, " ", ""))   'replace any spaces
    strChrs = Trim(Replace(strChrs, ",", ""))   'replace any commas

- assign strChrs to a global variable when the result is True:

    TestChrs = True
    clnTextBox = strChrs   'clnTextBox is the global variable


Amend the KeyDown event:

Replace: Me.ActiveControl.Text = GetVal(Me.ActiveControl.Text)

With: Me.ActiveControl.Text = GetVal(clnTextBox)


You can't allow the comma as an allowed character in the Select Case section; otherwise, the code will pass that on to the worksheet cell for processing.  So strip out the comma and all is well.

Let me know if you can integrate this into the code.

Wow, your new upload is pretty cool.  I will definitely integrate it into my app.

I like how if a single number is entered, it will not respond to a double click.  I was worried that it would go into Formula Mode for a single value entry.

I'm glad you like the idea and how responsive you were to it.  I can foresee using this in my other apps as well.  This originated from a user who asked if they could get a popup calculator similar to my app popping up a calendar (Excel's calendar control).   As far as I know, there is no such control.

So I think what we have here is a very very fair substitute and very easy to use.  I'm sure the users will enjoy it.

Well, the problem you were concerned with can still happen.  If you enter a formula, hit enter, then change the result to a different digit, then double click the text box, the previous formula will still pop back up.  I can fix that by clearing the tag whenever a key gets pressed if there is no operator in the text box, but then there you will not be able to bring the formula back up once you press a key after the result is returned.
I got the comma problem fixed I think.  The code does not format the text box result at all right now, so let me know what format you want, and whether or not you want the controls tag cleared once you make a entry into the text box.

:-)
ah

Yes, I do see that behavior.

I don't think it's a big deal to leave the previous formula in.  I seriously doubt this will be an issue.  If the user errs by typing in the textbox after the formula results, they have the previous formula as a backup.  So I say leave this part as-is.

The format should be standard at two decimal places (00,000.00).

Okay, here's another situation.

If the functions are moved to a Module, the Label5.Captions don't work.

Is there a substitute command that can be used?

And another situation.

Put text in the field, press enter, the msgbox appears, press ok.

The textbox flashes yellow and then white.

Can we keep it yellow?

you should be able to use:
Forms("UserForm1").Label5.Caption

in place of: Label5.Caption

But the form will be much easier to use in other apps if you leave the code in it, but you know your situation better than me.

the Highlighting test for an operator.  If one is found, the box turns yellow, if one is not, it turns to white.
As far as leavin the textbox yellow,  I'm not sure that would be an easy fix.  That would require at least 1 global variable to test if the message box had popped up, or a change or addition of code.  I will see what I can do, but I probably won't get to work on it to much more today.  I haven't left this computer since 5:30 this morning.  It's been 10 hours and my wife is starting to complain....lol
I'll be back later though...
:-)
ah

I amended the TestHighLight function by having it retest for valid characters.

Function TestHighLight(ctrl As Control)
    Dim b As Boolean
        If InStr(ctrl.Text, "+") > 0 Then b = True
        If InStr(ctrl.Text, "-") > 0 Then b = True
        If InStr(ctrl.Text, "*") > 0 Then b = True
        If InStr(ctrl.Text, "/") > 0 Then b = True
        If b = True Then
            ctrl.BackColor = RGB(255, 255, 0) 'yellow
            'Forms("frmEntry").Caption = "**Formula Mode**"
        Else
            If TestChrs(ctrl.Text) = False Then
                ctrl.BackColor = RGB(255, 255, 0) 'yellow
            Else
                ctrl.BackColor = RGB(255, 255, 255) 'white
                'Forms("frmEntry").Caption = "Amount"
            End If
        End If
End Function

Forms("UserForm").Label5.Caption  did not work - it thinks a sub is being called.

The fix was this: UserForm.Label5.Caption


I always thought that putting functions in modules was the way to keep the house clear of clutter?

Good easy ammendment...looks like it will work...:-)

Is you form named UserForm...or Userform1?

Putting functions in modules is always a good way to keep other objects clear of clutter.  Although that would be better, there is not enough code here to really clutter everything up to awfully much.  I don't want to dissuade you from putting it in a module, that is fine and good.  If you ever want to use this form in another app, it would just be easier if you could just put the form in it and be done.  It would work as is.  But as I said, using a module would be better, but unless you add quite a few more functions or code, I don't think it's that big of a deal.
1 thing I like to do in a case like this, is put all the functions together with the event code on the very top, or the very bottom, with about 10 lines between the two.
You do it however you like....:-)

:-)
ah

You know...you've made so many changes and improvements to this thing without much problem, I think you could of done this whole thing yourself...:-)

:-)
ah

Hey, you did all the work.  I simply started the race, held out the baton and you took it to the finish line.  I may have helped along the way but I bow to your work on this one.

Thank you ahammar for ALL of your assistance.  Look forward to our next project.

I think we have a killer app here!!

Here we go again,

I discovered another issue: enter a mathmatical symbol as the first character and the MsgBox goes in a never-ending loop with the only exit being the 'X'.

The valid characters to START an entry is:

'-' for a credit amount
'.' for a decimal amount
'+' because Excel accepts a plus sign to start a math entry.

Division, multiplication and comma should not be allowed to START or END an entry but is okay otherwise elsewhere in the entry as part of the formula.

I'm playing with the TestChrs function for a workaround this but I'd thought I would let you know.

I tried a test and started my formula with a '*', then tried it with a '/'.  It must have errored out in the GetVal function, because it came back with an error number (1004), but I clicked ok and it came right out of it.  So I can't duplicate your problem exactly.  Which error did you get when you do this?

:-)
ah

I changed the StrChr function a little bit and fixed that problem I think.  I actually copied what you did when you checked the last character and added another select case statement to check the first character and it seems to work.  In case you are still using close to the original that we were using when I left, then maybe this will still work for you:

Function TestChrs(strChrs As String) As Boolean
    strChrs = Trim(Replace(strChrs, " ", ""))
    strChrs = Replace(strChrs, ",", "")
    Dim x As Integer
    For x = 1 To Len(strChrs)
        Select Case Mid(strChrs, x, 1)
            Case "1", "2", "3", "4", "5", "6", "7", "8", "9", "0", "+", "-", "*", "/", ".", "(", ")"
                Select Case Right(strChrs, 1)
                    Case "+", "-", "*", "/", "."
                        MsgBox "Don't end your entry with a mathmatical symbol."
                        TestChrs = False
                        Exit Function
                    Case Else
                        Select Case Left(strChrs, 1)
                            Case "*", "/", "."
                                MsgBox ("You can only start your entry with a number, plus sign, or minus sign.  Please correct")
                                TestChrs = False
                                Exit Function
                            Case Else
                                TestChrs = True
                        End Select
                End Select
            Case Else
                TestChrs = False
                MsgBox ("You have entered an invalid character. Please correct.")
                Exit Function   'Exit once it finds a match
        End Select
    Next x
End Function

Oh...I added parenthises as allowed characters in mine. As you already know, if you don't want them, just take them out of the end of this line:
Case "1", "2", "3", "4", "5", "6", "7", "8", "9", "0", "+", "-", "*", "/", ".", "(", ")"

Good call on the parentheses.  I forgot about that one.

- Be sure to add both parentheses to the TestHighLight function.

- Add the left parentheses to the first Select statement in TestChrs since it will produce a error 1004.  But then again, the right parentheses is a valid ending character but without the left parentheses, the error 1004 will appear.  But I'm not particularly worried about the parentheses checking.

- Take out the period in the second Select statement.  It's okay to start with a period in case an entry is similar to .36

Okay.  I'm pleased to report that everything seems to work.  

Now I'm back to an original observation (see my post Date:03.09.2007 at 03:01PM PST) where I put in an extra TestChrs(ctrl.Text) to retest for invalid characters so the highlight could happen.  

Your current code will accept text and after pressing okay at the MsgBox, the textbox will flash a quick yellow where it should remain yellow.  The extra TestChrs was to retest after the MsgBox for the highlight since the invalid character is still in the textbox after pressing ok.

Somehow the extra TestChrs(ctrl.Text) is producing the looping MsgBox's.

Now here's the culprit doing the quick flash yellow:
It's the KeyCode = 0 in the Compute function.  Take it out and the field remains yellow with the next control getting the focus.

I amended the code to let negative numbers be entered without it triggering the textbox as a formula.  I tried to do with with Instr but couldn't quite the combination down.  Let me know if you can shorten this down.


    Dim x as Integer
    Dim c as Integer
    c = 0
    For x = 1 To Len(ctrl.Text)
        Select Case Mid(ctrl.Text, x, 1)
            Case "-"
                c = c + 1
            Case Else
        End Select
        If c = 1 And Left(ctrl.Text, 1) = "-" Then b = False           '-25
        If c > 0 And Not Left(ctrl.Text, 1) = "-" Then b = True     '25-25
        If c > 1 Then b = True                                                     '25-25-25
    Next x

        If InStr(ctrl.Text, "+") > 0 Then b = True
        If InStr(ctrl.Text, "*") > 0 Then b = True
        If InStr(ctrl.Text, "/") > 0 Then b = True
        If InStr(ctrl.Text, "(") > 0 Then b = True
        If InStr(ctrl.Text, ")") > 0 Then b = True

Discovered another quirk but you may have to expand your demo for the results to be written to a cell in order to duplicate this.

My UserForm has a Save/Close button and writes the textbox values to the cells.

Put in a formula and don't press enter.
The cell will highlight yellow with the formula exposed.
Press Save and let the textbox be written to the worksheet's cells.

What gets written is not the desired results.

Enter 9-9   Result: 39,334.00
Enter 5*4  Result: 5*4

So now we need some mechanism to capture a textbox that has not been calculated upon hitting the Save button on the form.

You need to format the cells as text specifically first otherwise Excel interprets 9-9 as a date.
Regards,
Rory

Yup I agree and the cell is text formatted.

But the key here is to NOT send 9-9 to the worksheet; otherwise, it takes the 'spaz' out of this app.

The textbox needs to be 'calculated' upon textbox exit or similar.  Since the textbox is in '**formula mode**', can this be detected and if active, force the calculation?

I posted this 30 minutes ago, but I put it in the wrong thread...oops... or you would of got this then...

You could do something like this at the start of your save button code, as long as you don't have text boxes other then the ones you are using to put formulas in:

Dim c As Control
For Each c In Me.Controls
    If TypeOf c Is MSForms.TextBox Then
        If TestChrs(c.Text) = False Then
            'set the back color, set the focus
            ctrl.BackColor = RGB(255, 255, 0) 'yellow
            KeyCode = 0
        Else
            'Get the final value, reset the bgcolor & label to default
            c.Tag = c.Text
            c.Text = GetVal(c.Text)
            c.BackColor = RGB(255, 255, 255) 'white
            Label5.Caption = "Amount"
        End If
    End If
Next c

That is the compute function repeated.  It will loop through all the text boxes and compute them.
The line that says:   c.Text = GetVal(c.Text)
will need to be changed so that the text box gets formatted like you have it in your Compute function


.  I tried just calling it, but I couldn't get it to work.  For some reason, this:
Dim c As Control
For Each c In Me.Controls
    If TypeOf c Is MSForms.TextBox Then
         Compute 13, c
    End if
Next c

does not work.  I don't know why because it should.  I think partially because 13 is suppose to be a MSForms.ReturnInteger, but 13 by itself I guess cannot be one.

More good changes you made too by the way...and thank you for the points and the grade on this question earlier too.

:-)
ah

oops..found an error...corrected code:
Dim c As Control
For Each c In Me.Controls
    If TypeOf c Is MSForms.TextBox Then
        If TestChrs(c.Text) = False Then
            'set the back color, set the focus
            c.BackColor = RGB(255, 255, 0) 'yellow
            KeyCode = 0
        Else
            'Get the final value, reset the bgcolor & label to default
            c.Tag = c.Text
            c.Text = GetVal(c.Text)
            c.BackColor = RGB(255, 255, 255) 'white
            Label5.Caption = "Amount"
        End If
    End If
Next c

I amended the code:

I do have other textboxes in the UserForm which the code will test but this is easily solved.

First, set the non-calculating textboxes' Tag to something like "Ignore".

Then test for the tag:

Dim c As Control
For Each c In Me.Controls
    If TypeOf c Is MSForms.TextBox And Not C.Tag= "Ignore" Then
        If TestChrs(c.Text) = False Then
            'set the back color, set the focus
            c.BackColor = RGB(255, 255, 0) 'yellow
            'KeyCode = 0
        Else
            'Get the final value, reset the bgcolor & label to default
            c.Tag = c.Text
            c.Text = GetVal(c.Text)
            c.BackColor = RGB(255, 255, 255) 'white
            Label5.Caption = "Amount"
        End If
    End If
Next c

If the code detects a textbox with the tag set to 'Ignore' then it will simply move on.

In my particular case, I also took out the highlighting to yellow since the code will force the computing of each textbox.

Also, Keycode = 0 does not work but I don't think it's needed.