PDA

View Full Version : Set the Workbook_SheetChange to False ??



rrenis
03-02-2009, 06:58 AM
Hi all - I have a sheets with a lot of match formula's used to perform a lookup on another sheet. I need to be able to add a line in to the sheet fairly regularly but this causes excel to recalculate which can take about 20 seconds, to avoid this I thought I could simply perform this via a macro turning the calculation off, inserting the line and then turning calculation back on again. The trouble is excel recognises the inserted line as a change an performs a full sheet re-calculation - therefore I'm no better off. :doh:

Does anyone know of a way in which you can force excel not to recognise the inserted line as a sheet change - or turn the automatic calculation back on without re-calculating? :think:

Thanks for looking.

Cheers,
rrenis

Bob Phillips
03-02-2009, 07:34 AM
How about a simple macro to turn off events, add the line, reset events. Tie this macro to a button.

rrenis
03-02-2009, 07:45 AM
Hi xld :hi:

I already have Application.EnableEvents = False before the code to insert the line and Application.EnableEvents = True after the code - is this what you mean as I still have the delay? :(

Cheers,
rrenis

P.S. my code is in an ordinary module - the subject line of the thread probably confuses this as I thought maybe there could be something added to ThisWorkbook module to reset the change event to false (?)

mikerickson
03-02-2009, 07:57 AM
Turning the Calculation back on triggers a re-calculation.
You can't escape a re-calc. when inserting the row.

Bob Phillips
03-02-2009, 07:57 AM
Yes, but not in a change event, a separate macro, driven by a button or a menu.

rrenis
03-02-2009, 08:02 AM
Hi xld - I'm not sure I follow you this is the code I have - do I need to split it up? (myForm is not important and contains no code)

Dim xlCalc As XlCalculation

xlCalc = Application.Calculation
Application.Calculation = xlCalculationManual
Application.EnableEvents = False

On Error GoTo CalcBack

my_Form.Show
DoEvents
Application.StatusBar = "Inserting row..."
Application.ScreenUpdating = False
Rows("4:4").Select
Selection.EntireRow.Hidden = False
Selection.Copy
Rows("5:5").Select
Selection.Insert Shift:=xlDown
ActiveSheet.Paste
Rows("4:4").Select
Selection.EntireRow.Hidden = True
Application.CutCopyMode = False
Range("A5:B5").Select
Selection.ClearContents
Range("A5").Select

Application.Calculation = xlCalc
Application.StatusBar = ""
Unload myForm
Application.StatusBar = False
Application.EnableEvents = True


Exit Sub

CalcBack:

Application.Calculation = xlCalc
Unload myForm
Application.EnableEvents = True

cheers,
rrenis

Bob Phillips
03-02-2009, 08:16 AM
That code doesn't look to me that it forces a recalc.

Where does the code reside?

rrenis
03-02-2009, 01:05 PM
hi xld - it just sits in a standard module.

cheers,
rrenis

rrenis
03-03-2009, 07:31 AM
hi all - does anyone have any idea why a recalc is being forced?? :help

thanks for looking.

cheers,
rrenis

Bob Phillips
03-03-2009, 07:37 AM
can you post the workbook?

mikerickson
03-03-2009, 07:42 AM
If the formula =COUNT(A1:A10) in on the spread sheet, the
Range("3:3").insert will force a re-calculation.

(This technique can be used to detect a Row_Insert "event".)

rrenis
03-03-2009, 07:54 AM
hi xld and mikerickson - the only formula on the sheet where lines are to be inserted is posted below, although it appears in every cell from L5: DO500 + and it will continue to expand as new rows are added hence the delay when adding a row...

=INDEX(My_Data!$1:$1984,MATCH(My_DataBase!$A12&"B",My_Data!$A:$A,0),MATCH(My_DataBase!L$2,My_Data!$1:$1,0))

I'm guessing that I'm stuck with the recalculation. If that's the case I'll live with it, just wondered if there was a way round it. If you still need me to post the workbook let me know as there's some sensitive data I'll need to strip out. Thanks.

Cheers,
rrenis

Bob Phillips
03-03-2009, 08:59 AM
Yes, I am sure it can be avoided.