Event when using a dropdown button (data Validation)

[mark hansen]

I'm trying to see if I can trigger code when I hit a drop down arrow on a data validation box. It seems the Private Sub Worksheet_Change event triggers after I have selected from the data validation. Is there an event triggered when I click on the data validation drop down box.

Here is what I'm trying to do. My workbook is large and it takes about 4 seconds to recalculate, down from 7 seconds after I reevaluated what I'm doing. I'm using a data validation box, with formulas on another page to shorten a list of user to pick from... That part works well, it the recalculation that slows things down. I really only need the configuration sheet to recalculation to pick up the first few characters from the entry, so the formulas can build a short list to display in the drop down box.

So far I have used Private Sub Worksheet_SelectionChange to turn off automatic calculation when I enter the cell, then I hoped to have just the configuration sheet recalculate to build the list when I click the dropdown arrow. Once I leave the cell I use Private Sub Worksheet_Change to turn auto calculation back on.

Can I do this?

 

[Fluff]

Is there an event triggered when I click on the data validation drop down box.

No there isn't.