MY FULL EXCEL VBA COURSE (Beginner to Expert) [35% Discount]: www.teachexcel.com/vba-course-update.php?src=yt_pinned_comment_HsiwC9xg06c 200+ Video Tutorials - 200+ Downloadable Excel Reference Files - 50+ Hours of Content
Oh. My. God. I spent an hour googling stuff before I found this amazing video. The beginning part of where to put the code is the key, and not found anywhere else. Thank you!
This is great - I have looked at 20 different sites that all have different syntax - I have spun in circles trouble shooting a basic problem of the trigger not firing - I knew it was a simple issue - thanks !!!!
Excellent John! Now, if I change multiple cells all within the Intersect Range by dragging down the cell above, how can the script be changed to acknowledge the changed cells and react accordingly? The current example you used only recognizes the cell used to drag down.
Great! Is it "Thanks for the video = Something" or "Not thanks for the video = Nothing".. haha! For the single cell test, I thought that you could just use: If Target = Range("A1") Then.. But, after tinkering with your code, I discovered that leaving off the .Address from Target.Address and Range("A1").Address will produce some wonky results. Thanks for that bit of learning. Typically, I use the If Not Intersect method anyway, even for a single cell reference. For some time, it confused me, because I was thinking: "if (pause) not intersect() is nothing". Then I realized that it is: "if not (pause) intersect() is nothing". I hope that helps someone struggling with it. Once I put the mental pause in the right place, the code made total sense and I've never confused it since. Thanks for sharing your knowledge.. it helps me move forward a little bit each day. Thumbs up!
Glad to help man! I honestly almost always use the intersect method too but I know that the other one is easier for a lot of people and a lot of people just want one cell - I used the intersect method in another tutorial and someone mentioned this simple way to check for a single cell, but yea, it's almost like Excel and VBA just want to make our lives more difficult than they really need to be hah. (and copy/paste really is king for so much code lol)
Hi this demo is exactly what I’m looking for (change within a range). This is the cheeky part. Is it possible to run a demo working with Google Sheets? Many thx Del
Nice Video ! I appreciate that you've shared this useful clip with us . Can this method be applied in case of multiple ranges and execute corresponding subroutines ? If it was , Can you share such like method ?
I'm familiar with this VBA in clasic excell. But recently I noticed Automate tab in excel and code editor is not the clasic one. To make things fun scripts are being saved in cloud. BUT, it looks this is totaly diferent then clasic VB as SUB is not suported and we deal with "function" amd main function as part of it. How these new type of functions can be trigered by focus change ?
I'm a mac user. As soon as I hit (general) dropdown I get "Variable uses Automation type not supported in Visual Basic? I've read that I can code it in I typed what you wrote, but still no joy.
Great video thanks. I am using this to automate a report that inserts the now function when someone logs an issue. Since the now function isn't static the macro takes the columns where the timestamp is and pastes it to make it hardcoded. I am now trying to work out however how I can delete the query when it's been completed and not trigger the macro.
Hello, I'm looking for a way to run a specific macro based on the content of a specific cell. Something like, if cell A2 = "text 1", run macro 1... if cell A2 = "text 2", run macro 2... I don't need to know when it changed, only to run the macros.. Do you have any tutorial how to do this? Thank you very much!
Question: changes in column values does not work if the value in the cell is produced from an Index(Match). How can I make it check if the target.text has changed?
thank you!!! this was why the module i use does not run automatically. i thought there was an error in the coding of the module itself but there was no error whenever i manually run it. so it was either it exited the sub right away or something else. turns out it was just not called to run TT_TT
Need help, I have multiple sheets where each sheet has queries but its running when i do refresh all but i want each sheet should get refreshed only when i change the value/value from list.. how can be achieved using marco scripts or any other way.
I need help because I can't word my query properly and so I can't find an answer: to isolate my issue, we have 3 cells. cell A is my "h:mm:ss.000" , cell B is another person's "h:mm:ss.000", and cell C is "the difference of those times I'm using an IF() so that if my time is higher: do x, if it is lower, do y. The way I worded the function in order for it to work without requiring negative values was just to subtract A from B if true or B from A if false. So the result is the same regardless. Now, I want the colour of cell C to change colour depending on if the IF() was true or false. With this video, I was able to at least get the colour to change when I manually change A or B but I want a cell of my choice to change cell A and that change will change cell C. (cell A "my time" is based on an average of times, and when I see a colour on cell 3, I can refer to another cell that tells me how much change in time do I need to produce, or how much cushion am I growing) so I will try to demo one result and if my average time changes and it changes the IF() from false to true, cell c=time->in green A b c > c 1 my ave time a different time difference > show difference in time when false ->red font 2 > 3 > My only theory is I have to make a macro that does something that sets of another macro.
How can do the same with Worksheet calculate, because you method the user need to type , and this will trigger an action ...... but I would like to have this starting automatically
New sub here. I hv 2 questions. 1. Can i pass more parameters into this sub? 2. For a user to keep copy and paste this sub is not ideal. Can this go into a module or xlam file that everyone can use? Thanks!
Please I a need a help how to do this one please ? " When text in cell A1="YES" (text) then value at B1=10, If text in cell A1="NO", the value at B1=0, If text in cell A1=0, then value at B1="ERROR"
Hi this was great video for beginner. I learned how to Run a Macro when a User Changes a Specific Cell, Range, or Any Cell in Excel in very simple way. Could you please also load video, How to write code to track all modification done on one spreadsheet(Sheet1) for specific range Range - 'Sheet1'!$A$94!$A$3:$K$117" And track all log(MOdification ) details in different tab in same excel sheet Date || Day || Modified by || User Name || Cell Value change from || cell value change to Thanks in Advance !!
Bro I want vba code if even a single cell is changed in entire workbook,it should trigger a mail from outlook , trigger should happen on saving workbook
Try a PivotTable change event. Note that working with Slicers in vba is SLOW, so if you will do that, set the ManualUpdate property to True for all connected PivotTables before you change their slicer selections.
Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("A:A")) Is Nothing Then ActiveCell.Offset(-1, 0).Activate a = Sheets("Sheet2").Cells(Rows.Count, ("A")).End(xlUp).Row + 1 Sheets("Sheet2").Range("A" & a).Value = ActiveCell.Value ActiveCell.Offset(1, 0).Select End If End Sub sir i am using above vba code to auto update in new sheet 2. My data is dynamically updated therefore it is not updating in new sheet 2 if i enter data manually press enter it is updated but excel linking is not updated how to do that.
Yes, totally agree! But, once you learn it, you will actually start defaulting to doing things there instead of using formulas in the worksheet because of how much easier it can make things - weird to think that programming code can be easier than making in-cell formulas lol. My new vba course (coming soon) will help clear-up a lot of the confusion as well ;)
MY FULL EXCEL VBA COURSE (Beginner to Expert) [35% Discount]: www.teachexcel.com/vba-course-update.php?src=yt_pinned_comment_HsiwC9xg06c
200+ Video Tutorials - 200+ Downloadable Excel Reference Files - 50+ Hours of Content
copy it, paste it, forget it - such a good logic, used it so many times
Oh. My. God. I spent an hour googling stuff before I found this amazing video. The beginning part of where to put the code is the key, and not found anywhere else. Thank you!
Couldn't understand better. Amazingly explained. Thank you
Thanks a lot! Now I'll finally be able to do what I was trying to achieve.
This is great - I have looked at 20 different sites that all have different syntax - I have spun in circles trouble shooting a basic problem of the trigger not firing - I knew it was a simple issue - thanks !!!!
Excellent video!! Helped me understand a lot of things in one video!! Thank you!!
Brilliantly explained - helped me for firing on filling of values in range to send email automatically with macro2-thanks a lot
Excellent John! Now, if I change multiple cells all within the Intersect Range by dragging down the cell above, how can the script be changed to acknowledge the changed cells and react accordingly? The current example you used only recognizes the cell used to drag down.
Very well explained! In fact I was wondering how to run 2 worksheet change events in one Macro. I tried many times without success
Thanks!! I'm really glad I could help :)
Thank You
I Really Appreciate your way of explanation the topic you are talking about
Thank You Again, Have a Nice Time with Great Regards
SUPERB!
Clearly explained and well taught...
Hello there, do you know at 14:00 in your video why your typing at A6 is filled in yellow Color automatically? Thanks a lot.
Great! Is it "Thanks for the video = Something" or "Not thanks for the video = Nothing".. haha! For the single cell test, I thought that you could just use: If Target = Range("A1") Then.. But, after tinkering with your code, I discovered that leaving off the .Address from Target.Address and Range("A1").Address will produce some wonky results. Thanks for that bit of learning. Typically, I use the If Not Intersect method anyway, even for a single cell reference. For some time, it confused me, because I was thinking: "if (pause) not intersect() is nothing". Then I realized that it is: "if not (pause) intersect() is nothing". I hope that helps someone struggling with it. Once I put the mental pause in the right place, the code made total sense and I've never confused it since. Thanks for sharing your knowledge.. it helps me move forward a little bit each day. Thumbs up!
Glad to help man! I honestly almost always use the intersect method too but I know that the other one is easier for a lot of people and a lot of people just want one cell - I used the intersect method in another tutorial and someone mentioned this simple way to check for a single cell, but yea, it's almost like Excel and VBA just want to make our lives more difficult than they really need to be hah. (and copy/paste really is king for so much code lol)
Excellent video. I think this is going to help me alot.
Thank you! And I'm very glad to hear it :))
Hi this demo is exactly what I’m looking for (change within a range). This is the cheeky part. Is it possible to run a demo working with Google Sheets? Many thx Del
The options on the left and right drop down Menu, are the options only there from latest excel versions ?
Nice Video ! I appreciate that you've shared this useful clip with us .
Can this method be applied in case of multiple ranges and execute corresponding subroutines ?
If it was , Can you share such like method ?
Great video! It really helped me in solving the problems I was running into. Thank you!!
I'm familiar with this VBA in clasic excell. But recently I noticed Automate tab in excel and code editor is not the clasic one. To make things fun scripts are being saved in cloud. BUT, it looks this is totaly diferent then clasic VB as SUB is not suported and we deal with "function" amd main function as part of it. How these new type of functions can be trigered by focus change ?
You helped solve my problem! Many Thanks!
Excellent tutorial! Thanks!
I'm a mac user. As soon as I hit (general) dropdown I get "Variable uses Automation type not supported in Visual Basic? I've read that I can code it in I typed what you wrote, but still no joy.
Great video thanks. I am using this to automate a report that inserts the now function when someone logs an issue. Since the now function isn't static the macro takes the columns where the timestamp is and pastes it to make it hardcoded. I am now trying to work out however how I can delete the query when it's been completed and not trigger the macro.
Excellent! How would you go about having this work for every nth cell in a column? Like every 5th starting at A1
Excellent Tutorial. A huge help. Thank you
Hello, I'm looking for a way to run a specific macro based on the content of a specific cell. Something like, if cell A2 = "text 1", run macro 1... if cell A2 = "text 2", run macro 2... I don't need to know when it changed, only to run the macros.. Do you have any tutorial how to do this? Thank you very much!
hello I have long sheet when i click button, it should navigate to particular area (scrll down to the bottom) , do we have exampe?
Question:
changes in column values does not work if the value in the cell is produced from an Index(Match). How can I make it check if the target.text has changed?
thank you!!! this was why the module i use does not run automatically. i thought there was an error in the coding of the module itself but there was no error whenever i manually run it. so it was either it exited the sub right away or something else. turns out it was just not called to run TT_TT
excellent video, it helped me a lot!
Need help, I have multiple sheets where each sheet has queries but its running when i do refresh all but i want each sheet should get refreshed only when i change the value/value from list.. how can be achieved using marco scripts or any other way.
I need help because I can't word my query properly and so I can't find an answer:
to isolate my issue, we have 3 cells. cell A is my "h:mm:ss.000" , cell B is another person's "h:mm:ss.000", and cell C is "the difference of those times
I'm using an IF() so that if my time is higher: do x, if it is lower, do y. The way I worded the function in order for it to work without requiring negative values was just to subtract A from B if true or B from A if false. So the result is the same regardless. Now, I want the colour of cell C to change colour depending on if the IF() was true or false.
With this video, I was able to at least get the colour to change when I manually change A or B but I want a cell of my choice to change cell A and that change will change cell C. (cell A "my time" is based on an average of times, and when I see a colour on cell 3, I can refer to another cell that tells me how much change in time do I need to produce, or how much cushion am I growing)
so I will try to demo one result and if my average time changes and it changes the IF() from false to true, cell c=time->in green
A b c > c
1 my ave time a different time difference > show difference in time when false ->red font
2 >
3 >
My only theory is I have to make a macro that does something that sets of another macro.
I tried to do if range("cell A").formula = true then.... but it didn't do anything
Is there a way to make it print the specific cell that is changed?
Hi, supposing that i insert the string "cp" on A1 cell, what should i do to automatically return on A1 the string "completed" ? Thanks
Thanks a lot! it helped a great deal :)
I'm very glad to hear it :)
How can do the same with Worksheet calculate, because you method the user need to type , and this will trigger an action ...... but I would like to have this starting automatically
Does this work when cell changes it value automatically by formula ?
No it doesn't. Tried as i'm looking for the same solution
@@addynaidu6470 please share the link if you find a solution for this....
@@BruceWayne-mf6xf hope this helps
ua-cam.com/video/EndyCGyMw0Y/v-deo.html
Thank you so much for your great help.
Hi please how can I create a macro to activate at 8am and activate at 8.10am
Please add also if sheet name matched with special then other sheet nothing
You ara amazing bro
Great. Mega useful!
New sub here. I hv 2 questions.
1. Can i pass more parameters into this sub?
2. For a user to keep copy and paste this sub is not ideal. Can this go into a module or xlam file that everyone can use?
Thanks!
Please I a need a help how to do this one please ? " When text in cell A1="YES" (text) then value at B1=10, If text in cell A1="NO", the value at B1=0, If text in cell A1=0, then value at B1="ERROR"
Thanks !
I was hoping to find something where the target is an actual formula and it’ll run the macros if the value changes due to another cell being changed
how do i print Hi there in a cell instead of getting it as pop up, someone pls reply
Hi this was great video for beginner.
I learned how to Run a Macro when a User Changes a Specific Cell, Range, or Any Cell in Excel in very simple way.
Could you please also load video,
How to write code to track all modification done on one spreadsheet(Sheet1) for specific range Range - 'Sheet1'!$A$94!$A$3:$K$117"
And track all log(MOdification ) details in different tab in same excel sheet
Date || Day || Modified by || User Name || Cell Value change from || cell value change to
Thanks in Advance !!
Bro I want vba code if even a single cell is changed in entire workbook,it should trigger a mail from outlook , trigger should happen on saving workbook
is there a way to run a macro each time a slicer is used?
Try a PivotTable change event. Note that working with Slicers in vba is SLOW, so if you will do that, set the ManualUpdate property to True for all connected PivotTables before you change their slicer selections.
How to handle, Insert and delete Rows / Columns in the Range, so Worksheet_change doesn't not run
thanks a lot
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A:A")) Is Nothing Then
ActiveCell.Offset(-1, 0).Activate
a = Sheets("Sheet2").Cells(Rows.Count, ("A")).End(xlUp).Row + 1
Sheets("Sheet2").Range("A" & a).Value = ActiveCell.Value
ActiveCell.Offset(1, 0).Select
End If
End Sub
sir i am using above vba code to auto update in new sheet 2.
My data is dynamically updated therefore it is not updating in new sheet 2
if i enter data manually press enter it is updated but excel linking is not updated how to do that.
Thanks Sir
marvelous
Okay , this calls the macro only after pressing the enter key , but my data changes automatically , and then it's not calling the macro
Thanks
It doesn't work unless the cell selects manually.
PLZ MAKE VIDEO ON
HOW TO ADD PICTURE IN USERFORM ??
Just go ask this in our forum and you will get a MUCH faster response - it is not difficult to do. www.teachexcel.com/talk/microsoft-office?src=yt
Excel VBA logic is absolutely baffling at times.
Yes, totally agree! But, once you learn it, you will actually start defaulting to doing things there instead of using formulas in the worksheet because of how much easier it can make things - weird to think that programming code can be easier than making in-cell formulas lol. My new vba course (coming soon) will help clear-up a lot of the confusion as well ;)
I just searching this from a long time such a huge helping code. Like Cell_LostFocus()
Double negative explanation makes sense.
God Blass You.