Getting Numeric Values in and out of Text Boxes

I have a form in Project VBA that has uses textboxes to display and edit numbers.

When I populate the fields initially, I use the following syntax...

TextBox1 = Format(NumberVariable, "###,###,##0;(###,###,##0)")

which displays the information with no decimal places.

I have come up with a way to keep the formatting the same when the user edits the data as follows
Dim MyFormat As String
Dim LastVal As Long

MyFormat = "###,###,##0;(###,###,##0)"

' This routine remembers the contents of a cell when it's clicked for editing
Sub TextBox1_Click()
  LastVal = TextBox1
End Sub

' This routine forces input to be numeric
Sub TextBox1_Change()
  If Not IsNumeric(TextBox1) Then
    TextBox1 = LastVal
  End If
  TextBox1 = Format(TextBox1, MyFormat)
  LastVal = TextBox1
End Sub

What is causing me problems is when I try to add the contents of the TextBoxes to create a total, the total behaves as though it's treating the TextBoxes as strings (Duh!) and I get long strings of numbers and commas rather than the total value.

Surely there's a way to force the boxes to act as numbers or is there an better object to capture and display numbers?

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

try Val(text1.text)
Try using replace.

dim numvalue as string
numvalue = Replace(TextBox1, ",", "");

Then if you want to get each number individually u can do

dim splt
splt = split (numvalue, ";(")
num1 = val(splt(1))
num2 = val(Left(splt(1), (len(splt(1)) - 1))) 'to get rid of the ")" at the end

I hope this is what you need!

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Have you tried converting the numbers to integers then adding them?

newnumber = CINT(oldnumber)
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Hi there

i use

cdbl(text1.text) + cdbl(text2.text)


The format is without decimal places so a "CDBL" really would't be needed.

Now that I think about it I you probably better of converting into a long as a integer has a cap at 32,767.

newnumber = CLNG(oldnumber)
I believe that I answered the question thoroughly, atleast from my understanding of it.
i think we all had did it
MSProjectGeekAuthor Commented:
Well I sorted this out mainly based off cachedVB's input but with the assistance of Mikal613 with the Val statement.  I've award split points accordingly - cahedVB gave me the replace function that can remove the commas in the string.

What I actually did in the end was to create variables for each of the text boxes and then used the text boxes really as displays.  As the user entered information, I restricted the input to numbers, commas, and backspace using the following routine...

Private Sub C1_kepress(ByVal keyascii As MSForms.ReturnInteger)
  If (keyascii < 48 or keyascii > 57 And keyascii <> 44 and keyascii <> 8 Then keyascii = 0
End Sub

with a routine for every text box - the C1 is simply the textbox name with cells named in an Excel-like format and the corresponding variable is T_C1 which is defined as a Long Integer.

Then for each text box (there were only 9 that input was allowed in), I have a routine that performs the necessary work as follows...

Private Sub C1_AfterUpdate()
  UpdateNeeded = True ' this is a trigger on exit to see if anything has changed
  Memstore = T_C1 'remembers' the old value in case the new one causes an overflow error in the totals'
  InField = 1
  T_C1 = TruValue(C1)
  C1 = Format(T_C1, MyFormat)
  RecalcForm ' calls my recalc routine
End Sub

The TruValue Function is really the place where the answers were used, here it is...

Function TruValue(IPString As String) As Long
  Dim Stemp As String
  Stemp = Replace(IPString, ",", "") ' replaces commas with nothing
  If Len(Stemp) > 9 Then
    MsgBox "Error - Exceeds max permitted value of 999,999,999"
    TruValue = MemStore ' reurn original value
    TruValue = Val(Stemp)
  End If
End Function
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.