1 00:00:01,250 --> 00:00:06,680 In this video we're going to talk a little bit about some of the events that we can build into our actual 2 00:00:06,680 --> 00:00:12,710 excel file and what I mean by this is we can set up events to run a macro when something specific happens 3 00:00:12,770 --> 00:00:19,190 on our excel file and one of the most common applications of this is double clicking on a cell to open 4 00:00:19,190 --> 00:00:25,520 up a user form that has this row of information in it or a double clicking on a different cell to automatically 5 00:00:25,520 --> 00:00:30,000 run some calculations on this row of information or in this column of information. 6 00:00:30,110 --> 00:00:33,950 And so double click is one of the most common events that you'll use. 7 00:00:33,950 --> 00:00:39,190 There are different events that you can execute and it's pretty easy to find that list. 8 00:00:39,410 --> 00:00:43,200 So for our example we're going to be building a double click event. 9 00:00:43,250 --> 00:00:49,070 So the first thing we'll do is go to our developer tab and open up our visual basic window. 10 00:00:49,080 --> 00:00:56,130 Now you can see over here on the left I am using an Excel file that we built previously and this is 11 00:00:56,130 --> 00:01:02,300 the excel file we built a custom function in which is what these three columns over here are for. 12 00:01:02,460 --> 00:01:08,270 And this is also the excel file that we built a form into add new employees to this table. 13 00:01:08,280 --> 00:01:14,010 So in this case we might want to be able to double click on this first name open up all of this information 14 00:01:14,040 --> 00:01:18,820 as a user form and we might want to be able to make some edits to that information and receive it. 15 00:01:18,960 --> 00:01:24,270 And this can be done pretty easily we already have the form built so we could copy and paste this form 16 00:01:25,050 --> 00:01:29,160 and just restructure it to be saving instead of adding new data. 17 00:01:29,310 --> 00:01:35,130 But for our purposes we're going to use a very simple example based on that same concept so that we 18 00:01:35,130 --> 00:01:38,700 don't have to build a new form but you can see how you would actually execute this. 19 00:01:39,210 --> 00:01:45,450 So you'll notice we have some Microsoft Excel objects up in the top of our project Explorer window. 20 00:01:45,450 --> 00:01:48,750 We can see we have sheet one and we have this workbook. 21 00:01:48,750 --> 00:01:54,550 Now if we right click on either of these you can view the code that's associated with those objects. 22 00:01:54,570 --> 00:02:00,420 So in our case we're interested in writing code directly onto Sheet 1 and we'll see this in a minute. 23 00:02:00,420 --> 00:02:07,720 So we'll click view code and we'll open up the code window for Sheet 1 now as I mentioned before we 24 00:02:07,720 --> 00:02:13,300 can see the full list of actions we can take to set up events by selecting worksheet. 25 00:02:13,300 --> 00:02:17,470 And this means we're going to apply whatever subroutine we write to this specific worksheet. 26 00:02:17,470 --> 00:02:23,530 So this will only execute on our sheet one worksheet if we add in sheets later this code will not relate 27 00:02:23,530 --> 00:02:25,000 to those sheets. 28 00:02:25,000 --> 00:02:29,200 Now we can come over here into this actions list and click on the dropdown and we can see there's a 29 00:02:29,200 --> 00:02:33,990 whole lot of different actions that we can take to create events in our purposes. 30 00:02:34,000 --> 00:02:40,400 And this is probably the most common we are going to select before double click and I'm going to go 31 00:02:40,400 --> 00:02:44,190 ahead and delete this private sub that was created automatically. 32 00:02:44,990 --> 00:02:50,610 And we're just going to look at this before double click subroutine. 33 00:02:50,650 --> 00:02:56,260 Now you can see we have target as range and cancel as boolean that are automatically created as inputs 34 00:02:56,690 --> 00:03:02,680 and we can use these or we can write our own criteria for when we want this double click to actually 35 00:03:02,680 --> 00:03:03,380 run. 36 00:03:03,400 --> 00:03:09,070 And so we're gonna take the latter of those two options so we're gonna say we're gonna create a variable 37 00:03:09,070 --> 00:03:14,320 C which is gonna be an integer and a variable R which is gonna be an integer. 38 00:03:14,470 --> 00:03:18,240 Now C is going to be the column that we double clicked on. 39 00:03:18,400 --> 00:03:22,980 So we're gonna say for the active cell which is the cell that we just double clicked. 40 00:03:23,140 --> 00:03:28,300 I want you to pull the column number and that will become our C value. 41 00:03:28,300 --> 00:03:35,890 We're also going to say for the active cell the row will become our variable are not just as a quick 42 00:03:35,890 --> 00:03:45,550 example of what this means we could say really quick if C equals six then we want to get a message box 43 00:03:45,550 --> 00:03:46,910 it says column six 44 00:03:49,430 --> 00:03:52,550 else we want to get a message box it says wrong column 45 00:03:55,650 --> 00:03:57,450 and don't forget to put the end if at the bottom. 46 00:03:57,990 --> 00:04:01,990 I'll go ahead and save this and go back to my main excel file. 47 00:04:02,050 --> 00:04:07,890 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 48 00:04:07,890 --> 00:04:13,920 here and I double click in column D I get a message box that says I've clicked the wrong column. 49 00:04:14,120 --> 00:04:20,230 If I double click in column six which is column F now I see I get the column six message. 50 00:04:20,230 --> 00:04:25,260 So we know that our column number is reading correctly according to what we're double clicking. 51 00:04:25,570 --> 00:04:35,980 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. 52 00:04:36,140 --> 00:04:41,090 And the reason that we have to click out of that cell is because VBA doesn't want you to write code 53 00:04:41,120 --> 00:04:46,270 while you have your cursor actively inside one of the cells on your Excel worksheet. 54 00:04:46,580 --> 00:04:51,140 So that's something to keep in mind always click out of a cell deactivate that cell before you continue 55 00:04:51,140 --> 00:05:00,220 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 56 00:05:00,220 --> 00:05:04,860 go and create my end if so that we don't forget it later. 57 00:05:04,890 --> 00:05:10,110 So what this statement reads is this is the criteria that I'm checking everything between the if and 58 00:05:10,110 --> 00:05:15,500 the then in this case I want to know if we clicked on something that wasn't in column 6. 59 00:05:15,510 --> 00:05:19,240 I only want to run this code if I click on the first name column. 60 00:05:19,260 --> 00:05:20,220 That's what I've decided. 61 00:05:20,220 --> 00:05:20,790 So I've put. 62 00:05:20,790 --> 00:05:23,060 Not C equals 6. 63 00:05:23,100 --> 00:05:27,660 So everything that's in the parentheses here gets checked against the negative. 64 00:05:27,720 --> 00:05:34,080 And that means if I click on anything other than what's in this column which is column six then this 65 00:05:34,080 --> 00:05:35,430 is going to run. 66 00:05:35,460 --> 00:05:40,410 So we're gonna say if we click on anything other than column six we just want to exit the sub. 67 00:05:40,410 --> 00:05:42,350 We don't want anything else to happen. 68 00:05:42,390 --> 00:05:47,360 We didn't click on the first name columns I'm not interested in running any sort of code. 69 00:05:47,370 --> 00:05:53,430 Now if we pass this criteria which means if this checks and we did click on column six we're going to 70 00:05:53,430 --> 00:05:59,280 jump past this little if statement and instead we're going to create some variables so we'll create 71 00:05:59,700 --> 00:06:02,150 a first variable for our first name. 72 00:06:02,310 --> 00:06:10,330 The last variable for our last name a start variable for the start date a role variable for the role 73 00:06:10,870 --> 00:06:16,340 and a full time variable for our full time. 74 00:06:16,340 --> 00:06:22,250 Now this is where normally if you had a form or something that you wanted to open up you could pre fill 75 00:06:22,250 --> 00:06:29,300 that form with these values and then you could write that form to save information that's adjusted instead 76 00:06:29,300 --> 00:06:32,810 of adding new information that would take us a minute to do. 77 00:06:32,810 --> 00:06:37,340 We already have the tools to do that based off of the form instructions videos. 78 00:06:37,340 --> 00:06:43,490 So instead we're just going to pull these values so we'll pull our first name value out of whatever 79 00:06:43,490 --> 00:06:44,950 row we clicked on. 80 00:06:45,140 --> 00:06:52,870 And we know that's in column six our last name value is in whatever row we clicked on column seven our 81 00:06:52,870 --> 00:06:59,780 start date value is in whatever row we clicked on column eight the role value is in whatever row we 82 00:06:59,780 --> 00:07:02,000 clicked on column nine. 83 00:07:02,090 --> 00:07:09,110 And last but not least the full time value is in whatever row we clicked on column 10 and instead of 84 00:07:09,260 --> 00:07:13,670 opening a new forum and going through all of that we're just going to create a couple quick message 85 00:07:13,670 --> 00:07:16,040 boxes just to see if this is working. 86 00:07:16,040 --> 00:07:24,020 So we'll say you selected and we'll go ahead and say our first name we give ourselves the space in between 87 00:07:24,200 --> 00:07:25,550 and then the last name. 88 00:07:25,550 --> 00:07:30,410 So we should get a message box that pops up and tells us what name we've selected and then we'll also 89 00:07:30,410 --> 00:07:42,120 create a message box that says they started on and we'll say the start date and combine that with as 90 00:07:42,120 --> 00:07:54,120 a full time variable so whether they're full time or part time employee in and we'll say roll. 91 00:07:54,170 --> 00:08:00,080 So all in all this should read you selected your employees name here and then they started on this date 92 00:08:00,110 --> 00:08:03,800 as a full time or part time employee in whatever their role was. 93 00:08:03,800 --> 00:08:10,110 So design management whatever we selected so we're going to test this out we'll click save or minimize 94 00:08:10,110 --> 00:08:11,190 this. 95 00:08:11,190 --> 00:08:15,420 And now I'm going to double click intentionally on something that's not in column six so I'm gonna come 96 00:08:15,420 --> 00:08:20,550 over here and I'll double click on column J and we can see nothing happens. 97 00:08:20,550 --> 00:08:22,170 My cursor just enters the cell. 98 00:08:22,170 --> 00:08:23,370 Nothing special happens. 99 00:08:23,370 --> 00:08:25,350 None of these columns are column six. 100 00:08:25,440 --> 00:08:28,160 So we know our if statement is working properly. 101 00:08:28,350 --> 00:08:32,910 Now I'm going to come over here and I am going to click in column six I'm just going to double click 102 00:08:32,940 --> 00:08:38,730 on one of these names so I'll double click on John's name here and we can see I've gotten this message 103 00:08:38,730 --> 00:08:47,020 box that says you selected John Smith or click OK they started on 1 1 20 as a full time employee in 104 00:08:47,020 --> 00:08:52,780 design so we know that our message boxes are working we know that our if statement is working and we 105 00:08:52,780 --> 00:08:58,370 know that we've successfully pulled the data out of this particular row that we double clicked on this 106 00:08:58,370 --> 00:09:02,650 will work for any of these rows that we double click on that have data and then so we know that our 107 00:09:02,650 --> 00:09:04,110 code is working properly. 108 00:09:04,120 --> 00:09:07,300 So this is what building and event does in excel. 109 00:09:07,450 --> 00:09:12,810 And you can tie these events directly to worksheets or to a specific workbook which can be very helpful. 110 00:09:12,970 --> 00:09:18,280 And it can get information out of an entire row or out of an entire column pretty quickly and into your 111 00:09:18,280 --> 00:09:22,210 VBA for you to to work with in your code directly. 112 00:09:22,210 --> 00:09:25,150 So in this video we learned about building custom events. 113 00:09:25,150 --> 00:09:30,100 We learned about a couple of the different kinds of events but primarily the before double click event 114 00:09:30,130 --> 00:09:32,260 which is the most common usage of this. 115 00:09:32,410 --> 00:09:38,200 And we learned how to actually go through and pull the active column the active row and then execute 116 00:09:38,200 --> 00:09:40,180 some code with that information.