Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    4 Star Lounger
    Join Date
    Sep 2002
    Location
    Stafford, Staffordshire, England
    Posts
    585
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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.
    <font color=blue><font face="Script MT Bold"><big>Rob</big></font face=script></font color=blue>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #3
    4 Star Lounger
    Join Date
    Sep 2002
    Location
    Stafford, Staffordshire, England
    Posts
    585
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calculations Using TextBoxes (97/2k)

    Many thanks, Hans, for your quick response. It will be of tremendous use.
    <font color=blue><font face="Script MT Bold"><big>Rob</big></font face=script></font color=blue>

  4. #4
    4 Star Lounger
    Join Date
    Sep 2002
    Location
    Stafford, Staffordshire, England
    Posts
    585
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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?
    <font color=blue><font face="Script MT Bold"><big>Rob</big></font face=script></font color=blue>

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    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. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Calculations Using TextBoxes (97/2k)

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

  7. #7
    4 Star Lounger
    Join Date
    Sep 2002
    Location
    Stafford, Staffordshire, England
    Posts
    585
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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.
    <font color=blue><font face="Script MT Bold"><big>Rob</big></font face=script></font color=blue>

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #9
    4 Star Lounger
    Join Date
    Sep 2002
    Location
    Stafford, Staffordshire, England
    Posts
    585
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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
    <font color=blue><font face="Script MT Bold"><big>Rob</big></font face=script></font color=blue>

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #11
    4 Star Lounger
    Join Date
    Sep 2002
    Location
    Stafford, Staffordshire, England
    Posts
    585
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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>
    <font color=blue><font face="Script MT Bold"><big>Rob</big></font face=script></font color=blue>

  12. #12
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    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. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #14
    4 Star Lounger
    Join Date
    Sep 2002
    Location
    Stafford, Staffordshire, England
    Posts
    585
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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.
    <font color=blue><font face="Script MT Bold"><big>Rob</big></font face=script></font color=blue>

  15. #15
    4 Star Lounger
    Join Date
    Sep 2002
    Location
    Stafford, Staffordshire, England
    Posts
    585
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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?
    <font color=blue><font face="Script MT Bold"><big>Rob</big></font face=script></font color=blue>

Page 1 of 2 12 LastLast

Posting Permissions

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