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
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
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
 
Upvote 0
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
 
Upvote 0
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)
 
Upvote 0
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)
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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)
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,245
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top