Thanks Dinesh! The Static value did the trick for me, however there are situations where you have to change the static variable value just BEFORE launching the macro to avoid infinite loops 🙂
Awesome!, can you help me with for example if I sort or filter a table while the ActiveCell is outside the table or any other table that may be, and have some CheckBoxes inside the table, the the checkboxes move to new positions but their linked cells reference didn't update , so how could I fix that in order to always have the linked cell being the same in wich the respective checkbox is placed, updates despite sorting or filterin the table while the activecell could be outside the table? I already write a VBA code to reasign their linked cells, but I have to run it manually, I need this to be triggered automatically when the respective table changed the CheckBoxes positions. Thank you.
Thank you for the tutorial. One question I have is, if your table had multiple rows (lets say 15), and a number of the formulars updated at the same time in different rows. how would you run the 'CopyData' macro for all the updated cells at only?
I would appreciate your advice Mr. Takyar. • I see that action is triggered by a cell value change, that is similar to what I need. • I want to trigger if the value falls between 1 and 9. • But the cell being monitored is a function of the cell I want to update. • I mention this because it might involve circular references • Cells A, B & C are blank until values are entered via the keyboard. • Cell M is computed as a function of cells A, B, C • I want to monitor cell M…do something if the calculated value falls within the range 1 thru 9 • If M falls into the range 1 thru 9, , I want B = M otherwise leave B as it was. • I hope this makes sense… To complicate things I know zip about VBA
Hello sir I'm looking for a similar solution. Only in my case if the value in a range of cells change thru a formula or vlookup function can you still run a macro. Couldn't find a solution for this on Google or UA-cam. Thank you!!!
@@Exceltrainingvideos Thank you sir the video link shared above does take care of a range of values but you still have to input manually to see the change. ie you have to manually input values in column C to get the % calculation in column D. If the values coming to column C is thru a vlookup or a calculation that doesn't trigger the macro. i'm looking for such a solution. Would appreciate if you could guide, still thankful for your time and efforts sir. Good day!!!
Hi Sir, I am working on macro where I am facing macro based excel is closes automatically due to formula in the excel sheet. Please guide for the solution.
Hi Dinesh, great video. on this video Static variable only holds the value until workbook closed and reopened. how can I keep the same value even closing and reopening?
Thank you so much for this tutorial it really helped me in creating my trading algo, I just wanted to ask instead of calling another macro can i simply write code in this sub function?? Please reply
Hi Dinesh Sir, It's not working for me. I am not sure why. The code is below, which I have placed in the Private Sub Worksheet_Calculate() Static Myoldval
Hello Sir, It's indeed a good learning. In case I want to "call email()" with such changes (with a formula) in a cell, how to do that, macro running and shooting emails when changes done manually but when same changes happening with a formula, macro not responding on such changes, can you please help !!
Hello sir, first of all thanks for the tutorial. I want to ask you a question what if purchase price or quantity purchased column values are changed by any external event...How to auto run this macro in such condition?
This doesn't work now, all code completely same as yours but when I change value in other cells then pop up says, Compile error: Sub or Function not defined
@@Exceltrainingvideos my code is below, but it doesn't works, My Macro5 is great and working when i run it manually, but when formula changes values it doesn't start. What wrong Im doing Sir *My code* Option Explicit Private Sub Worksheet_Calculate() Static Myoldval If Range ("L4").Value Myoldval Then Call Macro5 Myoldval = Range ("L4").Value End If End Sub *Sub Macro5()* ' ' Macro5 Macro ' Keyboard Shortcut: Ctrl+Shift+T ' Range ("A1:01").Select Selection. End (xlDown).Selec ActiveCell.offset (1, 0).Range ("A1").Select Sheets ("Analysis").Select Range ("14:L4").Select Selection.Copy Sheets ("Record").Select Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _ xlNone, SkipBlanks:=False, Transpose:=False End Sub
Sir I am working with corporate office. I am using macro based file but some time college thief file for personal benefit. I need one macro which is trigger while open file wrong password Ones macro trigger add in excel application and excel application start self delete file data once open any excel file Pls suggest
Hey guys, Below is the code and I want to lock cells A2 and A3 after cell A1 (A1 = B1+C1) is changing in results change either B1 or C1 or both. But it does not work. Could anyone help with that, please? Dim sPass sPass = "123" Dim rng As Range Set rng = [A2:A3] If Not Intersect(rng, [A1]) Is Nothing Then With ActiveSheet .Unprotect Password:=sPass .Cells.Locked = False Static oldValue If Range("A1") oldValue Then rng.Locked = True .Protect Password:=sPass oldValue = Range("A1").Value End If End With End If End Sub
God this is exactly what I was looking for. Not only was it a great video but it was clear, concise and easy to understand. Thank you
Thank you Very much. been stuck on this for weeks and now you solved it for me. Big thanks
Thanks Dinesh! The Static value did the trick for me, however there are situations where you have to change the static variable value just BEFORE launching the macro to avoid infinite loops 🙂
Your teaching style is awesome
Glad you think so!
thanks i am looking for this since 1 month but not getting it from anywhere. thank you a lot and God Bless You & Your Family
Thankyou sir!!!! It is very helpful...I applied on slicer value change.
It is amazing
Thank you! Just what I was looking for!
Awesome!, can you help me with for example if I sort or filter a table while the ActiveCell is outside the table or any other table that may be, and have some CheckBoxes inside the table, the the checkboxes move to new positions but their linked cells reference didn't update , so how could I fix that in order to always have the linked cell being the same in wich the respective checkbox is placed, updates despite sorting or filterin the table while the activecell could be outside the table? I already write a VBA code to reasign their linked cells, but I have to run it manually, I need this to be triggered automatically when the respective table changed the CheckBoxes positions. Thank you.
Thank you for the tutorial. One question I have is, if your table had multiple rows (lets say 15), and a number of the formulars updated at the same time in different rows. how would you run the 'CopyData' macro for all the updated cells at only?
Thank you . Please provide the way by which old value saved in same row in next column in same sheet for entire column.
I would appreciate your advice Mr. Takyar.
• I see that action is triggered by a cell value change, that is similar to what I need.
• I want to trigger if the value falls between 1 and 9.
• But the cell being monitored is a function of the cell I want to update.
• I mention this because it might involve circular references
• Cells A, B & C are blank until values are entered via the keyboard.
• Cell M is computed as a function of cells A, B, C
• I want to monitor cell M…do something if the calculated value falls within the range 1 thru 9
• If M falls into the range 1 thru 9, , I want B = M otherwise leave B as it was.
• I hope this makes sense…
To complicate things I know zip about VBA
Use an IF condition like so: www.exceltrainingvideos.com/if-else-function-using-vba-in-excel/
Thanks! Sir, with a Beautiful voice.
Glad to know!
Hello sir I'm looking for a similar solution. Only in my case if the value in a range of cells change thru a formula or vlookup function can you still run a macro.
Couldn't find a solution for this on Google or UA-cam.
Thank you!!!
This VBA lesson will guide: www.exceltrainingvideos.com/calculate-change-percentage-automatically-as-you-type/
Or search www.exceltrainingvideos.com
@@Exceltrainingvideos Thank you sir the video link shared above does take care of a range of values but you still have to input manually to see the change. ie you have to manually input values in column C to get the % calculation in column D.
If the values coming to column C is thru a vlookup or a calculation that doesn't trigger the macro. i'm looking for such a solution. Would appreciate if you could guide, still thankful for your time and efforts sir. Good day!!!
Hi Sir, I am working on macro where I am facing macro based excel is closes automatically due to formula in the excel sheet. Please guide for the solution.
Thanks! Excel Doctor!
Glad it was helpful! Please share the VBA tutorial with your friends too.
Hi Dinesh, great video. on this video Static variable only holds the value until workbook closed and reopened. how can I keep the same value even closing and reopening?
Off the cuff: Store the value in a cell.
@@Exceltrainingvideos Thanks Dinesh, I have done it. On open workbook redefined old value
Hello. I wanted to know how to trigger a macro when the value of the cell is changing because of a TR function
what is a TR function?
Thank you so much for this tutorial it really helped me in creating my trading algo, I just wanted to ask instead of calling another macro can i simply write code in this sub function?? Please reply
Yes you can!
Nicely explain
Hi Dinesh Sir, It's not working for me. I am not sure why. The code is below, which I have placed in the
Private Sub Worksheet_Calculate()
Static Myoldval
If Range("F5").Value Myoldval Then
Call Division_Click
Myoldval = Range("F5").Value
End If
End Sub
Did you create this macro Division_Click?
This link will help: www.exceltrainingvideos.com/run-macro-when-result-of-formula-changes/
Hello Sir, It's indeed a good learning.
In case I want to "call email()" with such changes (with a formula) in a cell, how to do that, macro running and shooting emails when changes done manually but when same changes happening with a formula, macro not responding on such changes, can you please help !!
Glad you find the channel useful. What formula are you using?
Sir, it transfer only G4 cell, sir i want to trasfer B4:G4, what to do ?
pls help.
Hello sir, first of all thanks for the tutorial. I want to ask you a question what if purchase price or quantity purchased column values are changed by any external event...How to auto run this macro in such condition?
Yes, you can
Excellent sir ..
So nice of you. Please share the VBA tutorial with your friends too.
Thank you for for the vedio but Why does the macro run every time I open excel ?
Are you using Workbook_Open() method or Auto_Open() method?
No sir Workbook_ calculate ()
i tried exact same code. but it didnt work at all. could you please help on this
All code is tested thoroughly on my laptop before publishing.
This doesn't work now, all code completely same as yours but when I change value in other cells then pop up says,
Compile error:
Sub or Function not defined
TYPO!
@@Exceltrainingvideos ? I didn't understand what it means TYPO
@@mr.himalayan6258 Mistake while typing!
@@Exceltrainingvideos my code is below, but it doesn't works, My Macro5 is great and working when i run it manually, but when formula changes values it doesn't start. What wrong Im doing Sir
*My code*
Option Explicit
Private Sub Worksheet_Calculate()
Static Myoldval
If Range ("L4").Value Myoldval Then
Call Macro5
Myoldval = Range ("L4").Value
End If
End Sub
*Sub Macro5()*
'
' Macro5 Macro
' Keyboard Shortcut: Ctrl+Shift+T
'
Range ("A1:01").Select
Selection. End (xlDown).Selec
ActiveCell.offset (1, 0).Range ("A1").Select
Sheets ("Analysis").Select
Range ("14:L4").Select
Selection.Copy
Sheets ("Record").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
End Sub
Sir
I am working with corporate office.
I am using macro based file but some time college thief file for personal benefit.
I need one macro which is trigger while open file wrong password
Ones macro trigger add in excel application and excel application start self delete file data once open any excel file
Pls suggest
Search www.exceltrainingvideos.com/ or this channel.
Have not any suggestions regarding this, please suggest if you have
Awesome - thanks
You're welcome! Please share with your friends.
I tried it. My cell is connected with a query. And code keeps running endlessly.
Try again thoughtfully.
Hey guys,
Below is the code and I want to lock cells A2 and A3 after cell A1 (A1 = B1+C1) is changing in results change either B1 or C1 or both. But it does not work. Could anyone help with that, please?
Dim sPass
sPass = "123"
Dim rng As Range
Set rng = [A2:A3]
If Not Intersect(rng, [A1]) Is Nothing Then
With ActiveSheet
.Unprotect Password:=sPass
.Cells.Locked = False
Static oldValue
If Range("A1") oldValue Then
rng.Locked = True
.Protect Password:=sPass
oldValue = Range("A1").Value
End If
End With
End If
End Sub
É us guri