Sleeper: Using TextBoxes for Numbers

[jklaus]

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]

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]

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]

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]

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]

Oops, I guess it should not have been a decimal separator but for the thousands...

[Steiner]

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]

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]

Hi Jeff,

maybe this article is something for you:

http://www.jkp-ads.com/Articles/ControlHandler00.htm

Daniel