Perform Calculations on Data in Text Boxes Without Command Button

How to perform calculations on Data in Text Boxes on User Form without a Command Button.

Watch the video below:

Calculate with numerical values in 2 text boxes on user form

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)
MsgBox total
End Sub

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.

auto calculate with numerical values in 2 text boxes on user form
Auto calculate with numerical values in 2 text boxes on user form

Published by

Dinesh Kumar Takyar

Welcome to exceltrainingvideos.com! My aim is to help you learn MS Excel including VBA. I always felt that a good way to learn something was to find solutions to problems in that domain. That is why I share these Excel videos with you. Mostly these are questions asked during my corporate training sessions. I've been training individuals and companies in computers since 1991. I did my B. Sc. (Vordiplom), M. Sc. (Diplom) and Ph.D. (Dr. rer. nat.) from Hamburg, Germany. The best thing about solving some of my visitor's questions is that I also gain new insights. For more Excel VBA solutions you may like to visit my YouTube channel: http://youtube.com/familycomputerclub For a structured Excel VBA training course online you can visit: https://www.youtube.com/excelvbaonline

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.