How to prevent cell update in Excel?

Question

I have a fairly large range (10,000 lines, 10 ears), which I fill line by line every day. I also have a smaller Range (366 lines, 5 cols) in which for each cell I run a macro. This macro does pretty much what DSUM or SUMIF do, but with a few criteria. The problem is that after this fantastic material has been implemented, I can’t fill my material decently, coz on every cell that I fill, the macro works dozens of times, and every time it loops hundreds, taking about 5 seconds to I could fill in the next cell.

I would like to be able to prevent a small range from updating (or the macro will not start), so I can fill it up normally, and then click a button to update everything.

Answer 1

You will need to lock the target range (setting the property Lockedfor the range to true). Then you will need to protect the sheet by calling the method of Protect()your worksheet. The following are examples of locking and unlocking cells A1:A4.

Locks:

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

Unlock:

Sheet1.Unprotect()

Change . After reading the question again, it seems like the OP is using the Worksheet_Change event. To which I have one word:

STOP

ActiveX , , .

Worksheet_Change . , , .

Answer 2

:

      Application.Calculation = xlCalculationManual

, Recalc. ?

recalc , :

• " IShouldNotRun Boolean"
• IShouldNotRun , .
• , .
• , , True.
• Reset Calculate, CalculateFull CalculateFullRebuild.

Answer 3

eell , , , Tools/Options "" "", .