Simple math using Text box values in a userform

Neville Bentley

Board Regular
Joined
Oct 3, 2007
Messages
101
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
 

Some videos you may like

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

BrianB

Well-known Member
Joined
Feb 17, 2003
Messages
8,127
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
 

Neville Bentley

Board Regular
Joined
Oct 3, 2007
Messages
101
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
 

BrianB

Well-known Member
Joined
Feb 17, 2003
Messages
8,127
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)
 

Neville Bentley

Board Regular
Joined
Oct 3, 2007
Messages
101
Hi there Brian,

Do you have a bit of code as an example?

Rgs

Neville
 

DonkeyOte

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,118
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)
 

Neville Bentley

Board Regular
Joined
Oct 3, 2007
Messages
101
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
 

DonkeyOte

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,118
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.
 

Neville Bentley

Board Regular
Joined
Oct 3, 2007
Messages
101
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
 

DonkeyOte

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,118
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)
 

Watch MrExcel Video

Forum statistics

Threads
1,095,789
Messages
5,446,504
Members
405,404
Latest member
clead

This Week's Hot Topics

Top