All right. I've got
a user form starter file on the course website it's called FuelForm STARTER.xlsm.
So you can go ahead and download that if you want to work along with me.
I've already created this user form just to save time.
You should definitely feel comfortable and gain
practice with making these from scratch and naming everything.
So everything has a name especially these text boxes.
So, I've put a frame here,
another frame and I have miles and kilometers.
And so, again, you should know how to put
all these buttons on here and so on, and name them.
So let's just go through what everything is named.
This first one is named distance, dist.
This second one is volume,
vol. Then the outputs.
So when we press GO,
we're going to use the inputs here for either mileage or kilometers,
and gallons or liters.
And we're going to output miles per gallon and kilometers per liter for both of these.
So this thing is named mpg,
that's going to be one of the output fields.
This is named kpl.
We've got a GO button and we've got a QUIT button.
I've also got these option buttons.
So we have Miles,
Kilometers that's just km,
gallons, gal and liters.
So, let's go ahead and put the code in here.
The easiest button to code is the QUIT button,
so I'm going to double click on QUIT button.
The name of our form is FuelCalculator.
So there's two ways to quit and I'm going to use unload FuelCalculator.
And that will then delete anything that the user has
put into the text fields of that user form.
One thing I didn't put on here is a reset button.
So if you like you can put a reset button.
The reset button will just unload the FuelCalculator form and then you can show it again.
So, let's go ahead and code the GO button.
So I'm going to double click on the GO button.
Now we're going to start everything in a two-way If Then,
because what we've got is that the distance can either be miles or kilometers.
So this radio button,
remember radio buttons are booleans they're either true or false.
So this is called Miles.
So I'm going to say If miles Then,
and then we're going to do whatever assuming that distance is in miles,
Else we're going to assume that it's in kilometers.
So, inside the Miles we're also going to have another If Then,
and that is If gallons Then,
and then we're going to have an Else.
So, if we haven't selected gallons, remember,
gallons corresponds to this radio button here.
So if gallons isn't selected then we're going to assume that we're in liters.
So I've got that. We have the End If and I'm just going to go ahead and copy this.
Because essentially what we've got is four different options,
we can have miles and gallons,
miles and liters, we can have kilometers and gallons,
or we can have kilometers and liters.
So we're going to have four different cases here.
For each of the four cases,
we're going to have two outputs.
We want to output the miles per gallon that's mpg,
and we also want to output kilometers per liter which is kpl.
The easiest two are Miles per gallon and Kilometers per liter,
so mpg corresponds to this text field here, this text box.
Mpg then is equal to distance divided by volume.
The other easy one to do is Kilometers per liter.
So If we're not Miles,
Then we're down here in Kilometers.
But we're here Kilometers per liter.
And Kilometers per liter Then,
assuming we've checked the right things which sort of directs us to this last one here.
Then Kilometers per liter is going to be distance per
volume because distance will be in kilometers,
volume will be in liters.
Now we can put in the other,
so we're going to have Kilometers per liter.
For each of these four options we're going to have mpg and kpl.
So we just use typical conversion factors.
If you're not quite comfortable with the math here,
these are just conversion factors because for example,
here in this first one,
we have distance is in miles volume is in gallons.
So what I'm doing in the numerator here,
is converting distance in miles to kilometers,
and then I'm taking the volume in gallons and converting that to liters.
So we're just going to do this,
for the other six cases,
we're going to put in these conversion factors.
And so, I've spent some time doing this.
I've also added the format number which will
round the answer to the nearest to the 10th place.
So we've got all of our results here.
And now, I think we're ready to go.
So now I can go to my Fuel Calculator.
Let's just press run here.
Let's do a couple of things so we can kind of test.
So if I go 100 miles in five gallons,
I should get a gas mileage of 20 miles per gallon, which seems right.
Let's do 100 kilometers in five liters,
I don't know if that's on the right order of magnitude,
but we can calculate,
we get 20 kilometers per liter.
And let me just make sure some of these other options work,
miles, and liters, and so on.
And then the QUIT button works.
Now the last things we need to do,
are we need to make it so that we can run this from the Spreadsheet.
In order to run it from the Spreadsheet,
we need to assign a button.
So I need to have a button here.
I'm going to go insert a button.
But before we can insert a button,
the button can only run a normal module.
So I'm going to go ahead and insert a module in which we show the Fuel Calculator form,
and I'm calling this run form.
So now on the spreadsheet,
I can insert a button.
So I'll just put it here. And I can assign that to a run form module.
And then I can rename that something like Calculate.
So when I press the Calculate button,
it's running the run form module.
The run form module opens up our user form,
and we get the user form to open.
And now, I can quit.
The last thing I'm going to do,
is if we're sending this to a client or co-worker,
we want it to automatically open upon when they open up the workbook.
So I can put event handler behind this workbook.
I can go up here to workbook and we enter in this,
and I'm just going to run.
There's two things you can do, you can either do FuelCalculator.Show,
or just type in the name of the run form module,
so when we open up,
we're going to run that and we're going to open up the user form.
Either way, you're going to get the same result.
But you definitely need a module with
FuelCalculator.Show because the button here is assigned to that,
and you can't just open a user form using a button.
So, that's it for just making a basic user form in VBA. Thanks for watching.
