VBA has the ability to be triggered by "events" that occur while a user is in Excel. But make sure you turn the calculation setting back to automatic or semi-automatic or you will cause extreme confusion to yourself or your user.
#S AND S CODE#
Significant runtime efficiency can be made if you turn automatic calculations off while your VBA code is executing. If your code is affecting cells that feed cell formulas, Excel will try to recalculate the values in real time. You can find this code over at the Code Vault section of my website. If you want to be an overachiever, you can add some VBA that turns off every single Page Break currently in existence during your code's runtime. If your code will be modifying multiple worksheets, you may want to turn off the displaying of Page Breaks for all the sheets getting affected. To keep this article's code clean, I assumed the macro would only affect the ActiveSheet. This means there is a "light switch" in each worksheet of your workbook. A Page Break is a worksheet-level setting rather than an application-level setting. Now Page Breaks are different from the other settings I cover in this article. Because Page Breaks need to constantly recalculate, it is good to play it safe and shut them off while your code is being executed You could image the time consumption that might take place if you are running VBA code that is deleting or adding thousands of rows to a spreadsheet. One bad thing about page breaks when you have VBA code running is they want to recalculate the "breaks" whenever a change is made to the spreadsheet. I have a personal hatred for the visualization of page breaks, but I suppose there are a few of you out there who might like them. The only scenario I can think of where you might want it on is if your code is displaying some sort of animation on screen. 99.9% of the time, you will ALWAYS want to turn OFF Screen Updating. I would always say she was trying to brainwash me through the macro because there was non-stop screen-flickering for 30 straight minutes! Little did I know that a single line of code would have saved my eyes (and mind) from deterioration and at the same time speed up the code's runtime dramatically. The first macro I ever ran was a co-worker's that took 30 minutes to finish executing (it did a lot of stuff and was very poorly written). Let's look at these settings in detail to appease those of you who want to know why this code works. Your computer is simply trying to do too many things at once. This slows your computer's processing power down and in turn decreases your computer's ability to run your code as fast as it can. The above VBA code I just shared with you is simply turning off settings from within your Office application that want to use your computer's memory to report out on your computer screen every single thing your macro is doing (in real-time).