Formatting in Text Boxes

[Mali]

hi - i have an issue with formatting text boxes...... i'm able to format text
boxes with code similar to below....in the userform initialize sub.....there
is another text box which is the sum of TX1 & TX2.....i place the sum line
before formatting and so this works perfectly when the userform opens....

Me.T.Value = Val(TX1 + Val(TX2)

Me.TX1.Value = Format(Me.TX1.Value, "#,##0")
Me.TX2.Value = Format(Me.TX2.Value, "#,##0")
Me.T.Value = Format(Me.TX2.Value, "#,##0")

however, TX1 and TX2 is an input box as well and i've got code to sum 1 & 2
using change event....

Private Sub TX1_Change()
Me.T.Value = Val(TX1 + Val(TX2)
End Sub

this doesnt seem to work becos the sum here is doen off the formatted number
and so the result is not correct....the sum works fine when i remove the
formatting during initialize...

i did a way to....
1. deformat TX1, TX2
2. perform sum of the two and place value back in T
3.reformat TX1, TX2, T after the sum has happened.


thx a lot for your help.

 

[Dave Peterson]

For each textbox:

Retrieve the value from the textbox.
Remove any numberformatting (commas, currency signs, spaces(?))
Check to see if it's still numeric

If it is, then add it to the sum.

Format the sum nicely when you put it back into the textbox T.

 

[Mali]

hi Dave - Thx for the reply.........not sure how i remove formatting though?

 

[Dave Peterson]

Option Explicit
Sub Testme()
Dim myStr As String
Dim myChars As String
Dim iCtr As Long

myStr = " $1,235.45 "
myChars = "$, "

For iCtr = 1 To Len(myChars)
'replace with a space character???
myStr = Replace(myStr, Mid(myChars, iCtr, 1), "")
Next iCtr

MsgBox myStr

End Sub

 

[Rick Rothstein \(MVP - VB\)]

You could use what Dave has posted or you could do the following. The main
problem you are having has to do with the dual functionality of VB's plus
sign (it is either an addition operator for numbers or a concatenation
operator for Strings) and which functionality the plus operator assumes for
Variant arguments. If you "add" two variants together (whether they are
numbers or text), VB treats them as text and concatenates the result. That
is why you are using the Val function... because TextBoxes return Variants,
you need the Val function to convert the strings that look like numbers in
them back to numbers. Unfortunately, Val doesn't know that comma, dollar
signs and what not can be part of numerical values and it truncates and
entry at an internal character that is not a dot (US type decimal point).
But, if you perform a mathematical operation on a Variant containing a
number, it will return a number for its answer. So, if you multiply the
numerical String contents of a TextBox by one, it will revert back to a
number and the plus sign will work as an addition operator if the second
operand is also a number (even if that number is a formatted numerical
String value). So, you could, in its simplest form, do this in your Change
event code...

Private Sub TX1_Change()
If Len(TX1) = 0 Or Len(TX2) = 0 Then Exit Sub
TX3.Value = 1 * TX1.Value + TX2.Value
End Sub

Where the first line makes sure there is something in each TextBox. However,
I think you should make this code more robust by checking if the entries in
TX1 and TX2 are, in fact, both numbers.

Private Sub TX1_Change()
If Not IsNumeric(TX1.Value) Or Not IsNumeric(TX2.Value) Or _
Len(TX1.Value) = 0 Or Len(TX2.Value) = 0 Then Exit Sub
TX3.Value = 1 * TX1.Value + TX2.Value
End Sub

Rick

 

[Mali]

thx a tonne Rick !!!...that worked great....i actually put the whole summing
code in a seperate sub and then called it into the change event as i had 10
boxes with user entry and the total needed to change for evey text
change...also i was able to format it back to the way i wanted after the sum
action had happened.

thx again!!!

 

[Mali]

hi Rick - i've got some issues with this again now.....in my case i ahve a
few text boxes (5) and a text box tha sums everythign up........everything
works fine when there are some values either numbers or zeroes in the
boxes...however in case one fo the boxes doesnt have any value (no zero as
well) then in those cases the sub exits becos of the not is numeric
function....

what should i do to resolve this?

thx a lot for your help so far....

thx a tonne Rick !!!...that worked great....i actually put the whole summing
code in a seperate sub and then called it into the change event as i had 10
boxes with user entry and the total needed to change for evey text
change...also i was able to format it back to the way i wanted after the sum
action had happened.

thx again!!!