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.
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.
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!
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!
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
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!
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...?
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?
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?
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
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!!
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.
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...
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
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.
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?
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?
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.
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 ?
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.
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).
@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.
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.
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
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
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
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.
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?
(newb) Is there a reason why I can't use
If Target = Range("B2") Then
instead of
If Application.Intersect... Goto done: ?
Hi can we lock the entire worksheets without protecting using VBA code
This is the best content for VBA on youtube
I never knew it was possible to use the application dot in the immediate window thanks!
You're out here making a positive impact on more lives than you know. Thank you sir!
My pleasure!
Awesome. I may have said this before but I’ll say it again - this is the #1 primo VBA code site!
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.
Nice work, Paul! Very timely; I was just recently dealing with needing this functionality.
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
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.
glad to help Chris.
Your channel is a mine of usefulness! Cheers! Stay awesome!
Thanks for your support Salah.
Thanks a lot. Very interesting topic with many pitfalls.
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.
Great. Thank you Paul for letting me discover a totally unknown functionality of Excel VBA.
You're welcome Nelson.
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.
Great video! Could have used it earlier today. Took me a bit to remember the enable events. Cheers!!
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!
Thanks Rich
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!
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.
i cant undertand why u dont have more then 1 M subs. AWESOME videos!
Thanks. Glad you like it.
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
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!
I was wondering about this also
Thanks! Very helpful tutorial.
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...?
thank you Paul... I learned something new again!
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?
Excellent and very useful video. Thank you
Great tips! Thanks Paul :)) Thumbs up!!
Excellent as usual. Thanks Paul.
Thanks K D
Great video again. Also the addition of using Call Stack... Never knew that existed. Really useful in debugging!
This is really cool man, thanks!
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?
fantastic explanation of events! learned stuff
Great video. You have a new subscriber. Do you have anything similar for comboboxes. They are really troubleshooting for me.
I love your videos. Thanks so much for taking the time to put these together!
Thanks Bawlsackin.
Another very useful and didactic video. Thanks Paul.
You're welcome. Thanks Joao.
Really excellent!!!!
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
Great video! Thanks Paul.
Thanks Prince.
Again proved you are master in VBA code.
May I know how many years experience in VBA coding?
Do you have a video about the call stack? This was the first time I’ve seen that window used.
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.
First time I heard of call stack too, and I’ve been working with VBA for 2 years now
Thank you again Paul.
Thank you too Ismail
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?
That's fine when only one cell is returned but if multiple cells are returned they it won't work.
Thanks Paul, I use this with drop down data validations alot. I like the idea about the GoTo though.
Thanks for always leaving your comments for my videos.
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
Hey... Thank you sooo much bro... 😊
Thank you very much
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!!
For the purpose of demonstration I forgive you the GOTO!
Is there another way to handle that with out GoTo?
5.SL0
Like this:
If Not Intersect(....) Is Nothing Then
Do something
End If
Archibald Tuttle ok cool
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.
Good explanation!
Thank You So Mutch, Sir.
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...
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
Just got a notification for your latest video but won't let me watch as it says it's private?
is it possible to triger worksheet change event to act like combobox change event?
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.
Thank you for your wonderful lessons
Is there a way to make a column accepts only numeric values?
Or perhaps date
Use Data Validation.
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
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?
Any Chance of you giving the code for this Paul. I have not managed to duplicate it myself.
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?
It only triggers when the you finish entering data in a cell.
Excel Macro Mastery - thank you!
Great video👏👏👏
Thank you 👍
well... you can use conditional formatting instead. It's so simple... :D
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.
does Target.Address="$B$2" serve better than Intersect(..)?
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 ?
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.
@@michael.andreae Thanks a lot Michael ! You are right, of course. I should try that next time I need it.
Exactly what Michael said😀
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).
Could have a lot of practical applications only if Excel reads values changes from formulas as modification but I get it.
@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.
Doesn't work with web query or any automatic cell change. Only works when user changes it manually.
Why put "exit sub" after the "done: label when the very next line is "end sub"?
Because you would normally add the error handling section after the exit sub.
Long awaited for this, Thanks for post, keep doing videos on Events
Glad you liked it.
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?
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.
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
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?
You should add music to your videos.
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
Why does my target say "target = nothing " when highlighted
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
That's true. You have to be careful with it.
👍
This is not magic
Thank you Paul!!! Once again great content.
Thanks Michal