Excel VBA: The Magic of the Worksheet Change Event

[@Excelmacromastery]

Hi everyone, Enjoy your watching and please leave your comments below. Don't forget to press SUBSCRIBE to my channel for more videos. Thanks for your support.

[@jorti10191]

is there a reason why when executing code from a module it randomly will jump to worksheet change event even if worksheet change event sub ended?

[@dunuth]

(newb) Is there a reason why I can't use If Target = Range("B2") Then instead of If Application.Intersect... Goto done: ?

[@twinkle547]

Hi can we lock the entire worksheets without protecting using VBA code

[@snakeeyesOFFICIAL76]

This is the best content for VBA on youtube

[@gregbernard7861]

Awesome. I may have said this before but I’ll say it again - this is the #1 primo VBA code site!

[@nathanbowers1761]

Nice work, Paul! Very timely; I was just recently dealing with needing this functionality.

[@joelabacherli1310]

Brilliant. The possibilities are endless once a firm grasp is attained and this content is ideal for that. Thank you so much for this information.

[@alechill3286]

You're out here making a positive impact on more lives than you know. Thank you sir!

[@Excelmacromastery]

My pleasure!

[@mswordexpert]

Thanks a lot, I think that in this particular scenario we can use (Target) in stead of (intersect) if target = Range(B2) then ' run the code

[@SalahEddineH]

Your channel is a mine of usefulness! Cheers! Stay awesome!

[@Excelmacromastery]

Thanks for your support Salah.

[@JoseVicenteMontes]

Thanks a lot. Very interesting topic with many pitfalls.

[@realhuahin]

I never knew it was possible to use the application dot in the immediate window thanks!

[@Chris_Rock]

Love your videos. You always seem to teach me something I've wanted to know, but wasn't sure exactly how to ask when I do an internet search.

[@Excelmacromastery]

glad to help Chris.

[@victorpiva701]

i cant undertand why u dont have more then 1 M subs. AWESOME videos!

[@Excelmacromastery]

Thanks. Glad you like it.

[@dgjanes917]

Great video! Could have used it earlier today. Took me a bit to remember the enable events. Cheers!!

[@wayneedmondson1065]

Great tips! Thanks Paul :)) Thumbs up!!

[@jakeabivaofficial]

Thanks! Very helpful tutorial.

[@NielsHeusinkveld]

For years I've used this (which I stole from the internet) and it behaves very well: If Not Intersect(target, Range("B1:B10")) Is Nothing Then end if Running the code every time you change something in the range b1:b10.

[@federico.gorrini]

This is really cool man, thanks!

[@nelsonrioux5555]

Great. Thank you Paul for letting me discover a totally unknown functionality of Excel VBA.

[@Excelmacromastery]

You're welcome Nelson.

[@TechTinkTronics]

Yes! I love the Worksheet Change Events. I have made some very interesting worksheets with code based upon Worksheet Change Events. I love the part about the importance of Application.Enable Events, that has saved me many times. Thanks for making this awesome video!

[@richrobins5711]

Thanks again! I had used this before but just cut and pasted from an example. Its nice to know how it really works! I added turning off the Events to my code to make sure I did not get into a loop! Great lesson!

[@Excelmacromastery]

Thanks Rich

[@sircorn4248]

Excellent and very useful video. Thank you

[@stevereed5776]

Thanks Paul. I have a routine that creates a new sheet and fills it in. Is there a way to run an event on the new sheet without having to put the change event code into the new sheet?

[@sasavienne]

Excellent as usual. Thanks Paul.

[@Excelmacromastery]

Thanks K D

[@wiktorkabanow]

fantastic explanation of events! learned stuff

[@bawlsackinharry5855]

I love your videos. Thanks so much for taking the time to put these together!

[@Excelmacromastery]

Thanks Bawlsackin.

[@joaocustodio2094]

Another very useful and didactic video. Thanks Paul.

[@Excelmacromastery]

You're welcome. Thanks Joao.

[@JonathanExcels]

Do you have a video about the call stack? This was the first time I’ve seen that window used.

[@Excelmacromastery]

Hi Jonathan. I might do a video on it in the future. There is not a lot to it but I think many people are not familiar with it.

[@edwinmiranda4005]

First time I heard of call stack too, and I’ve been working with VBA for 2 years now

[@cavokrider]

Really excellent!!!!

[@the_feature_selector859]

Great video again. Also the addition of using Call Stack... Never knew that existed. Really useful in debugging!

[@VIPULDJHAVERI]

brillinat and very precise , as usual in all your videos thanks for sharing mastery Mr. Paul at 8.13 when we deleted cells in col B target cell made blank but color remains , pl inform how we improve to take care for such change in target cell even in multiple range selected

[@wpryan02358]

Thanks for your helpful and informative videos. I became an instant fan! In the case of the Worksheet Change event, I have been using the command "if target.address = ("cell reference here") then...". Is there an advantage to one or the other...?

[@walterbierkens]

