So in this video we're going to talk about how to tie code to your user forums that you create.
Because obviously we need some VBA code to be able to actually open this form or to close the forum
or to add the employee that we just created to our table in excel.
So we definitely need to put some VBA into this form before we do that.
I'll talk through this forum really quickly.
We had built a couple of examples in previous videos just of what all of these different controls are.
So this form is built specifically for our Employee table that we have here.
So when I go back to my form I have here I have first name last name start date roll and I put a little
checkbox for full time and I'm going to assume that this box is not checked that the employee is going
to be a part time employee.
I put all of this on a frame and this is just to help organize the form a little bit.
I also change the background color of our regular form and this is something that I typically do just
to help things kind of look a little bit nicer and make it clear what the user is supposed to interact
with.
It's totally up to you if you want to change the background color of things or if you want to put your
objects on a frame or if you just want to put them on your regular form.
It's not going to change anything at all in the code.
So just talking through each of these two you can go ahead and build a similar form the first and last
name are both just regular text box which is this control here.
And I just entered these I made them a little bit longer than the normal text box so that we'd have
some room to type in.
I also made the start date a text box and I left this one a little bit shorter since it shouldn't take
a whole lot of room to give a start date.
The role is a combo box and this is because we will be actually building our dropdown list of our roles
and we'll do that in the VBA code.
And of course the full time down here is a checkbox which is this object here and combo box is this
one.
It's different than this box so make sure that you use combo box.
Otherwise you'll have to use a little bit different code.
You will have to change out combo box rule this box later on.
And then of course we have our two buttons adding close.
And so I need to go through all of these and name them and again like we mentioned before we went to
avoid naming things with spaces or anything like that so I'll call this close button or click on my
Add button and I'll call this Add button and you can see on both of these I change the background color
property which is this property here.
I also changed the font to be bold and I increased the font size to 10 just to make it stand out a little
bit I'll go up to my checkbox here and we'll see a full time check.
We'll go to our last name box.
We'll call this last name text call this one first name text come over here to our start date and we'll
call the start date text
and lastly we have our roll combo.
You can see I'm putting this little tag at the end for combo text box or button or whatever this happens
to be the controller happens to be the reason that I'm doing that is it'll make it a little bit easier
when we're in VBA to remember.
Oh yeah.
This is the text box.
This is how I should interact with the text box and so it'll help us later on if we build that into
our names a little bit or make it easier for us to reference in VBA appropriately.
So I'll go ahead and I'll give you a minute to pause the video and you can go ahead and build this form
and that should have been enough time to pause.
So now we can step into the actual code that gets tied to this user form.
If I go over under forms on the left in our little project Explorer window here and I right click on
this form you can see two options come up you code and you object.
So if I go ahead and save my database really quickly or save my file and then I close out of this form
we can see when I right click on this.
I have two options.
You code or view object if I click on View object.
My form pops back up.
If I right click and select view code irregular code window will pop up and this is just like the module
window that we've been working with in all of our other videos.
But this time we can see this is actually tied directly to our ad employee form which is this form here.
So any code that we put into this ax essentially as a module for this specific form.
So this is where we'll want to do things like clear the form when we open it up or build our list that
should appear in the royal box or tell it what happens when I click add.
And what happens when I click close.
And so that's what we'll be doing in this video.
So we're going to start by building the close buttons code.
So if I click on my close button here we can see we called it closed button.
So over in my module window I'm going to create a private sub and we're gonna stay close button underscore
click Open parentheses close parentheses enter.
So now we can see up here this automatically changes to be the object of our close button and the action
which is click.
And so this matches what we've told the code we want to do here which is our object before the underscore.
And then after the underscore the action that we take.
So this means when we click on our close button this sub is going to run.
So what we want to do here is close the form which we're gonna do by saying unload me.
And what this means is that in our in our code here we're telling it we want to unload an object which
means make it disappear we don't want to see it anymore get it off the screen.
So unload this object and we've told it the object we're interested in unloading is me.
Which means the object that this close button here lives on which is this ad employee form.
And we could hit on this a little bit differently but this works just fine because we know this is always
going to be the object that gets closed when we click on the close button.
So we'll say unload me to close this form.
Now I'm going to go ahead and create a second private sub.
And in this case we're going to look at our Add button and what happens when we click on that add button.
And so what we want this to do is if we go back to our main excel file here we want the Add button to
create a new row in this table and fill it all out.
So right off the bat we can see we're looking at the sixth column is when our table starts.
And if I click into this table and go to table design I can see that I've named this table the employee's
table.
So when I try to reference this table I'm going to call it employees.
It's now going to go back to my code.
Make sure we're clicked into our Add button code here and we're gonna go ahead and we're going to create
some variables so we're going to start with our first name variable which we'll just say is first.
And that's gonna be a string which means text our last name variable will also be text or start date
variable will also be text.
Our role will also be text.
And last but not least we have our full time checkbox and it because this is a checkbox it means or
our value is going to be boolean which means it's either checked or it's not checked there's no gray
area in between.
It's either true which means checked or false which means it's unchecked.
And so we're gonna go ahead and create that full time variable as boolean and what this means is later
on when we go to store this value in our table we're gonna want to do a little bit of a conversion because
we don't want to store that boolean value in our tables so when we initially pull this value it's going
to pull is either true or false and we don't want to put the word.
True or false into our table necessarily.
So we'll do a little conversion that says if this full time variable is true then I want to store the
value full time in my table and if this value is false then I want to store the value part time in my
table.
So we'll get to that when we actually add a row to our table.
The next thing we want to do is actually pull all these values off of our form.
So the way we're going to do that is we're gonna set our first variable which means our first named
variable equal to the first name text box value.
And this first name text if you'll remember is what we named our first name text box here.
I can see this is called First name text.
So that's how I reference it in my code.
We'll keep moving.
So our last name variable is going to equal our last name text box value our start date variable is
going to equal our start date text value.
Our role is going to equal our role.
This one is a combo box if you'll remember value and our full time is going to equal our full time checkbox
value.
So now we've gone ahead and we've pulled each of these values off of the form based on what we named
these particular objects that we're interacting with.
So now we can go back to our actual excel file and add a row to our tables so the first thing we need
to know is what is the sheet name.
And it looks like this is called Sheet 1.
Second thing we'll need to know is what column.
Our table starts and which is column 6.
And the third thing is what is our table name which we determined earlier is employees.
Now go back into my code and I'll go ahead and activate my sheet one worksheet.
So I say worksheets Sheet 1 which is the name of my worksheet that activate.
So now I know everything that's following this is going to assume that I have my sheet 1 activated.
I'm going to go ahead and create a little variable R and I'm going to store it in this variable the
number of roads that are currently in our table.
So we'll say on our worksheet Sheet 1 which we don't really need to do since we just activated this
but just to be safe we'll go ahead and type that out and then we'll say list objects employees.
So now we've referenced our employees table that is on sheet 1 then we want to look at the list rows
which means all of the rows that are in this table and we want you to count them.
So this variable R will tell us how many rows are currently in our table and that does not count the
header row or any of the roads that are before our table.
So if I go back to my cell file or should return the value 1 which means we only have one row of data
in this table right now.
It's not going to count this hetero and it's not going to tell me this blank row exists.
So I know in my code later on I'm probably going to have to account for these two rows here the header
row and the blank row.
I'll go ahead and go back to my code.
Now we want to go ahead and we're gonna want to add a new row to our table to we'll say on sheet 1 We
want to look at the employees object which is our table we want you to look at the rows in that table
and we want you to add a row.
So we'll do that like so and we could have put this before R which probably would have made this a little
bit easier for us but we'll just keep working with how we have it now.
So we know now we're going to have a new row that's created just below.
So we'll be looking for this particular line of our table.
So now I know if I'm trying to reference this cell and I don't want to use it by specifying you know
I'm looking at my table if I just want to use the cells function of my worksheet I know I'm going to
be looking in column 6 and then I know I'm going to want to add to to cover this and I'm going to want
to add one to cover my new row and I'm going to do that to my R value so I know my our value is going
to count the number of rows in my table which is currently 1 row then I'm going to add one for this
blank add one for this had a row and add one again to get to the row that we actually want to interact
with and this will work no matter how many rows we have in our table so let's say we have three rows
in our table again I'm going to want to add one to the blank one for the header and one just to get
to my last row that I've just added.
So this will make sense when we step through the code in a minute.
So what we're going to look at is specifically cells are plus three row.
And again that's one for the blank one for the header and one to get to our new row.
So we've added three and we're going to look in column six which is column X or F which is what we've
just determined is our first column of our table we want the value to be our first name and then we're
just going to step through on the same row step through each column and store our variables that we
just pulled off of our form.
So again our plus three this time column eight the value of the cell should be our start date.
Next we have our role which will be in column 9.
And now we've gotten to our last variable which is this full time variable.
So if I go back to my sheet here this is where we were explaining earlier we don't want to store the
value true.
We don't want the cell to read true or false so we want it to read either full time or part time.
So going to do this using an if statement which we've covered in a previous video so we'll say if our
full time variable equals true then for this cell which is our plus three row tenth column I want that
value to say full time.
So if my full time box is checked if it's true then I want this cell to read full time otherwise or
else I want the cell to read part time and then we'll end that as well and maybe we want to put a little
message box at the bottom here that says the employee has been added to the table and we'll go ahead
and jump past this Button's input we'll just let the OK button be what shows up and we'll title this
success.
So we're going to jump back over to our excel file and we can see in this file we have an ad employee
button here that we haven't really done anything with yet.
And if you don't remember how to add this button there's an Excel controls a video that shows you how
to do this or in the developer tab you can click on insert and go ahead and insert a regular form control
button in our case we'll just leave it as an employee and we'll right click on this and say assign macro
and we can see right now I actually don't have any macros that have been created in our workbook.
Remember that all of the code we've written is tied directly to our form and it's all private.
So we can't reference any of it.
So what we're going to do is cancel out of this window go back to our VBA and we're going to go ahead
and insert a module now we can see this is just defaulted to module one you can rename this if you want
to but it doesn't really matter.
We're going to create a public sub and we'll call this open forum open closed parentheses hit enter.
And this time in this code we want to show our ad employee form.
And remember we've called this form ad employee.
That's the actual name of the form.
So we're going to reference that form directly by typing its name and then we're going to type dot show.
So now when this line runs VBA is going to find this object which is our ad employee form and it's going
to show that object which just means open up this form and show it to the user.
So now I'm going to save this.
Go back to my excel file and in here if I right click on this again and say assign macro we now see
we have this open form macro that showed up so I'm going to highlight that and I'm going to click.
OK now one more thing we're going to do in our code before we move on.
We'll shrink this down a little bit and we'll go back into our ad button code and I'm going to click
just to the left of this first line here and what this does is this will actually pause our code on
this line and bring us into this code.
Windows that we can step.
Line by line through this code and see how it actually interacts with our excel file.
Now go back here.
I'm going to click off of this button in a different cell.
Come back in and now I'm going to click on the ad employee button which we would expect will open our
form and it looks like that's exactly what it's done.
So now I'll just type some dummy values into here.
So what does say first name last.
Say 1 1 20 something like that.
Right now we can see we don't have anything built into our roll combo box so I'm just going to type
a custom value and we'll cover how to create these lists in a different video and we'll go ahead and
say this is a full time employee.
Now if I click close right now just to test things out we see it does close my form automatically I
don't get any sort of message or anything like that.
Now you can build in messages you can build in actions that happen when you click on that close button.
But for our purposes we just wanted it to close out of that form.
So now click at employee again and it will pop our phone back up.
So I'll say first last start date I'll use 120 again we'll just create a custom roll just as an example.
Look check the full time box.
Now when I click add it's going to go ahead and open up our VBA window and we can see it's paused on
our first name line here which is what we expect and we can walk through this step by step to see how
this will actually interact with our excel file.
So I'm going to go ahead and shrink this window over a little bit with this form code over shrink this
down a little and we'll do the same to this file.
Now you'll notice right now if we click in here it's not actually going to let us interact with our
excel file you should get a little sound that makes it clear you're not allowed to click in here.
And that's because the property of this particular form right now is set to true for show modal which
means while this form is open I can't actually interact with my excel file.
So instead we'll just go ahead and shrink this down a little bit more.
And now we can see all of our excel file so we should still be able to see how this code works.
So I'm going to step through this using f eight on my keyboard.
You can also go up to debug and say step into that I'll just use f eight so now I've stepped past this
line.
I can see my first name has pulled the first from this cell last has become last from this cell start
has become our start date we entered role became the role we entered and full time is reading as true
which is what we expect because it's a checkbox and we told it this was going to be a bullion value
which means either true or false.
Now I'm going to go back to my excel file and activate my sheet one which it already is but this is
just a precaution.
So we'll hit F eight.
It's now going to pull the number of rows currently in my table which we would expect would be one.
So we'll hit F eight again and check out this value and it does say one so we know that this is read
correctly we'll press f 8 again we should see a row be added to our table here and it looks like it
has been.
So now we can go ahead and start filling out these values so starting in column F which is column 6
we should see the first name and then seven should see the last name start date roll.
And now we're back to our full time value.
So this is where we built our if statement to say hey if this value equals true we want you to store
the value full time.
If it's false which means the checkboxes unchecked we wanted to read part time.
So if we press f 8 we should see this statement check out.
So the condition is met and it should enter the cells equals full time line and it does so that's good.
So I'll hit update again and we'll see it does read full time.
Now I think again one more time should get your message box that'll pop up and say the employee has
been added to the table will click OK and now we're on and subs will process it one more time to go
ahead and exit out of the code.
Now if I come back and here I can close out of this form and we know everything that we've built has
worked properly.
And next we'll be talking about how to actually assign lists to that combo box.