Excel VBA: The Magic of the Worksheet Change Event
Вставка
- Опубліковано 24 лип 2024
- 👉 Ready to master VBA?
- Check out my full courses: courses.excelmacromastery.com/
- Subscribe to the channel here: bit.ly/36hpTCY
- FREE CHEAT SHEET: Get the free cheat sheet on VBA arrays here: (bit.ly/2MXsnz9)
(Note: If the download page doesn't work then make sure to turn off any popup blockers)
Excel VBA: The Magic of the Worksheet Change Event
#vbaevent #vbachangeevent
Useful VBA Shortcut Keys
========================
Search keyword under cursor: Ctrl + F3
Search the word last searched for: F3
Compile the code: Alt + D + C OR Alt + D + Enter
Run the code from the current sub: F5
Step into the code line by line: F8
View the Immediate Window: Ctrl + G
View the Watch Window: Alt + V + H
Auto complete word: Ctrl + Space
Get the definition of the item under the cursor: Shift + F2.
Go to the last cursor position: Ctrl + Shift + F2.
Switch between Excel and the VBA Editor: Alt + F11.
View the Project Explorer Window: Ctrl + R.
Get the current region on a worksheet: Ctrl + Shift + 8(or Ctrl + *).
To move lines of code to the right(Indent): Tab.
To move lines of code to the left(Outdent): Shift + Tab.
View the Properties Window: F4.
Add a breakpoint to pause the code: F9(or click left margin).
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
thank you Paul... I learned something new again!
Great video again. Also the addition of using Call Stack... Never knew that existed. Really useful in debugging!
Great video! Could have used it earlier today. Took me a bit to remember the enable events. Cheers!!
fantastic explanation of events! learned stuff
I love your videos. Thanks so much for taking the time to put these together!
Thanks Bawlsackin.
I never knew it was possible to use the application dot in the immediate window thanks!
Thanks! Very helpful tutorial.
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!
Your channel is a mine of usefulness! Cheers! Stay awesome!
Thanks for your support Salah.
This is really cool man, thanks!
Thanks a lot. Very interesting topic with many pitfalls.
Great tips! Thanks Paul :)) Thumbs up!!
This is the best content for VBA on youtube
Thank you Paul!!! Once again great content.
Thanks Michal
Excellent as usual. Thanks Paul.
Thanks K D
Great video! Thanks Paul.
Thanks Prince.
Really excellent!!!!
Another very useful and didactic video. Thanks Paul.
You're welcome. Thanks Joao.
Excellent and very useful video. Thank you
Great. Thank you Paul for letting me discover a totally unknown functionality of Excel VBA.
You're welcome Nelson.
Thank you very much
You're out here making a positive impact on more lives than you know. Thank you sir!
My pleasure!
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.
Awesome. I may have said this before but I’ll say it again - this is the #1 primo VBA code site!
Thank you again Paul.
Thank you too Ismail
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
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.
Thank You So Mutch, Sir.
Good explanation!
Hey... Thank you sooo much bro... 😊
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
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
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.
Great video👏👏👏
Thank you 👍
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
i cant undertand why u dont have more then 1 M subs. AWESOME videos!
Thanks. Glad you like it.
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!!
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...?
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.
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
Thank you for your wonderful lessons
Is there a way to make a column accepts only numeric values?
Or perhaps date
Use Data Validation.
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. You have a new subscriber. Do you have anything similar for comboboxes. They are really troubleshooting for me.
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?
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
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?
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.
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.
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
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.
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
Long awaited for this, Thanks for post, keep doing videos on Events
Glad you liked it.
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.
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?
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?
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...
well... you can use conditional formatting instead. It's so simple... :D
Any Chance of you giving the code for this Paul. I have not managed to duplicate it myself.
does Target.Address="$B$2" serve better than Intersect(..)?
👍
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!
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.
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😀
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.
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.
Doesn't work with web query or any automatic cell change. Only works when user changes it manually.
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
You should add music to your videos.
Why does my target say "target = nothing " when highlighted
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.
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.
Great as always!!!
Paul, can we set another workbook's userform with the UserForms.Add method? for example, I want this to work:
--------------------------------------------------------
Dim FRM_atual as object
Set FRM_atual = UserForms.Add("another_workbook!userform_name")
debug.print FRM_atual.name
----------------------------------------------------------
This code is in one workbook, and the userform I want to set as object is in another workbook. Please, Paul, can you help me? I've searched but the only way i see is to put this code temporary in the other workbook, do the stuff i want and after delete it. There is probably a better way to do that.
This is not magic