Behaviour of Worksheet_Calculate event in Excel – stored events? -

and thank reading post. have encountered strange (in opinion) behaviour of worksheet_calculate event in excel (windows 7 ultimate, excel 2010), let me describe problem i’m having giving set up.

take workbook 2 sheets. sheet 2 cell a1 has formula =sheet1!a1, enablecalculation property of sheet2 set true , in sheet2 object there sub

private sub worksheet_calculate()     = msgbox("value " & me.range("a1").value, vbokonly) end sub 

in module1 there sub

public sub mysub()     application.enableevents = false     thisworkbook.worksheets(2).enablecalculation = true     thisworkbook.worksheets(2).enablecalculation = false     application.enableevents = true end sub 

now, enter 1 in sheet1 cell a1 – message box “value 1” displayed. next, set enablecalculation property of sheet2 false , enter 2 in sheet1 cell a1 – nothing happens. now, run mysub in module 1 – cell a1 on sheet2 displays 2 , enablecalculation property of sheet2 set false. far, good. enter 3 in sheet1 cell a1 – message box “value 2” displayed!

it seem during execution of mysub calculation event fired (even though application.enableevents set false) , value of 2 stored (where?) , event released when 3 entered cell a1 on sheet1 (even though enablecalculation set false sheet2). clue what’s going on here , how “fix” strange behaviour?

many thanks.

quote http://www.decisionmodels.com

'setting enablecalculation false , true flag formulae on worksheet uncalculated. if calculation mode automatic recalculation triggered. calculation methods, including sheet.calculate excluding range.calculate, calculate formulae on sheet. can use method way of simulating calculatefull @ worksheet rather workbook level. note sheet.calculate not reset uncalculated formulae flags, 2 sheet.calculates in succession after toggling enablecalculation property both full sheet calculate.'

this seems fix it

public sub mysub()     application.enableevents = false     application.calculation = xlcalculationmanual     thisworkbook.worksheets(2).enablecalculation = true     thisworkbook.worksheets(2).enablecalculation = false     application.calculation = xlcalculationautomatic     application.enableevents = true end sub