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.