Getting Numeric Values in and out of Text Boxes

Posted on 2004-11-02
Last Modified: 2010-05-02
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?

Question by:MSProjectGeek
    LVL 48

    Assisted Solution

    try Val(text1.text)
    LVL 4

    Accepted Solution

    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!
    LVL 8

    Expert Comment

    Have you tried converting the numbers to integers then adding them?

    newnumber = CINT(oldnumber)
    LVL 6

    Expert Comment

    Hi there

    i use

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

    LVL 8

    Expert Comment


    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)
    LVL 4

    Expert Comment

    I believe that I answered the question thoroughly, atleast from my understanding of it.
    LVL 48

    Expert Comment

    i think we all had did it
    LVL 4

    Author Comment

    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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Training Course: Java/J2EE and SOA

    This course will cover both core and advanced Java concepts like Database connectivity, Threads, Exception Handling, Collections, JSP, Servlets, XMLHandling, and more. You'll also learn various Java frameworks like Hibernate and Spring.

    Introduction While answering a recent question ( in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
    Article by: Martin
    Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
    As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
    Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    6 Experts available now in Live!

    Get 1:1 Help Now