So in the last couple of videos we've built this employee creation forum.
We went ahead and tied all of these various objects on this forum to code and we built our little show
form codes so now we know when we click on the ad employee button here we get this form that shows up
we can fill this out.
We can add new employees to our table here and we know all of this is working smoothly.
So our next step is to go ahead and add a list to our roll combo box here.
And this is something that you actually want to do when the form loads.
So as the form is opening for the first time we have the chance to interact with the form before the
user can even see it.
So this is the kind of situation where you might want to if you wanted to lock down some of these objects
that these are couldn't type in them or if you wanted to hide some of them and set the visibility property.
This is all stuff you could do as your form is loading for our purposes.
We can go ahead use form load to create our dropdown list but we could also use it to preset some of
these values to be either true or false as as in the case of this checkbox or we can preset some of
the named values or the start date or anything like that.
So that's what we'll be covering in this video is on the form loading going ahead and pre filling or
pre determining some of these values.
And so the way that we do that is by creating a subroutine.
So we'll say private sub and this time we're going to take user form underscore initialize open parentheses
close parentheses enter and we can see up here these two dropdown tab automatically updated which is
what we would expect.
So this is telling us we are interacting with the object user form which is this form that we've built.
That's what this is it's a user form.
And on the initialization of this object.
So as we load this form as we open this form we're going to do whatever code we put into here.
So this is where we could preset some values or build our list for our combo box.
So let's start by pretty filling our date.
So maybe we want the start date to be today's date.
Maybe this is a form that we fill out on the very first day that the employee starts work.
So the way we do this is by referencing our start date text box.
So we'll type start date text which is the name of this text box that we put onto our form and we'll
say that the value of this text box is going to be date.
Now we can see date automatically capitalizes that's because this word date has meaning in VBA already.
That's why we can't create a variable that's called Date.
It would confuse the code later on as to whether we're referencing our variable or referencing what
VBA currently has tied to the word date.
Now date in VBA means today's date.
So when we actually run this code we should see the start date cell up here automatically default to
today's date.
The next thing we want to do is go ahead and build our roll combo box list that appears when we select
that dropdown button.
So we're actually going to do this with a with end with statement.
And that's because we don't want to have to continue to reference this roll combo box over and over
again.
So I'm going to type with roll combo hit enter.
We'll go ahead and give myself a line of room to work with.
And I'm going to type.
End With.
Before I forget that we're sure we close out this with statement and now roll combo again is just the
name of this combo box if I click on this we can see it's called roll combo so I'm saying with my roll
combo box I want to add an item in this case I want to add the item we'll say design is the first option
maybe we also have sales maybe we also have production and maybe we also have manager.
So now each of these add to each of these items should have been added to the list.
That appears when we click on this role drop down menu.
So as we load this form all of these items are going to get added to the potential list for our roll
combo box.
Now if we wanted to clear off our roll combo box and empty it out at the beginning of our form we could
do that by typing roll combo dot clear and that would MDL anything that's currently stored in our combo
box or roll box that we have there.
So now we can go ahead and go back to our main excel file here and this time when I click on the add
employees button we have something that we expect to happen as the form opens so we'll click on this
to see if our code works and we'll see as this pops up.
We have a pre entered start date which happens to be today's date.
And again that's what we would expect seeing as we preset this value to be today's date and in our roll
if we click on this dropdown we can see our various options that we wanted to pre build into our role
have been created in this list and I can either select this or I can type in my my own new role and
that's fine since we use that combo box.
So I'll go ahead and close back out of this and you can see how it'd be pretty easy to build a simple
loop to go ahead and add these items if you wanted to make this dynamic.
So if we wanted to have a Rolls list somewhere else on our table and say we have roles here design production
sales manager it would be pretty easy to build a quick loop that would run through this and add each
one of these cell values into our combo box list by adding an item and instead of typing design typing
cells whatever cell we're in value something like this.
And that would be pretty simple to do as a loop if you wanted this to be a dynamic list that you could
control from the Excel file rather than having to come into the code itself to control it.
So in this video we talked a little bit about opening up your form and how we would drive and preset
some of the values on opening the form.
We also talked about how to create the lists that will populate your combo box in your list box which
you would do the same way you would just change whatever the name of this combo box is to be the name
of your list box.
And so now that we know how to do this we can go ahead predetermined values on our form and build our
lists on our form as we open them.
So it's super helpful tool to use user form initialize.