Got a version of Excel that uses the menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon interface.
With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company.
Learn more about Allen...
If you have Excel 97 and you create a macro that runs whenever a change occurs in your worksheet, you may have noticed an interesting phenomenon--the macro doesn't always run when you think it should. It runs just fine if you enter a new formula in a cell or enter a new value, but it doesn't run if you use data validation and someone picks something from the validation drop-down list.
Microsoft knows about this problem. In fact, you can find more information about it in the Knowledge Base, here:
There are essentially several ways you can work around the problem. First, you could upgrade to a newer version of Excel. I'm not exactly sure which version it was corrected in, but I do know that in Excel 2003 selecting something from a data validation drop-down does trigger the Worksheet_Change event.
The second option is to use some other method of changing the worksheet data than with data validation. For instance, you could use a combo box from the Controls toolbox. Setting one up is a bit more difficult than using data validation, but much more versatile.
Finally, you could create a formula reference to the validated cell, and then trigger your macro from the Worksheet_Calculate event. For instance, if your validated cell is A7, then you could use the formula =A7 in a different cell. When the value in A7 changes (the user selects from the drop-down list), then a recalculation is begun because the results of the formula change. This, of course, triggers the Worksheet_Calculate event, where you could implement your macro. (Since the Worksheet_Calculate event can be triggered by lots of changes, you would need to add some checking code to your handler to make sure that the validation change is what ultimately triggered the event.)
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (2398) applies to Microsoft Excel 97.
Save Time and Supercharge Excel! Automate virtually any routine task and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! Mastering advanced Excel macros has never been easier. Check out Excel 2010 VBA and Macros today!