In this video we're going to talk a little bit about some of the events that we can build into our actual
excel file and what I mean by this is we can set up events to run a macro when something specific happens
on our excel file and one of the most common applications of this is double clicking on a cell to open
up a user form that has this row of information in it or a double clicking on a different cell to automatically
run some calculations on this row of information or in this column of information.
And so double click is one of the most common events that you'll use.
There are different events that you can execute and it's pretty easy to find that list.
So for our example we're going to be building a double click event.
So the first thing we'll do is go to our developer tab and open up our visual basic window.
Now you can see over here on the left I am using an Excel file that we built previously and this is
the excel file we built a custom function in which is what these three columns over here are for.
And this is also the excel file that we built a form into add new employees to this table.
So in this case we might want to be able to double click on this first name open up all of this information
as a user form and we might want to be able to make some edits to that information and receive it.
And this can be done pretty easily we already have the form built so we could copy and paste this form
and just restructure it to be saving instead of adding new data.
But for our purposes we're going to use a very simple example based on that same concept so that we
don't have to build a new form but you can see how you would actually execute this.
So you'll notice we have some Microsoft Excel objects up in the top of our project Explorer window.
We can see we have sheet one and we have this workbook.
Now if we right click on either of these you can view the code that's associated with those objects.
So in our case we're interested in writing code directly onto Sheet 1 and we'll see this in a minute.
So we'll click view code and we'll open up the code window for Sheet 1 now as I mentioned before we
can see the full list of actions we can take to set up events by selecting worksheet.
And this means we're going to apply whatever subroutine we write to this specific worksheet.
So this will only execute on our sheet one worksheet if we add in sheets later this code will not relate
to those sheets.
Now we can come over here into this actions list and click on the dropdown and we can see there's a
whole lot of different actions that we can take to create events in our purposes.
And this is probably the most common we are going to select before double click and I'm going to go
ahead and delete this private sub that was created automatically.
And we're just going to look at this before double click subroutine.
Now you can see we have target as range and cancel as boolean that are automatically created as inputs
and we can use these or we can write our own criteria for when we want this double click to actually
run.
And so we're gonna take the latter of those two options so we're gonna say we're gonna create a variable
C which is gonna be an integer and a variable R which is gonna be an integer.
Now C is going to be the column that we double clicked on.
So we're gonna say for the active cell which is the cell that we just double clicked.
I want you to pull the column number and that will become our C value.
We're also going to say for the active cell the row will become our variable are not just as a quick
example of what this means we could say really quick if C equals six then we want to get a message box
it says column six
else we want to get a message box it says wrong column
and don't forget to put the end if at the bottom.
I'll go ahead and save this and go back to my main excel file.
Now if we test this out if I double click on any other column then 6 which is column F so if I go over
here and I double click in column D I get a message box that says I've clicked the wrong column.
If I double click in column six which is column F now I see I get the column six message.
So we know that our column number is reading correctly according to what we're double clicking.
So I'll come back into my VBA window a click out of the cell and then I'll come back into my VBA window.
And the reason that we have to click out of that cell is because VBA doesn't want you to write code
while you have your cursor actively inside one of the cells on your Excel worksheet.
So that's something to keep in mind always click out of a cell deactivate that cell before you continue
to write your code so this time I'm going to use and not statement and we'll see why in a second I'll
go and create my end if so that we don't forget it later.
So what this statement reads is this is the criteria that I'm checking everything between the if and
the then in this case I want to know if we clicked on something that wasn't in column 6.
I only want to run this code if I click on the first name column.
That's what I've decided.
So I've put.
Not C equals 6.
So everything that's in the parentheses here gets checked against the negative.
And that means if I click on anything other than what's in this column which is column six then this
is going to run.
So we're gonna say if we click on anything other than column six we just want to exit the sub.
We don't want anything else to happen.
We didn't click on the first name columns I'm not interested in running any sort of code.
Now if we pass this criteria which means if this checks and we did click on column six we're going to
jump past this little if statement and instead we're going to create some variables so we'll create
a first variable for our first name.
The last variable for our last name a start variable for the start date a role variable for the role
and a full time variable for our full time.
Now this is where normally if you had a form or something that you wanted to open up you could pre fill
that form with these values and then you could write that form to save information that's adjusted instead
of adding new information that would take us a minute to do.
We already have the tools to do that based off of the form instructions videos.
So instead we're just going to pull these values so we'll pull our first name value out of whatever
row we clicked on.
And we know that's in column six our last name value is in whatever row we clicked on column seven our
start date value is in whatever row we clicked on column eight the role value is in whatever row we
clicked on column nine.
And last but not least the full time value is in whatever row we clicked on column 10 and instead of
opening a new forum and going through all of that we're just going to create a couple quick message
boxes just to see if this is working.
So we'll say you selected and we'll go ahead and say our first name we give ourselves the space in between
and then the last name.
So we should get a message box that pops up and tells us what name we've selected and then we'll also
create a message box that says they started on and we'll say the start date and combine that with as
a full time variable so whether they're full time or part time employee in and we'll say roll.
So all in all this should read you selected your employees name here and then they started on this date
as a full time or part time employee in whatever their role was.
So design management whatever we selected so we're going to test this out we'll click save or minimize
this.
And now I'm going to double click intentionally on something that's not in column six so I'm gonna come
over here and I'll double click on column J and we can see nothing happens.
My cursor just enters the cell.
Nothing special happens.
None of these columns are column six.
So we know our if statement is working properly.
Now I'm going to come over here and I am going to click in column six I'm just going to double click
on one of these names so I'll double click on John's name here and we can see I've gotten this message
box that says you selected John Smith or click OK they started on 1 1 20 as a full time employee in
design so we know that our message boxes are working we know that our if statement is working and we
know that we've successfully pulled the data out of this particular row that we double clicked on this
will work for any of these rows that we double click on that have data and then so we know that our
code is working properly.
So this is what building and event does in excel.
And you can tie these events directly to worksheets or to a specific workbook which can be very helpful.
And it can get information out of an entire row or out of an entire column pretty quickly and into your
VBA for you to to work with in your code directly.
So in this video we learned about building custom events.
We learned about a couple of the different kinds of events but primarily the before double click event
which is the most common usage of this.
And we learned how to actually go through and pull the active column the active row and then execute
some code with that information.