?
Solved

Getting Numeric Values in and out of Text Boxes

Posted on 2004-11-02
9
Medium Priority
?
305 Views
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?

Help!
0
Comment
Question by:MSProjectGeek
  • 2
  • 2
  • 2
  • +2
8 Comments
 
LVL 48

Assisted Solution

by:Mikal613
Mikal613 earned 60 total points
ID: 12477074
try Val(text1.text)
0
 
LVL 4

Accepted Solution

by:
cachedVB earned 90 total points
ID: 12477150
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!
0
 
LVL 8

Expert Comment

by:ampapa
ID: 12477921
Have you tried converting the numbers to integers then adding them?

newnumber = CINT(oldnumber)
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 6

Expert Comment

by:dambuster99
ID: 12482123
Hi there

i use

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

Steve
0
 
LVL 8

Expert Comment

by:ampapa
ID: 12485387
dambuster,

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

Expert Comment

by:cachedVB
ID: 12816037
I believe that I answered the question thoroughly, atleast from my understanding of it.
0
 
LVL 48

Expert Comment

by:Mikal613
ID: 12816060
i think we all had did it
0
 
LVL 4

Author Comment

by:MSProjectGeek
ID: 12829246
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
  Else
    TruValue = Val(Stemp)
  End If
End Function
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses
Course of the Month16 days, 6 hours left to enroll

850 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