# Thread: Calculations Using TextBoxes (97/2k)

1. ## Calculations Using TextBoxes (97/2k)

I am trying to create probability trees, using userforms, as a student aid. I simply need to know how to set the properties of textboxes so that I can refer to them in VBA to show the results of simple calculations. Eg Textbox3.value = Textbox2.value*Textbox1.value. I would also like the option to display the results to either 2 decimal places or as a fraction.
I hope that I've explained the problem with enough clarity.
Thanks in anticipation.

2. ## Re: Calculations Using TextBoxes (97/2k)

Text boxes in userforms don't have a Format property, unlike their cousins in Access. So you'll have to do it yourself in code. Since the value displayed in the text boxes will be different from the underlying values, you need to store the underlying values somewhere. You can use variables for this, or cells in a worksheet (but you can't use the ControlSource property of the text boxes, because then a change in the displayed value would cause a change in the underlying value.)

Code could look more or less like this:

Dim var1, var2, var3

Private Sub TextBox1_AfterUpdate()
var1 = TextBox1
TextBox1 = Format(var1, "0.00")
UpdateTextBox3
End Sub

Private Sub TextBox2_AfterUpdate()
var2 = TextBox2
TextBox2 = Format(var2, "0.00")
UpdateTextBox3
End Sub

Private Sub UpdateTextBox3()
var3 = var1 * var2
TextBox3 = Format(var3, "0.00")
End Sub

Formatting as a fraction involves using the TEXT workbook function, for example:

TextBox3 = Application.WorksheetFunction.Text(var3, "? ?/??")

Note: since Value is the default property of a text box, you don't have to mention it explicitly.

3. ## Re: Calculations Using TextBoxes (97/2k)

Many thanks, Hans, for your quick response. It will be of tremendous use.

4. ## Re: Calculations Using TextBoxes (97/2k)

Hans, I must be doing something stupid. I used the code from your reply exactly as written but TextBox3 is not updated. It displays 0.00 no matter what values I insert into the other TextBoxes. I looped through the code and I discovered that when the "Private Sub UpdateTextBox3()" procedure is run, var1 and var2 are not passed on. They are now empty. Any ideas?

5. ## Re: Calculations Using TextBoxes (97/2k)

I use XL97
I created a userform, and added 3 text boxes
I copied Hans' code and pasted it into the userform object
When I "show" the userform it works exactly as expected.

What are you doing?

Steve

6. ## Re: Calculations Using TextBoxes (97/2k)

Here is my test workbook. The userform contains the code I posted.

7. ## Re: Calculations Using TextBoxes (97/2k)

Thanks Hans for the Workbook. My error (of course) was not knowing where to declare the variables var1 etc.
I added the Dim statement to the After_Update code. Still, I've learned something new.

8. ## Re: Calculations Using TextBoxes (97/2k)

In this case, the variables are shared by all procedures in the module behind the userform, so they must be declared at the top of the module, as you learned the hard way.

If you declare a variable as public at the top of a (standard) module, it can be used in all procedures and functions in all modules.
If you declare a variable as private at the top of a module, it can be used in all procedures and functions in that module, but not in procedures and functions in other modules.
If you declare a variable within a procedure or function, it can be used within that procedure or function. It will lose its value when the procedure or function finishes, unless you declared it as static; in that case it will keep its value between calls.

9. ## Re: Calculations Using TextBoxes (97/2k)

Thanks for the info, Hans, and also for your succinct explanation of Number Pictures. I have another request.
I am experimenting with different ideas concerning the probability trees. I have 2 textboxes formatted to display fractions embedded in a worksheet. Ideally, I would like to have their decimal equivalents displayed in 2 separate cells on the same sheet. Is this possible?
TIA

10. ## Re: Calculations Using TextBoxes (97/2k)

Hello Rob,

I presume that the user can't type directly into the text boxes; their content is controlled from somewhere else. You can write code in the Change event of the text boxes: while in design mode, right click a text box and select View Program Code. The code would look more or less like this:

Private Sub TextBox1_Change()
Range("A1") = ... ' insert the "source" of the numeric value of TextBox1 here.
End Sub

You can format the target cell (A1 in my example) any way you like. Post back if this is off the mark; I'm not sure I understood your description correctly.

11. ## Re: Calculations Using TextBoxes (97/2k)

The idea is that the textboxes are used by pupils to enter their response to a simple probability problem as fractions. I originally thought these values could be linked to cells on the sheet which are then refenced by functions. However, after playing around for a while I realised that, as far as I could see, Excel always considers them to be text in these situations, unless someone knows of a way round this.
So the problem boils down to finding a way of displaying the values keyed into the boxes as 'real' numbers in cells.
(I'm not sure that this is a better description) <img src=/S/doh.gif border=0 alt=doh width=15 height=15>

12. ## Re: Calculations Using TextBoxes (97/2k)

If you use something like what Hans mentioned, [range ("a1") = ], even if it is a string, if it LOOKS like a value, excel will convert it in the cell. Even many fractions excel will convert to decimal.

What type of "values" are you using that do not get converted? You might have to write a routine to convert them yourself and enter that into the cells.

Steve

13. ## Re: Calculations Using TextBoxes (97/2k)

You *can* have text boxes on a userform bound to cells in a worksheet. If the user enters a fraction such as 3/5 into the text box, the associated cell will display 3/5, but the text box will display 0.6. If I understand correctly, you want the cell to display the decimal value and the text box to display the fraction. I don't know of a way to do that with "bound" text boxes. With an unbound text box, you'd have to write your own parser to convert a string like 5/8 to its numeric equivalent 0.625. This function (from Microsoft) should help:

Function Frac2Num(x)
' Parses a standard fraction of the form "a/b" or "a b/c"
Dim Temp As String
Dim intPos As Integer
Dim n As Double
Dim Num As Double
Dim Den As Double
If VarType(x) < vbInteger Or VarType(x) = vbDate Then
Frac2Num = Null
ElseIf VarType(x) <> vbString Then
Frac2Num = x
Else
Temp = Trim\$(x)
intPos = InStr(Temp, " ")
If intPos = 0 Then
If InStr(Temp, "/") = 0 Then
n = Val(Temp)
Else
n = 0
End If
Else
n = Val(Left\$(Temp, intPos - 1))
Temp = Mid\$(Temp, intPos + 1)
End If
intPos = InStr(Temp, "/")
If intPos <> 0 Then
Num = Val(Left\$(Temp, intPos - 1))
Den = Val(Mid\$(Temp, intPos + 1))
If Den <> 0 Then
n = n + Num / Den
End If
End If
Frac2Num = n
End If
End Function

You can use this in the AfterUpdate event procedure of the text boxes.

14. ## Re: Calculations Using TextBoxes (97/2k)

Hans,

I'm extremely grateful for the time and trouble you have taken with this request. I will employ it over the weekend.

15. ## Re: Calculations Using TextBoxes (97/2k)

Hi Hans

Despite your best efforts, I am unable to get the code to work. VB seems to think that the textbox is empty (Frac2Num = Null) after typing in a fraction, then jumps to the end of the code. I must be missing something. Would you test the code so that I know that it's my error?

Page 1 of 2 12 Last

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•