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

4 жыл бұрын

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

3 жыл бұрын

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

@twinkle547

2 жыл бұрын

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

This is the best content for VBA on youtube

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.

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

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

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

@Excelmacromastery

3 жыл бұрын

My pleasure!

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

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!

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

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

fantastic explanation of events! learned stuff

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

@Excelmacromastery

4 жыл бұрын

Thanks Bawlsackin.

Thanks a lot. Very interesting topic with many pitfalls.

Thanks! Very helpful tutorial.

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

This is really cool man, thanks!

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

@Excelmacromastery

4 жыл бұрын

Thanks for your support Salah.

Really excellent!!!!

Excellent and very useful video. Thank you

Thank you Paul!!! Once again great content.

@Excelmacromastery

4 жыл бұрын

Thanks Michal

Thank you very much

Excellent as usual. Thanks Paul.

@Excelmacromastery

4 жыл бұрын

Thanks K D

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 video! Thanks Paul.

@Excelmacromastery

4 жыл бұрын

Thanks Prince.

Good explanation!

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

4 жыл бұрын

glad to help Chris.

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

Another very useful and didactic video. Thanks Paul.

@Excelmacromastery

4 жыл бұрын

You're welcome. Thanks Joao.

Thank You So Mutch, Sir.

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

@Excelmacromastery

4 жыл бұрын

You're welcome Nelson.

Thank you again Paul.

@Excelmacromastery

4 жыл бұрын

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!

@Excelmacromastery

4 жыл бұрын

Thanks Rich

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.

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 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...?

Great video👏👏👏

@Excelmacromastery

4 жыл бұрын

Thank you 👍

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 cant undertand why u dont have more then 1 M subs. AWESOME videos!

@Excelmacromastery

3 жыл бұрын

Thanks. Glad you like it.

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.

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

@Excelmacromastery

4 жыл бұрын

Thanks for always leaving your comments for my videos.

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

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?

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

10 ай бұрын

I was wondering about this also

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

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

@trevorburford-reade8685

4 жыл бұрын

Use Data Validation.

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?

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

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

@Excelmacromastery

4 жыл бұрын

Glad you liked it.

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

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

@Excelmacromastery

4 жыл бұрын

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

4 жыл бұрын

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

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

4 жыл бұрын

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

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...

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

👍

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

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?

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.

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

For the purpose of demonstration I forgive you the GOTO!

@edwinmiranda4005

4 жыл бұрын

Is there another way to handle that with out GoTo?

@thearchibaldtuttle

4 жыл бұрын

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

@edwinmiranda4005

4 жыл бұрын

Archibald Tuttle ok cool

@Excelmacromastery

4 жыл бұрын

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.

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

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

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

4 жыл бұрын

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

@terrycline8689

4 жыл бұрын

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).

@meldeebueno

4 жыл бұрын

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

@meldeebueno

4 жыл бұрын

@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 ?

@michael.andreae

4 жыл бұрын

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

4 жыл бұрын

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

@Excelmacromastery

4 жыл бұрын

Exactly what Michael said😀

You should add music to your videos.

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

@Excelmacromastery

4 жыл бұрын

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

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

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

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

@Excelmacromastery

4 жыл бұрын

That's true. You have to be careful with 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?

@david_allen1

8 ай бұрын

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.

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