This is the single MOST IMPORTANT topic, excel users ever need to know, owing to the fact that the excel is inherently very VULNERABLE to human errors whether intentional or unintentional.
This code is great thank you. Do you by any chance know how to monitor a Cell that is linked to a dropdown control value? I have a dropdown that is populating a cell with the number of the choice. but this is not currently updating in the Table. The value changes in the table but it does not cause the event to fire off. There are other cells that take a manual input. when that occurs the msgbox fires off.
Great video. now in msgbox shows let say B3 was 7 and changed to 3 and this shows only in msgbox, but not in table? were it should shows update the table as well as it shows in msgbox too . in Previous value should be 3 and current should be 7..
If you are in the worksheet that you want to monitor and there are no linked cells that will change the value of the monitored cell then yes you could do that. In this case, put the code to monitor the change within the worksheet and not ThisWorkbook.
@@TeachExcel if there are linked cell in the worksheet then I assume I should use the calculate event ? Or the sheetchange event ? By the way, I am taking your VBA course right now. It is really good and you explain it really well
I'm really glad you like it!!!! :) Yes, you are correct. The normal way to track a cell being changed or updated is to use an event that tells you which cell was changed or updated, like the Worksheet_Change event or the Workbook_SheetChange event and then check if the changed cells are the cells that you want to monitor. However, in this tutorial I don't directly monitor a cell, but, instead, I check the Table of values that I monitor after every change in the workbook/worksheet; in this case, I could use another event, such as the Calculate event, because I don't need to be given the cells that have changed by the event and I can account for a cell being changed due to a linked cell being updated. But, this means that I do need to use a Table to store cell values so I can know if anything in the cells that I care about was changed. The first method that I mention here requires you to monitor all linked cells instead of just monitoring a central Table. This can sound confusing but if you play around with this in the workbook, you will quickly start to notice the differences in the two methods that I mention here. I hope this didn't seem too confusing.
In the case of this tutorial, there isn't much difference unless you care about the actual originally changed cell/worksheet. However, the calculate event will not run if you just input text into a cell and no formula is changed as a result - the change event will run in this case. This seems to be the biggest difference, but my tutorial here uses formulas and so both events trigger for the same changes.
This is the single MOST IMPORTANT topic, excel users ever need to know, owing to the fact that the excel is inherently very VULNERABLE to human errors whether intentional or unintentional.
This code is great thank you. Do you by any chance know how to monitor a Cell that is linked to a dropdown control value? I have a dropdown that is populating a cell with the number of the choice. but this is not currently updating in the Table. The value changes in the table but it does not cause the event to fire off. There are other cells that take a manual input. when that occurs the msgbox fires off.
Very nice tutorial 👌
Thank you Nader :)
sir how can we use auto pop up msg in the userform in which a cmd btn linked to specific cell value changes
Great video. now in msgbox shows let say B3 was 7 and changed to 3 and this shows only in msgbox, but not in table?
were it should shows update the table as well as it shows in msgbox too . in Previous value should be 3 and current should be 7..
Can it tell you who made the change?
Can we use a change event instead of the sheetchange ?
If you are in the worksheet that you want to monitor and there are no linked cells that will change the value of the monitored cell then yes you could do that. In this case, put the code to monitor the change within the worksheet and not ThisWorkbook.
@@TeachExcel if there are linked cell in the worksheet then I assume I should use the calculate event ? Or the sheetchange event ? By the way, I am taking your VBA course right now. It is really good and you explain it really well
I'm really glad you like it!!!! :)
Yes, you are correct.
The normal way to track a cell being changed or updated is to use an event that tells you which cell was changed or updated, like the Worksheet_Change event or the Workbook_SheetChange event and then check if the changed cells are the cells that you want to monitor. However, in this tutorial I don't directly monitor a cell, but, instead, I check the Table of values that I monitor after every change in the workbook/worksheet; in this case, I could use another event, such as the Calculate event, because I don't need to be given the cells that have changed by the event and I can account for a cell being changed due to a linked cell being updated. But, this means that I do need to use a Table to store cell values so I can know if anything in the cells that I care about was changed.
The first method that I mention here requires you to monitor all linked cells instead of just monitoring a central Table. This can sound confusing but if you play around with this in the workbook, you will quickly start to notice the differences in the two methods that I mention here.
I hope this didn't seem too confusing.
@@TeachExcel I am just confused on when to choose the calculate vs the sheetchange event since both methods are targeting linked cells
In the case of this tutorial, there isn't much difference unless you care about the actual originally changed cell/worksheet. However, the calculate event will not run if you just input text into a cell and no formula is changed as a result - the change event will run in this case. This seems to be the biggest difference, but my tutorial here uses formulas and so both events trigger for the same changes.
excel cell value auto save from changing values of maximum or minimum value / high or low
It should be save in another cell