Simple math using Text box values in a userform
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Simple math using Text box values in a userform
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Oct 2007
    Posts
    101
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Red face Simple math using Text box values in a userform

    Hi Out there,

    I am looking for some examples of simple math (Adding, Subtracting, Dividing & Multiplying) using Text box values in a userform.

    I have 6 textboxes. 3 of them for data entry and 3 of them for calculating from the other three.

    I am not looking to work of any worksheet, it must be done in the user form.

    Any Idea's

    Cheers Neville

  2. #2
    Board Regular
    Join Date
    Feb 2003
    Location
    Luton, England.
    Posts
    8,127
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Simple math using Text box values in a userform

    This is one way :-
    Code:
    Private Sub CommandButton1_Click()
        '- convert text values to Double precision. Add. Convert result to a string
        TextBox3.Value = CStr(CDbl(TextBox1.Value) + CDbl(TextBox2.Value))
    End Sub
    Regards
    BrianB (using XL2003 & 2010)
    www.cycleofgrowth.com
    Most problems occur from starting at the wrong place.
    Use a cup of coffee to speed up all Windows processes.
    It is easy until you know how.
    **FORMATTED/COMMENTED CODE IS MORE LIKELY TO GET A REPLY

  3. #3
    Board Regular
    Join Date
    Oct 2007
    Posts
    101
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Smile Re: Simple math using Text box values in a userform

    Brian B,

    Thanks for your reply. That piece of code will be great for a command button.
    But how would it look like as the user was entring into other text boxes?

    In other words I have a user form and as the user enters data there are some specific fields that get calculated automatically with the data from the various text boxes that are entry fields.

    Does that make sense?

    Rgs

    Neville

  4. #4
    Board Regular
    Join Date
    Feb 2003
    Location
    Luton, England.
    Posts
    8,127
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Simple math using Text box values in a userform

    I don't recommend it, but you can try putting the code into the textbox_change event. The problem is that the code runs each time a letter or number is entered.

    (Doubleclick the textbox in the form setup)
    Regards
    BrianB (using XL2003 & 2010)
    www.cycleofgrowth.com
    Most problems occur from starting at the wrong place.
    Use a cup of coffee to speed up all Windows processes.
    It is easy until you know how.
    **FORMATTED/COMMENTED CODE IS MORE LIKELY TO GET A REPLY

  5. #5
    Board Regular
    Join Date
    Oct 2007
    Posts
    101
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Cool Re: Simple math using Text box values in a userform

    Hi there Brian,

    Do you have a bit of code as an example?

    Rgs

    Neville

  6. #6
    MrExcel MVP DonkeyOte's Avatar
    Join Date
    Sep 2002
    Location
    Suffolk, UK
    Posts
    9,118
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Simple math using Text box values in a userform

    if you only wanted to fire when focus moves away from the entry text box you could use the textbox control exit event ?

    also if some of your textboxes are read-only could you not use labels instead ?
    (possibly reduce end-user confusion)
    Does my a$$ look big in this picture ?

  7. #7
    Board Regular
    Join Date
    Oct 2007
    Posts
    101
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Red face Re: Simple math using Text box values in a userform

    Hi there Laws10,

    What would be ideal is if someone could show me the code (I am not a very good coder - I manage to bumble through the code and get it to work).
    I am not formally trained in VBA and I cannot find a good example of the code anywhere on the web or in my books - the books just do not cover this subject well enough. The books assume all users will revert back to the spreadsheet and not use a form for the complete format of gathering data.

    So a bit of code as an example would really help.

    Cheers

    Neville

  8. #8
    MrExcel MVP DonkeyOte's Avatar
    Join Date
    Sep 2002
    Location
    Suffolk, UK
    Posts
    9,118
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Simple math using Text box values in a userform

    Neville, below is a sample whereby if user tabs out of Textbox1/2 Excel will try and put sum of TB1 + TB2 in Textbox3, however, if either TB1/2 is non-numeric it will just put an Error Notice in textbox3 as it can't calculate the values.

    Code:
    Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    On Error GoTo InvalidTypes:
    TextBox3.Value = CStr(CDbl(TextBox1.Text) + CDbl(TextBox2.Text))
    Exit Sub
    InvalidTypes:
    TextBox3.Value = "Non-Numerics in Either Textbox 1 or Textbox 2"
    End Sub
    
    Private Sub TextBox2_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    On Error GoTo InvalidTypes:
    TextBox3.Value = CStr(CDbl(TextBox1.Text) + CDbl(TextBox2.Text))
    Exit Sub
    InvalidTypes:
    TextBox3.Value = "Non-Numerics in Either Textbox 1 or Textbox 2"
    End Sub
    Regards learning VBA - I'd say the vast majority of us here are self taught (ie learn by doing) - running into the same problems you did in terms of finding good reference books / courses ... keep plugging away and it will soon make sense... that's why this place is good as you can pick up stuff as you go along... I still do.
    Does my a$$ look big in this picture ?

  9. #9
    Board Regular
    Join Date
    Oct 2007
    Posts
    101
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Simple math using Text box values in a userform

    Lasw10,

    Many thanks for that - I am of the "Monkey see Monkey do" school - I will add this titbit to my Code library.

    One final question - If one of those value entry boxes is a List box is there a special way I have to treat the variable if I am using it in a calculation?

    Cheers

  10. #10
    MrExcel MVP DonkeyOte's Avatar
    Join Date
    Sep 2002
    Location
    Suffolk, UK
    Posts
    9,118
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Simple math using Text box values in a userform

    Well I would say if you're going to have a lot of controls firing the same code (ie three controls all doing the same calculation as the linked together) I'd be inclined to do something along the lines of:

    Code:
    Private Sub ComboBox1_Change()
    Call TB3_Value
    End Sub
    
    Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    Call TB3_Value
    End Sub
    
    Private Sub TextBox2_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    Call TB3_Value
    End Sub
    each control invoking a common routine so you only have to update one piece of code if you decide to alter the calculations etc...

    So the above all call TB3_Value and here would be TB3_Value routine (stored alongside the above)

    Code:
    Sub TB3_Value(Ctrl As String)
    On Error GoTo InvalidTypes:
    TextBox3.Value = CStr(CDbl(TextBox1.Text) + CDbl(TextBox2.Text) + CDbl(ComboBox1.Value))
    Exit Sub
    InvalidTypes:
    TextBox3.Value = "Non-Numerics in Either Textbox 1, Textbox 2 or ComboBox1 "
    End Sub
    Note to determine the selection in the combobox control - combobox1.value will do it (where combobox1 = control name)

    As for your mantra - I hope you're over seven years old as I thought it was at that age that the ability to "learn" was lost ?
    (It is by this rule that we can determine all sales staff to be > 7)
    Does my a$$ look big in this picture ?

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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