How to perform calculations on Data in Text Boxes on User Form without a Command Button.
Watch the video below:
One user asked if there is a way to auto calculate with numerical values in 2 text boxes on user form without command button.
We’ll click on the Developer tab and then click on Visual Basic to open the the Microsoft Visual Basic for Applications Editor window. Next we click on the Insert tab and select Userform. A Userform1 is created. In this Userform1 we add two text boxes TextBox1 and TextBox2. Since we wish to perform calculations like addition, multiplication and division, we can do that when we enter the data in the TextBox2 and leave it since the user doesn’t wish to use a command button.
When we double-click on the TextBox2 we are offered the change event. We would prefer to use the Exit event i.e. after entering the data in TextBox2 we would press the Enter key and the calculation should happen.
To add the values in the two text-boxes we would write the following code between the two lines of code that we get when select the Exit event:
Private Sub TextBox2_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim total As Long
total = Val(TextBox1.Value) + Val(TextBox2.Value)
The numbers that we enter in our text-boxes are strings or text. VAL stands for VALUE in VBA terminology. This function converts the string in our text-boxes to an actual number and we can perform arithmetic operations easily. Now we can also force the user to enter only numerical values into the text boxes and validate the entries.
We also need to inform the user about the action we have performed. So we use the MsgBox command to issue a message to the user containing the result of our arithmetic manipulation as shown in the training video.