Excel VBA: The Magic of the Worksheet Change Event

hello and welcome in this video we're going to look at the magic of using the worksheet event in Excel VBA so the worksheet event is very very powerful it's quite easy to use but you can run into problems with it if you don't know what you're doing so you've got to be very careful so in this video gonna show you a few simple precautions to take and then you can get started using the worksheet event now if you like this video please click on the like button below as it really helps with the algorithm and if you want to get notified of my upcoming videos then please click on the subscribe button so let's go ahead and get started looking at the worksheet event so what is the worksheet change event well the worksheet change event is basically an event that happens when we change the value of a cell on a worksheet and we can take advantage of this by getting called to run so an event is simply a soap we can put cord in and an event runs when a particular event occurs so that's simply what we mean by an event so for example if we put G in the cell b2 it'll run code that I've written already and we'll change the cells beside it to the color green now if I change the value to be it then changes to blue and you can see that this code is running every time that this cell changes so if I change it to white you can see it changes automatically to yellow and then if I delete it it just removes the color so you can see this is very very powerful indeed because when values change in a cell we can get certain code to run so let's have a look at the code that we use this is the code that we use to change the colors so it's quite simple it takes a letter as a parameter and it takes the range where we want to change the colors and then based on the letter it selects which color to put in the range so it's very very simple indeed but how do we do this with a worksheet event well let's have a look at that now the name of the worksheet that we want to change is called color and you can see it here in the worksheet project window so you can see SH color and color we double-click on this and it opens the worksheet now in the top left it says general but we select worksheet and then in the right-hand side in the drop down you can see that it brings us all the different events that are available now a quirk with VBA is that it automatically selects a event if one isn't picked so as soon as we change to worksheet in the top left if there is no event in it then VBA automatically adds an event but we can just delete this when we have the event we want now we're interested in the change event so this means when a value changes in the cell we want something to happen can we can delete this so let's explain what the worksheet change event is what happens is someone changes the value in the sheet then this sub runs and when it runs it provides us with the cells that were changed so let's see how that works so let's just put a value in d3 and you can see this event ran and we've got the target so let's step in and a target you can see says 4 but if we add the target to the watch window and then let's have a look at the watch window let's put in an address you can see that the address of the cell is d tree so d tree changed now imagine we have values in in AE tree and we step through the chord and you can see this time the target address is e tree now we can have more than one cell so for example if we deleted the values in both of these cells you can see this time that we've got a range D tree to each tree as the term so that's basically the worksheet event and now that we have a worksheet event we can basically add any code we want into the event so let's go ahead and start pulling the cord into the worksheet change so we want to call the colors so set cell colors and we pass it the target and the value and that's basically given us the letter and the second thing that we want to pass it is the range that we want to change so we can say active sheet because it's going to come in here on the active sheet and we simply say range and c2 to c4 so let's put a letter in here and see what happens we put the letter G and it works so you can see that is quite straightforward to get it to work however there's a few things we need to keep in mind if I for example add a letter in to b6 and say that's G it automatically changes it to green and if I add the letter Y here it automatically changes it to yellow so we only want it to work on a particular cell and in this case to sell as b2 so how we do it is as follows we use the application intersect function so application and intersect and what we do is we give it two ranges and it will give us back the cells in those two ranges that intersect so two ranges are of course target and active sheet v2 so what we want to say is if they don't interact so if there's no intersection between these what we want to do is we want to skip out of the function so we say if this returns nothing so it'll return nodding if there's no cells that are in common and we want to call to don't so don't is a label at the bottom of our soap and we just do an exit soap from there so now let's try this code again and see exactly what happens we're gonna change this letter G to the letter B you can see it is no effect so let's delete these still has no effect let's try red and I still has no effect but if we make the change in the cell b2 you can see that it made a change so let's put a breakpoint in so we can see exactly what's happening if we change here so we put in be their checks if the to the current cell that we've changed if that intersects would be two and if it doesn't has it in this case it returns nothing then it goes to darlin exits now in this case if it does intersect so it isn't naughty but then it continues on and it sets to sell colors as we expect and you can see the cell colors blue now it is the second thing that we want to keep in mind sometimes we only want to make a change if one cell has changed so for example in this case somebody could change the value in a lot of cells by hitting delete and we run into trouble because it's trying to set the target value and that doesn't make sense because the target value is just one letter when we do the target value for multiple cells it's going to give us back an array so we want to have a second kind of test here and what our second test is is if we say the target and we say the cells duck count is greater than one then go to don't so in other words if more than one cell is selected then we're out of here so let's do this again we do B you can see that it goes in and everything is okay and let's clear this breakpoint and we do G you see everything is okay now we do F there's no difficulty and then if we go and we select a lot of cells and we select delete it doesn't do anything so the code doesn't run because it's basically saying that it doesn't make sense now depends on our application in this case we could get it to change and basically say we're just going to take the cell b2 of that range or and then we will will base it on that so it depends really on the application but in a lot of cases you just want to make a change if one cell is updated so that's something to keep in mind so one thing we've got to be very careful of because it could crash Excel and that is keep calling events so have an event called another event call another event and eventually Excel will crash because it's in a never-ending loop so how that can happen is basically if we were to go in here and we were to say let's change so active sheet so the cell that we've just been dealing with so and that is v2 so imagine that we came along and we set the value so after we make the change we set the value to equal something else so we could do this inadvertently or we could do it deliberately but we don't realize the consequences let's put the break points here and the break point here now we'll go back to the cell and let's add and we'll put red in it here and just see what happens so everything is okay it'll step two discord fine steps to discord fine and it sets text to cell color but now what we've done is we've changed this cell so we've changed a cell and this causes it to go back into the worksheet change event because we've changed this cell and it jumps changes the color and then we're sent in the cell again so this corner to go back now if we look at the call stack so we do control L you can see that worksheet change event has been called by a worksheet change event which has been called by a worksheet change event and this will actually go on forever until it basically crashes Excel until accelerants out of stack space which is where it stores all these subs or functions so what we should do before we continue on with our change event is we should do application that enable events equals false and then when we're finished we basically say it equals to true so in Dawn we basically can turn them back on now we don't really need to run this again the four until we don't really need this code until after we've done our checks and we can put it before it it really just depends now that we have the code let's see how exactly this runs so we say B we do the normal checks we step through the code it's turned off events we've set to sell colors and now we're setting the range b2 to be equal to the value G and the code just continues on sets the enable events on and exits the soap and you can see that it's set the value to blue even though we've set the cell to G at the end it doesn't run again so this is very important to turn off events in this case you don't always need to do it pens on the scenario that you're working with but you should be very much aware of it you've got to be very very careful with the events and in most cases it's better to turn them off now sometimes you might run the code so let's put a comment here or actually let's do it in a different way so let's run this code again and we're going to do two red so we run the code to red and then what happens is we've run the cord and maybe it was like we've said it enable events to false and then maybe something went wrong with the code and we stopped it so we stopped the code right here and then we go back and we say okay let's set it to green and and nothing's happening and the reason nothing's happening is because the event is now off and we've got to turn it on again now this might seem kind of obvious now but this is something that can happen and you might not be aware of why suddenly the events and working so to get it to work you basically can take this code and I'll just bring it up to make it clear so we put the cord into the immediate window like this and just press ENTER and this will turn the events back on so you cannot you can run code in the immediate window like this is quite useful so now when we select G and you can see we do B it works fine we do be outside there's no problem we do G here you can see it changes to G the green and we do Y here it changes to yellow so just to conclude on what we do in the worksheet event in a lot of cases we want to check the number of cells that have been changed because generally speaking we only want to do something if one cell has changed the second thing is that important is we want to check which cell has been changed because we're going to do different things based on the cell that's changed so we need to know what that is and we do that use an application intersect now another very important thing is to turn off events because it can cause our application to crash or it can actually cause Excel itself to crash so you've got to be very careful and also the team to keep in mind is if your events are not running you just don't seem to kick off it could be because you have enable events set defaults so you can easily set it to true by just running it in the immediate window or you can just run a soap on its own that just does application enable events equals true so that's the video on the worksheet change event I think you will have found some very useful information there if you liked the video please click on the like button and if you want to get notified of my upcoming videos then please click on the subscribe button and don't forget to go to the description below to get the free cheat sheet if you haven't already I hope you enjoyed the video if you got queries or comments please leave them in the section below see you on the next video

Channel: Excel Macro Mastery

Views: 34,875

Rating: 4.974432 out of 5

Keywords: excel, microsoft excel, vba, excel vba, vba worksheet change, visual basic for applications, worksheet change event vba, worksheet change event excel vba, worksheet change event

Id: TYyPrqqFgVg

Length: 14min 24sec (864 seconds)

Published: Mon Jan 27 2020