In the controls in Excel files video we discussed how to put controls into your excel file and the different
types of controls that are available.
For example I've inserted a button control into this particular excel file and I can tie some code to
this button that will run when we click on it and you can see a name of this button add employee and
over on the right here I have a table that is just an example of a table you might use to track all
of your employees so we can leave this Excel file just like this and let each individual user come in
and add a row to this table and fill it out manually.
Alternatively we could create a form that will actually pop up on the screen when we click on Add employee
and we will fill out the form and the form add that information to the table for us.
And there are a couple of reasons we might do this.
One is to eliminate any human error we can build some check some error checks into the form that'll
make sure we actually did enter a date for example or one of the roles that got entered is actually
a valid role and we're only selecting full a part time if we wanted to generate an employee I.D. for
example that's something that we could do encode into there's a couple different reasons that we might
use code for something like this.
This is a pretty simple example of what we might want to build a user form for.
And so that's what we're going to be looking at in this video.
So the first thing that we're gonna need to do is actually insert a form into our excel file so we'll
go up to the developer tab and open up our visual basic and now up here and insert where we usually
select module we're going to click on user form instead and then we can talk about this screen for a
second.
So this block you see here this is the actual form that you've inserted.
This is the window that will pop up on the screen.
This is what your users will see and interact with.
So this is what we're gonna be working on and what we're going to be building this toolbox over on the
right side of my screen.
This contains all of the different controls that we'll be talking about in this video and we'll go through
these one by one.
If your tool box doesn't pop up automatically or if it ever disappears you can get it back by clicking
on this tool box.
Button up at the top of your screen.
The other thing to pay attention to is the properties window over on the left which will cover in more
depth since as a lot of really important properties that you might want to adjust.
And we'll do that in a later video.
So first let's expand our form here.
I'll just click on the corner and drag it out.
So we have a little bit more room to work with and we can take a look at all of these different controls
in our tool box.
So the first one to look at is this little A.
And this just means label.
So we'll click on it.
Go ahead draw it on our form across the top like this and we can see it shows up and it says label 1
if we double click into this box if you just click a second time into this box you'll see you'll be
able to actually type in here.
So we'll just type this is our example form something like this and then we'll click out of this we
can see our tool box will pop back up.
I'm going to move it over to the side of my screen do some rearranging so I don't have to keep finding
it again.
And so this is just the label.
And what this means is this actually doesn't perform any purpose on our form.
This isn't something that the user will be able to change or interact with it's just text that's going
to show up.
That will help us give instructions to the user or maybe clarify what this particular box on the form
is supposed to be.
So we definitely use labels a lot.
And again we'll talk about this property window down here later but there are some things in here that
you'll probably want to adjust.
So for now we'll just leave our little example label up there.
I'm going to click back onto the form to get out of the example label.
Next I'm going to click on this which is a text box and I'll just click somewhere on the form drag it
out create a little text box.
Now this is something that the user actually will interact with.
This is an empty text box that they're going to be able to type into and again and will cover the properties
later.
But just as an example we have this text box.
Now we have lists box and combo box which are these two right here and there's a little bit of a difference
between the two so combo box and we'll go ahead and just draw a combo box onto our form and we can just
make it about the same size as our text box and we can see it has this little dropdown arrow right here.
So with a combo box we can preset this list to be maybe on our form.
This is like our role so maybe we have manager designer product support something like that and we can
select this dropdown and we'll see that list populate and we can select from this list.
Now on a combo box you can also type your own answer into the box itself and there's a setting that
we can turn off to control that but that's the primary difference between combo box and a list box so
if we draw a list box onto the form now and we'll draw this one a little bit bigger the list box you'll
see doesn't actually have this dropdown arrow and that's because we're going to have a whole bunch of
options that appear in this box if we have too many options they can scroll down using the vertical
scroll bar that'll show up on the right here.
But generally speaking a list box takes up a lot more space on your screen.
And so a lot of people will just use combo boxes and we can control the property that allows the user
to custom enter their own information.
If we don't want them to be able to do that next we have a checkbox which is pretty self-explanatory.
You can double click into this control here to change whatever the checkbox is so we'll say maybe full
time or something like that and we'll click back out of it next.
We have this option button and the difference between a checkbox and an option button is that you typically
have multiple option buttons on here.
So maybe this time we have one that says full time and one that says part time and in our code we can
actually set this up so that these options are tied to one another.
So if we select full time part time automatically is un selected or if we select part time full time
is automatically on selected.
So if you have a couple of different options and you don't want to use something like a combo box to
select from them you might use an option group.
And again this is all something that you would want to group together whereas the checkbox is something
that is a standalone item you can have multiple checkboxes on your form and you can interact with them
individually and get different results.
Next we have this toggle button and what the toggle button does is you can either click it and it will
stay clicked or you can click on it again and it will actually on click on this button.
So this might be something where you have two different versions of the form maybe one version of the
form is for managers and one version of the form is for regular employees.
So you might click this toggle button to toggle your manager side of the form.
And at that point we can go into code and we can say we want you to hide this field in this field and
we want you show these new fields over here.
And that's typically what you would use a toggle button for.
It's a little more complicated than you really need in Excel VBA so you won't see this very often but
it does have a unique application that might fit a specific purpose for you next we have this frame
and this can be good for organizing different things so if I draw this frame on here you can see it
shows up with the text frame one and we can change this in the property section of our form over here
and caption and again we'll cover the properties later on that just as an example we'll say full or
part time something like this and maybe we want to put our option groups into this frame to help organize
our form a little bit so we can drag them on there.
And now these option groups are part of this frame.
So we can see if I drag this frame it's actually going to pull my option groups with it.
So this is a super helpful tool for organizing your form makes it easy to shift things around later.
And it also makes it easy for the user to identify all of this information is grouped together.
This is one subsection of my form so it's useful for cleaning up your form as well.
So I'll pull my tool box back over here and the next thing we're going to look at is a regular command
button so I'll go ahead and insert one of these on the form and we can see right off the bat.
It looks very similar to the toggle button and also to the command buttons that we can insert onto our
regular excel file.
And this serves a regular same sort of purpose.
So if we click on this button we can tie code to it that's going to run after we click on this.
So we'll go ahead and create a closed button and then I'll insert another one and I will call this button
add and I found it's best to generally go ahead and give your users a close button even though they
could also exit out of the form by clicking on the actual exe itself.
And that's just because it gives them something that they feel is an alternative to the actual Add button.
So if they don't want to add this information they don't want to save this information.
They can use the closed button instead and know that none of this is going to get saved so these next
four controls here the scroll bar spin button image and reference edit controls aren't used as often
as the rest of these controls are you probably won't find yourself ever in a situation where you need
to use these for that we'll go ahead and go through them so we'll click on scroll bar and click onto
our screen and you can see the scroll bar shows up and this looks more like a slider than it does really
a control or a scroll bar.
So this is a kind of situation where maybe you have an interest rate or something and you can drag this
bar up or down to change that interest rate.
So there's a lot of code that goes into making this operate properly and it's not used as often because
it's just as easy for the user to manually type in the interest rate in something like a text box.
So you won't see the scroll bar used very often.
You can also have a spin button which will go ahead and insert over here as well.
And the spin button you can see are just up and down arrows.
So these are just used to increase or decrease the value.
So it's good for fields like age for example where you might just want to increase or decrease your
value by one.
But again you could just use a text box for this.
So not very common to see the spin button on a form.
Now the images control I'm not even going to insert on the form.
They get a little messy because you have to make sure the image you're pointing to is on a shared network
drive.
If the image is on your local computer storage drive and someone opens this form on a different computer
the form will actually enter a loop.
Error trying to locate that file.
So generally the image control isn't used very often and it's not usual that you would need to have
an image on your form in the first place.
Now the reference edit control lets the user select a range of cells in the workbook and this can be
helpful if your code does something like automatically formatting arrange yourselves or if you're searching
through data for a particular value but nine times out of ten your range is going to be predetermined
using something like a table.
So this won't be used very often.
So the last control that we haven't talked about yet is this multi-page and the TAB strip and these
both do similar things.
So we'll talk about the multi-page since this is more common to see and forms.
So I'm actually going to expand my form and give us some room over here to insert this new control and
I'll click on the multi page and go ahead and drag it out.
Now the tab strip does something very similar.
It doesn't have this kind of frame that you see on the multi-page so most people will just go ahead
and use the multi page.
So this is where we were talking about earlier with our toggle button for example you might be toggling
back and forth between.
I'm adding a manager versus I'm adding an employee.
This is a separate way that you might accomplish that this is also good for breaking out if you have
lots of information that's kind of too much for one form.
Maybe you don't want to use a whole bunch frames like this.
Maybe you want to actually separate out that information onto separate pages that we can toggle back
and forth between.
So if I for example grab my toolbox and if I insert a label onto my page one here and we see this says
label two when I toggle to my page to that label to disappears and this works for all of our other controls
as well.
So if I insert for example an actual text box that we could type into and insert this onto my page two
of my tabs control here when I come back over.
Now on toggling between page one which has the label and page two which has the actual text box.
So this can be a really powerful tool for if you have a form that has a lot of information on it that
needs to get filled out or maybe you have different roles in each of these roles needs different information
to go along with it.
That's the kind of thing where you might want to have a multi-page document like this going across the
top of your screen where they could individually click on each page and walk through that information.
It's also really good for building a dashboard.
So if you have a lot of different functions that go into your database maybe you have an entire set
of functions for an administrator and then an entire set of functions for a salesperson and maybe a
third page and that's all a set of functions for your production line.
This is something where you could put a whole bunch of buttons actually on to these pages and use this
as your dashboard rather than trying to create a dashboard on your actual excel file.
So these are all of the different tools that you would see the different controls that you might insert
onto a form.
And an example of the form would be building something like the employee ad form which we will be doing
over the course of the next couple of videos to actually set this up and tie it to code and make sure
everything's running smoothly.