Run a Macro when a User Changes a Specific Cell, Range, or Any Cell in Excel

here I'll show you how to run a macro when a user changes a specific cell within a worksheet a range of cells or any cell within the worksheet so we're gonna cover all three scenarios before we start check the video description and click the link to teach Excel so you can download the files for the tutorial and follow along and make sure to subscribe and accept notifications so you can see all the new tutorials alright so first things first let's cover when a user changes anything within the worksheet and then we're going to narrow it down to a specific cell by the end so we go to the VBA window by doing alt F 11 and what we're gonna do here is instead of going to the normal insert module which is what we do for almost every macro what you want to do here is to double click the worksheet where you want this macro to run where you want something to happen when a user changes a cell you're gonna see a window open up like this and in the left hand menu if this is not doesn't look like this by the way it might not be fullscreen it might just look like this but it's the same window I just made it fullscreen so then we go to the left section here where it's going to say general by default click the drop down arrow click worksheet and by default it's going to be worksheet selection change which means that whatever is in here will run every time you select something in the workbook that's not what we want don't worry about it go to the right hand drop-down over here and these are all the options we have and every one of these options it's an event it means that when something in Excel happens we're going to run a macro and this allows you to determine what event would run the macro so before we delete before we double click right click calculate change and so on and what we want here is change so click that and then we have this little section worksheet worksheet change let's go ahead and delete the selection change we don't need that I'm going to go ahead and remove the extra space and now in here anytime we change something in the worksheet a macro will run so let's just do a little message box hi there and let's test it out hit alt f11 to go back to the worksheets now I can select it nothing happens double click hit escape nothing happens double click enter something happens it essentially thinks it changed even though to us nothing changed so notice that even if we don't input anything it's going to do this and we have our macro that ran a little message box hi there so when we type something in it runs the macro but we can still select everything and do all of that now let's see what happens when we select the cell and hit the delete button what the worksheet has changed so you don't just have to activate the cell for that to happen now as it currently stands it doesn't matter what cell we do something with it is going to run the macro so let's go ahead and make it run on a specific cell I'm going to highlight the cell here just so it is easier for you to find when you download the workbook so we'll work with cell a1 so alt f11 double click the worksheet that you're working with and now it is time to limit when this is going to run what we have here which it helps us out tremendously it's so cool is this up here what this does is in the target variable it creates a variable a target variable that stores what was changed so we can now figure out what cell was changed so if I do something like this target dot address it's pretty cool let's go back to the worksheet let's type in here or double click in here and hit enter a 1 so we get the location of the cell that was changed go back to the VBA window all 511 and I can start to see how we're going to limit this only works on certain cells now what I'm going to do examples is to leave that in there and comment it out so that you can play around with it leave another one in here just the hi there one so uncomment that remove that little single quote if you want to run that one and now let us begin to limit it using the target variable if target dot address equals now let's figure out the range we want to do it with a 1 range a 1 dot address then now let's go down here and if so now we have I'm going to select this and hit the tab key to push it in we have surrounded the code we'd like to run with an if statement this if statement uses target address the same thing we just output and it checks to see if it's equal to well the address of cell a1 and we can put whatever cell we want in here a 1 B 2 C 3 whatever you want now let's go back to the worksheet check it out I go down here nothing down here nothing up here and it runs how cool is that it's that simple so now we have a macro that will run only when we do something with cell a1 let's go ahead and add a comment and now let us let us create a section of code that's going to run when we change any one of a number of cells so let's go over here and let's say a 3 to a 5 put that as yellow this will be our range and we want to run it if we change any cell in here but we don't want to make a bunch of if statements that say if it's a 1 or if it's a 3 if they fourth say 5 you don't want to do that that is just very annoying so we're going to use an if statement but it's going to be a little bit different this time let me add some space it's going to seem a little confusing let me just type it out first if not intersect target target you know it's the cell that was changed and now we want to do a range but here we can type in the range 8th with a full range so we don't have to type in a single cell so a 3-2 let's say what was that a 5 close parenthesis for the intersect and then is nothing then okay now and if let's output a message box changed my range let's make sure this works now alt f11 we're out here enter nothing nothing now let's go in here changed my range but the other box did not run now let's go up here and run this one hi there but this time the range one did not run so perfect exactly what we want now let's talk about it this right here is logically speaking a lot more confusing than this up here but we need to use this because what we're doing using the intersect function is to see if this right here or you can see when you type a comma you have lots of arguments that you could put here as ranges you want to see if it intersects with this target here so basically it's saying do these guys overlap yes or no and the way to check for this within an if statement to check if it did overlap we can't just reference this directly is we have to do this kind of double negative thing so we first check if it is nothing so it's kind of like if it didn't return a result kind of and then we put a knot in front of it to negate that and reverse it I have never been good at explaining this all you need to know is that if you want to check if a user is going to work with a range do something change a range of cells you use this format copy it paste it forget it that's how I look at this because it just works it's easy peasy and in a moment I'll show you how to pull this part of it out so how to pull out gosh I hate this editor for code how to pull sorry how to pull this range out so that you can change it at the top instead of having to go in here and mess with this stuff and this section of code up here if you wanted to run when a single cell has changed now let's say that you're building larger macros and you don't want to put everything in here you want to have or you've already got them other macros in your modules so sub another macro I'm another macro so let's say you wanted to call another macro from over here that is easy to do let me comment that out run a macro that is located side of a module all we have to do well there's two ways to do it but I like to do this way types the word call and then another macro so the name of the macro let's test it out delete this value I'm another macro so it's really really easy sometimes people get stuck on that that's all you have to do and if we could delete the call yes and I could do that and it would run the macro but I do like to keep call in there just helps me to remember that we are calling another macro so I like to keep it in there so if you want to run a macro locate another module just do that and that way you can keep your code a bit more organized now before I go I want to show you how to pull as I mentioned a little earlier how to pull this dude out of here so you're not hard coding the ranges into these if statements so what we can do up here will do two things first we shall declare some variables so let's say cell to change and we want to set that as a range because we want to store it as a cell reference basically dim range to change now what we're going to do is we're going to set these variables cell to change and range to change equal to a range each one is equal to a range 1 is equal to a single cell range and 1 is a multi cell range now to set it equal to a range which is an object we have to use the word set if I was setting it to text or numbers I do not have to put this word in front of it so set cell to change equals to range a 1 and now we can go down here copy cell the change put it right here we still need the dot address at the end of it and you will notice if we were typing it out so let me delete this cell to change the second I hit the dot you're going to have a bunch of options start typing address and it fills it in for you once you hit the tab key like that we do the same thing for the next one that range to change equal to range a three to a five copy that go down here and delete that put it in there and we do not need address in this case so now it's very easy all we have to do is change this up here and this right here we don't have to mess with these if statements we can leave this annoying little part alone and never have to look at it again just change this up here if you want to change that so let's go back to the worksheet make sure everything works text I'm another macro more changed my ranged stuff here and if I do this nothing okay automatically filled in yellow very annoying undo but it doesn't run when I change any cells any other cells so alt 11 to go back here that's all there is these are the two ways to make it so that a macro is going to run if a user changes something in a cell or change something in a range or also I guess the third way if they change anything within the worksheet in the cells this is how you can call other macros so you don't have to have all your code in here and this is also how you can make the macro a bit more robust and a bit better for the long term so it's easier to manage by breaking out the range references up here in two separate variables that's all for this tutorial I hope you found it helpful I hope you liked the tutorial if it was helpful don't forget to give it a thumbs up and make sure to subscribe and accept notifications so you can see all the new tutorials

Channel: TeachExcel

Views: 62,716

Rating: 4.9023199 out of 5

Keywords: #msexcel, #excel, vba, macros, excel macro tutorial, events in excel, excel vba course, excel vba help, macros in excel, how to excel vba, events in macros, run a macro, teachexcel, teachexcel.com, microsoft office, microsoft excel

Id: HsiwC9xg06c

Length: 15min 1sec (901 seconds)

Published: Tue Sep 10 2019