View Full Version : Sleeper: Using TextBoxes for Numbers
jklaus
06-07-2005, 02:56 PM
Hello,:banghead:
I'd like to use a userform with about a hundred textboxes which will contain numerical data.
I would like to format the data in the textboxes to a specific format (accounting or percent), and then be able to use the textboxes' data as inputs for other textboxes.
The problem I'm running into though is applying the formats. When I apply the format I cannot scrub the format from the data to then use as an input.
Are there such things as numberboxes?? I wish...
Cheers, Jeff
Jacob Hilderbrand
06-07-2005, 03:04 PM
Maybe something like this.
TextBox1.Value = Format(TextBox1.Value, "0.00%")
or
TextBox2.Value = Format(TextBox1.Value, "0")
Or something along those lines. Maybe you can post an attachment that clarifies what you would like to do.
Also with 100s of TextBoxes it would be a good time to use a Class Module to group their code. Are you doing this already?
jklaus
06-07-2005, 03:14 PM
Thanks for the reply DRJ,
I am okay with being able to apply the format to the textbox. However once I apply a format, I can then no longer use the data within the textbox and numerate it.
For example:
TextBox2 = Format(TextBox2.Text, "$##,##0")
TextBox645 = Format(Val(TextBox2) + Val(TextBox10) + Val(TextBox18)
I was hoping this would allow me to be able to numerate (add, subtract, mult, div) my original textboxes. But from what I see Val function stops looking for numbers after the first non number is found. So I can take out $$ sign, but as soon as it runs into a comma it stops, so that doesnt work.
I'm affraid I dont know anything about Class Modules. I would be interested in learning more if you could point me in the correct direction.
Much appreciated.
Jeff
Jacob Hilderbrand
06-07-2005, 03:24 PM
Well we can force Excel to read the values as Doubles (or whatever type you want).
Me.TextBox4.Value = Format(CDbl(Me.TextBox1.Value) + _
CDbl(Me.TextBox2.Value) + CDbl(Me.TextBox3.Value), "$##,##0")
CDbl will make the value into a Double Data Type.
As for Class Modules have a look here:
http://www.vbaexpress.com/kb/getarticle.php?kb_id=85
http://www.vbaexpress.com/kb/getarticle.php?kb_id=86
If you want specific help, can you post your workbook?
Steiner
06-08-2005, 12:23 AM
My VBA does not even like the format ##,##, it expects a . instead: ##.## . The result then is shown as a , as it is set in the region settings.
Steiner
06-08-2005, 12:28 AM
Oops, I guess it should not have been a decimal separator but for the thousands...
Steiner
06-08-2005, 12:46 AM
Ok, I'm pretty sure I confused it again, but if you use String-functions to prepare the pre-formatted textboxes, it should work. I tried it this way:
Option Explicit
Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
TextBox1.Text = Format(TextBox1.Text, "$##,##0")
CalcTb3
End Sub
Private Sub TextBox2_Exit(ByVal Cancel As MSForms.ReturnBoolean)
TextBox2.Text = Format(TextBox2.Text, "$##,##0")
CalcTb3
End Sub
Private Sub TextBox3_Exit(ByVal Cancel As MSForms.ReturnBoolean)
TextBox3.Text = Format(TextBox3.Text, "$##,##0")
End Sub
Private Sub CalcTb3()
Dim x As String, y As String
With TextBox1
'If we have some text inside
If Len(.Text) > 1 Then
'Remove the leading $
x = Right(.Text, Len(.Text) - 1)
x = DotReplace(x)
Else
'Otherwise set it to 0
x = "0"
End If
End With
With TextBox2
If Len(.Text) > 1 Then
y = Right(.Text, Len(.Text) - 1)
y = DotReplace(y)
Else
y = "0"
End If
End With
TextBox3.Text = Format(Val(x) + Val(y), "$##,##0")
End Sub
Private Function DotReplace(x As String) As String
'Kills all dots in a string
Dim i%
Do
i = InStr(x, ".")
If i > 0 Then
'Office97-compatible functions only
x = Left(x, i - 1) + Right(x, Len(x) - i)
Else
Exit Do
End If
Loop
DotReplace = x
End Function
Daniel
jklaus
06-09-2005, 09:30 AM
Well we can force Excel to read the values as Doubles (or whatever type you want).
Me.TextBox4.Value = Format(CDbl(Me.TextBox1.Value) + _
CDbl(Me.TextBox2.Value) + CDbl(Me.TextBox3.Value), "$##,##0")
As for Class Modules have a look here:
http://www.vbaexpress.com/kb/getarticle.php?kb_id=85
http://www.vbaexpress.com/kb/getarticle.php?kb_id=86
If you want specific help, can you post your workbook?
I used the suggestion for forcing to treat as a double which works great - thanks!
Decided to look into the Class Modules, and they do confuse me a bit :yes
But Here is what I've got, but it doesnt quite work yet... But as you can see I have a TON of textboxes so It would be alot easier to use those fancy class modules.
Any help would be appreciated.
Cheers, Jeff
Steiner
06-20-2005, 05:23 AM
Hi Jeff,
maybe this article is something for you:
http://www.jkp-ads.com/Articles/ControlHandler00.htm
Daniel
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.