Great video's! I wonder why to use "Application.Intersect", while we can use the simpler: "if Target = Activesheet.Range("B2")"? Because the latter one also works!

[@kiwim3p587]

I was wondering about this also

[@edge5817]

thank you Paul... I learned something new again!

[@PrincePedia]

Great video! Thanks Paul.

[@Excelmacromastery]

Thanks Prince.

[@stevennye5075]

Good explanation!

[@montmraaa]

I normally loop through the cells in Target to evaluate each individually. This will work when multiple cells are changed as part of the same event.

[@S_g88]

Hey... Thank you sooo much bro... 😊

[@mdismailhosen8280]

is it possible to triger worksheet change event to act like combobox change event?

[@oguzturkyilmaz543]

Thank you very much

[@michaelallen7613]

Great video. You have a new subscriber. Do you have anything similar for comboboxes. They are really troubleshooting for me.

[@josephdaquila2479]

To get an understanding of when various Excel events run I like to just write a line of msgbox code to say whatever event just ran.

[@KarthikKAVE]

Again proved you are master in VBA code. May I know how many years experience in VBA coding?

[@lennysaucier]

Like it. In particular, when the value is changed, I am trying to return the old value of b31 to b33. I cannot figure it out. Can you help

[@ventjemazzel8822]

Thank you again for another great video Paul; I have a question:how about using an IF statement like this: If target.Address="B2" Then run the code?

[@Excelmacromastery]

That's fine when only one cell is returned but if multiple cells are returned they it won't work.

[@iincitr]

Thank you again Paul.

[@Excelmacromastery]

Thank you too Ismail

[@MohAboAbdo]

Thank You So Mutch, Sir.

[@peter00000001]

Nice, very good for the first steps, but I trying to understand the custom events for dynamically created objects on a form. But the interaction between the different classes is not clear for me. And on the internet I find not really a good explanation or example that give me the full insights. Is this something that will come in an future video?

[@WTC_2U]

Thanks Paul. your example shows just a single character being changed, triggering the event. What happens if we type a multi-character word/phrase - does the event trigger with each new character entered? If I type in "Paul", does the Change event trigger once, or four times?

[@Excelmacromastery]

It only triggers when the you finish entering data in a cell.

[@WTC_2U]

Excel Macro Mastery - thank you!

[@muriwangul.chinyerere5381]

Great video👏👏👏

[@Excelmacromastery]

Thank you 👍

[@krn14242]

Thanks Paul, I use this with drop down data validations alot. I like the idea about the GoTo though.

[@Excelmacromastery]

Thanks for always leaving your comments for my videos.

[@amarnadhg6896]

Long awaited for this, Thanks for post, keep doing videos on Events

[@Excelmacromastery]

Glad you liked it.

[@niharikagaikwad2865]

Hi Kenny! This video worked wonders for me. Thanks a lot! Also, what can I do if I have to run the same code in the same sheet for different blocks of cells when different letters are typed in? For eg: If I type D, another block of cell should get colored

[@tonyhaddad1088]

Thank you for your wonderful lessons Is there a way to make a column accepts only numeric values? Or perhaps date

[@trevorburford-reade8685]

Use Data Validation.

[@iidseans5442]

Thanks for the content. Now I'm trying to imagine how I can combine this with a timer. I want to use a WS.Active event to run a macro(Manual Calculate this sheet). But other macros are already in/activating the WS to un/protect all sheets as part of the open/close WB process. All I can think is to activate the WS and invoke a timer to run a macro to toggle WS.Calculate on and off. Anyone have a good idea?

[@UncleChopChop22]

Just got a notification for your latest video but won't let me watch as it says it's private?

[@dandeman1148]

Nice, but how can you use The event to copy and delete a row another sheet If an item in column 2 is a certain text

[@johnjoecawleyie6004]

Any Chance of you giving the code for this Paul. I have not managed to duplicate it myself.

[@TheUnicleo]

does Target.Address="$B$2" serve better than Intersect(..)?

[@jankichanddevi6538]

Hi, I learnt vba but I am not able to start any automation in my office. Would you please give me some tips to perfect in vba automation. Please advise.

[@ezequiasrocha3037]

Worksheet change event does not track the moment when I change the sheet name. 😔 By the way, why does Excel doesn't track the sheet renaming event?

[@kanbalan3043]

Hey this is fantastic I could create a data entry form where user will be able to check data entry error soon they finished entering the last digit fab I wonder you could solve this problem for me . here we are ... I created a data entry form and linked the database below so that user can select a particular row and then edit or delete so far so good The issue is I want to make this list to be in descending order and as and when a new row of data is entered this appear right at the top so that the user know what is the last piece of data entered

[@sapisoftware1942]

Hi Paul, awesome your videos! Could you to consider a couple videos of how to use MSXML2.XMLHTTP60 library to do webscraping? Especially querySelector instead of Xpath (it seems does not exists for this library). Regards!!

[@meldeebueno]

