Worksheet Change Puzzle

Hi!

I seem to be in a bit of a logical mess. I have a worksheet set up so that each cell has a data validation list. When someone selects an item from the list, I run Application.Calculate from the Worksheet_Change function because there are other cells that are affected and need to be updated.

Now, the problem is that I have a few very large macros that change hundreds and hundreds of cells at one time - and when that occurs, you have the Application.Calculate function running hundreds and hundreds of times as well, slowing down the speed considerably.

I would like to have it both ways if I can - if someone changes one cell, I'd like to run the Application.Calculate, and when a large macro runs, I'd like to put off the Application.Calculate until the very end so it just runs once.

Any ideas? Thanks!

Dan

Create a global variable like
Dim IgnoreCalculate as Boolean
at the top of a general Module code sheet, outside any subs. It will start out False by default

Set this while your big subs run, and clear it when finished.

Check this before executing Application.Calculate in your other sub.

Awesome, thanks guys - I went with declaring the public boolean variable. I did this because I have a number of my rows grouped, and if Application.Calculate is ever automatic, it calculates each time I hide or show my rows.

Thanks again -

Dan