Running a Macro off Data Validation Values

**Sky188** · 06-07-2012, 04:59 PM

Is there a function that will run a macro every time a bit of information is updated?

For example, if I had a data validation list with Chevy, Toyota, and Mozda set in A1 and I have a macro called Macro1 that will hide row 11, Macro 2 that will hide row 12, and Macro3 that will hide row 13, is there any way to set up a function so that if A1 = "Chevy", run Macro1, if A1 = "Toyota", run Macro2, and if A1 = "Mozda", run Macro3?

Part 2 if this question:
If the above function is possible, can I use an If statement or some other sort of function so it is set up to unhide a row if the returned value is false? Aka, A1 = Chevy, so it runs Macro 1 to hide Row 11, and runs Macro 4 and 5 to unhide rows 12 and 13. Then if A1 is changed to = Toyota, it runs Macro 2 to hit Row 12, and runs Macro 4 and 6 to unhide rows 11 and 13, etc.

Thanks for your time and response!

**tigeravatar** · 06-07-2012, 05:05 PM

Sky188,

I'm assuming it's "Mazda" and not "Mozda". Also, I would discourage you from calling a multitude of other macros rather than just putting the code in the routine that will be calling them anyway.

As to your question, you can use the worksheet_change event. The code would go in the appropriate sheet code module. Here's how it would look given your description:

Please Login or Register  to view this content.

**Sky188** · 06-11-2012, 03:41 PM

Thanks for getting back to me on this tigeravatar, I really appreciate it!

Is there any chance you could post an excel sheet with the above code working properly so I can mess around with it? I've never seen, used, or heard of the "event" capabilities of excel and the above code is completely unknown to me.

Thanks a ton!

**tigeravatar** · 06-11-2012, 04:02 PM

Sky188,

Attached is an example workbook based on the criteria described. When you select an item from the drop-down list in A1, the appropriate row is hidden.

To view the code, press Alt+F11 to open the Visual Basic Editor (VBE) and then double-click on 'Sheet1' to view the Sheet1 code module.