How to prevent the update of a cell in Excel?

Question

I have a pretty large Range (10,000 rows, 10 cols) that I fill line by line on an everyday basis. I also have a smaller Range (366 rows, 5 cols), in which, for every cell, I run a macro. That macro does pretty much what DSUM or SUMIF do, but with multiple criteria. The problem is that after having this fancy stuff implemented, I can't fill my stuff decently, coz on every cell I fill the macro is ran dozens of times, and every times it loops hundreds, taking around 5 seconds to let me fill the next cell.

I'd like to be able to prevent the smaller range to be updated (or the macro not to be ran), so I can fill it all normally, and then press a button to update everything.

Answer 1

You will need to lock the target range (setting the Locked property of the Range to true). Then you will need to protect the sheet by calling the Protect() method of your worksheet. Below are examples of how to lock and unlock the cells A1:A4.

Locks:

Sheet1.Range("A1:A4").Locked = true
Sheet1.Protect()

Unlocks:

Sheet1.Unprotect()

Edit: After re-reading the question, it seems as though the OP is using the Worksheet_Change event. To which I have one word:

STOP

Just put in an ActiveX button into the sheet, and then assign a macro to that, as described here.

The Worksheet_Change event is fraught with peril. Beware ye who enter, for there be dragons.

Answer 2

Well there's this:

      Application.Calculation = xlCalculationManual

But that stops all recalculations, until you rest it to automatic or explicity call for a Recalc. Is that sufficient, or do you need more?

If you need to limit the recalc restriction to only prevnting specific macros from executing, then the following:

1. Add a "Global IShouldNotRun as Boolean" to your macro(s)
2. Your macro(s) should check the IShouldNotRun value and exit if it is true.
3. write another macro that toggles this global.
4. Have whatever is doing you data loading call the routine to set the global to True.
5. Reset it after you are done loading and call Calculate, CalculateFull or CalculateFullRebuild.

Answer 3

I don't have ecell on my computer so I cannot check the exact path, but as far as I remember after going to Tools/Options menu there is a tab 'calculation' or 'computation' where you can disable the automatic formula recalculation.