Simple math using Text box values in a userform

[Neville Bentley]

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

 

[BrianB]

This is one way :-

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]

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]

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]

Hi there Brian,

Do you have a bit of code as an example?

Rgs

Neville

 

[DonkeyOte]

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]

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]

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.

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]

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]

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:

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)

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)