User Forms 3: Calculations
12 Comments


I am going to show you in this screencast
a simple calculation within a UserForm, so this is just about one of the most basic UserForms that we could actually set up. Before I put a UserForm I often times just
draw a schematic of how I want this thing to work before going straight into the editor. So I have this initial UserForm and I want
the user to be able to enter an A, enter a B, and then when they press calculate it will
actually spit out the product of those two numbers. When the user presses reset it will wipe those
clean, A and B, and then the user can actually modify those and start over, and then as always
we want to put a “Quit” button so when the user presses quit the user form will disappear. We start out by going into Excel, alt+F11
to bring up the editor, and then I am going to go over here and insert a UserForm, and
it looks something like this. For this example we want to have some text,
so we’ll have the label here, you can change it, and then you can put a text box here for
what the user can input there, and you can also add buttons here. Another thing you need to do is you need to
name anything that actually gets input from the user. So this is a text box. The default name is textbox, but we might
make this something more descriptive like “A Input”. So I’ve gone ahead and formatted everything
nicely, I just wanted to show you exactly what I’ve got. So here we have this is named “A Value”, this
is named “B value”, and this is going to be named “Product”. We have a calculate button, I’ve named that
“Calculate” button. We have “Quit” button and “Reset” button. In order to calculate the product we’re going
to take the value in A times B’s value, and when the button calculate is pressed the product
is going to be calculated. We can double click on this button, and this
is where we’re going to put the code in there to calculate the product. Product is going to be equal to A value times
B value. It’s as easy as that. So let’s go back to the UserForm. We also want to put in a reset so that will
wipe everything clean, and there’s a statement “Unload” and then user your UserForm name:
“Unload UseForm1” basically removes everything and shuts that UserForm down. Now we want this to actually pop back up because
we’re just resetting it. To bring up that user form again we type in
“UserForm1.Show”. Now the only other button we have is “Quit”,
so let’s double click on “Quit”, and we can type in the command “UserForm1.Hide” and
that will actually quit the UserForm. So we’re almost ready to go, the only problem
now you can’t just run a UserForm, you actually have to run it from a module, so let’s go
in here, we’re going to insert a module, and I am going to write a sub “SimpleProduct”,
I just want to show the UserForm, so “UserForm1.Show”. When we run module 1 it will open up and show
that UserForm and the user can do something with it. One last thing, I am going to transfer back
over to Excel, I am going to go up here to “Developer”and “Insert” button, and I am going
to sign this button to our simple product, I can just make this be “Go”, I am going to
press “Go:, we can enter a 5 for example, a 4, we can click calculate, it calculates
a product, we reset it wipes everything clean and then re-shows the form, so let’s see if it
works for something different, 20 times 10 we can calculate the product, and then I can
reset and I can quit. This is just a very easy way to create a simple
UserForm that uses some sort of calculations in VBA.

12 thoughts on “User Forms 3: Calculations

  1. Please check out our Engineering Computing playlist for more screencasts with different options.  https://www.youtube.com/playlist?list=PL587E12CFE1977F08

  2. Hello My Name is Towsif .
    I need an Help,
    I have made a Apparel costing software where is few maths as u know. Know my
    question is how can i calculate in one texbox using 7 other texbox. Items are bellow ..
    {((sl+bl+Allw10)*(1/2Chest+AllWs4))*2*gsm165*doz or pes/10000000} +wastage10%
    please help me….

  3. sir I want to display my data in multiline textbox from range a1:c5. can you please help to display this whole range in textbox using excel VBA
    my whatsapp#+91-9026413903

  4. It’s really very simple indeed! Thanks. But what is the formula to multiply two textboxes with decimals? Is that the same?

  5. Can you help me how to link a combo box to a command button? The scenario is that we need to get input from 2 input boxes and select addition or multiplication in combo box, later on clicking a command button the operation should be performed and the result should be shown in another Text Box.

  6. Hello. Great video.
    I was just wondering if you could help me out. I am looking to create a UserForm that has 3 user inputs (all numbers) these numbers are automatically multiplied by another number and the result is automatically shows in another text box. Is this possible?

Leave a Reply

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