I have been using worksheet change for years. One problem I was having is that when I put formula on my target cell so that when that value changes, the cell that would reflect the resulting event should change too depending the value I set. However, it's not working like that because Excel is only proceeding with worksheet change when the cell value has changed and not merely the resulting value from a value (which Excel reads as non-modification).

[@meldeebueno]

Could have a lot of practical applications only if Excel reads values changes from formulas as modification but I get it.

[@meldeebueno]

@Carsten Mikkelsen Thank you, Carsten. Appreciate the help. This will help me a lot in doing my reports moving forward. Thank you for taking a chunk of your time for a clarification.

[@anandchhangani1031]

Thanks dear. Excellent video on VBA worksheet change event. I have been using it for a long time, but never exactly knew as to when I should enable or disable the events and that your video has cleared my confusions in this regard. Recently I am trying to save live streaming data of stock prices (already subscribed by me and which are streaming in my sheet1). The data actually appears and then next moment new data comes. The streaming data are coming in Range("A1:M1") and I want to capture and save and then analyze those data. For saving data I tried to use Worksheet Change event but it is not working and I don't know why it is not working. The worksheet change event has been coded as follows: Private Sub Worksheet_Change(ByVal Target As Range) Dim i As Integer Application.ScreenUpdating = False If Application.Intersect(Target, ActiveSheet.Range("A3:M3").Value) Is Nothing Then GoTo done Application.EnableEvents = False i = Sheet2.Range("A" & Rows.Count).End(xlUp).Row + 1 Range("A3:M3").Copy Sheet2.Range("A" & i) Application.CutCopyMode = False Application.ScreenUpdating = True Application.EnableEvents = True done: Exit Sub End Sub The code stops on line "If Application.Intersect showing run time error 424 (Object Required)........" About Target.Address the watch window says: "Out of Context". Any help....please point out the mistake...

[@christianbaert607]

Very useful vid ... as usual :-) Although, I am a bit puzzled with the Intersect command. Why not using "If Target.Address = "B2" then ... It seems easier to me. Or did I miss something ?

[@michael.andreae]

I guess this would work as long as only one cell is returned, but if you change more than one cell you get an Array back. Intersect is a clever way of handeling both cases.

[@christianbaert607]

@@michael.andreae Thanks a lot Michael ! You are right, of course. I should try that next time I need it.

[@Excelmacromastery]

Exactly what Michael said😀

[@ottoleisering7855]

Why put "exit sub" after the "done: label when the very next line is "end sub"?

[@Excelmacromastery]

Because you would normally add the error handling section after the exit sub.

[@alteavanloggerenberg2464]

Hi, what if you had 11 sheets but you only wanted 7 specific sheets to trigger the event change, how would you go about it?

[@david_allen1]

There is a Workbook_SheetChange event that runs after every Worksheet_Change event (there is one for each sheet) for every sheet in the workbook. You would have to put code in the Workbook_SheetChange event that checks for the name (or number) of the sheet which had a change (for example using Target.Parent.Name or the Sh.Name parameter specified in the event definition), and when the sheet matches one that you want to catch, execute your intended code; otherwise, just exit the sub. You could perform the check easily with a SELECT CASE construct or an IF/ELSEIF construct.

[@thearchibaldtuttle]

For the purpose of demonstration I forgive you the GOTO!

[@edwinmiranda4005]

Is there another way to handle that with out GoTo?

[@thearchibaldtuttle]

5.SL0 Like this: If Not Intersect(....) Is Nothing Then Do something End If

[@edwinmiranda4005]

Archibald Tuttle ok cool

[@Excelmacromastery]

I actually don't like this method because if you have multiple if then you end up with multiple levels of indentation. It makes the code difficult to read. doing an exit/goto upon failure makes the code flow more obvious. Of course it's down to a personal choice.

[@CANDEEIRO]

well... you can use conditional formatting instead. It's so simple... :D

[@naveenofficial3680]

What if target cell is set by another cell reference?? It is not working. I hv a sheet in which value of a cell is pasted by another cell every time. What can be done? Plzz guide me

[@Caracazz2]

Doesn't work with web query or any automatic cell change. Only works when user changes it manually.

[@wren4077]

Why does my target say "target = nothing " when highlighted

[@raypang8400]

dude not even following on the watch step, my file just keep giving me out of context in value for target address and empty in type and sometimes just dont respond at all

[@deechvogt1589]

I really like your videos but this time I do have an issue with the introduction of using the GoTo statement when you could have easily written the code to just drop through to the end sub. The done label and the Exit Sub statements were completely unnecessary.

[@ghoulsarefree]

You should add music to your videos.

[@ImAHugeTroll]

The reason i hesitate to use workbook change events is because as far as i know there is no way to undo your actions because you are triggering macros at every change. otherwise i think this would be a much more useful workbook event because so often i want to use it but cant justify it

[@Excelmacromastery]

That's true. You have to be careful with it.

[@tughanozsezer9332]

👍

[@user_2085]

This is not magic

[@mike_case]

Thank you Paul!!! Once again great content.

[@Excelmacromastery]

Thanks Michal