Application.EnableEvents not working on a second workbook

[nyconfidential]

Hi All - I wrote a sub that copies certain formulas from one workbook to another blank workbook. I've set Application.EnableEvents to false - this seems to work fine if I'm copying code to another sheet in the same workbook, but if I'm copying to another workbook, events are triggered and I receive an error. I assumed that when something is done on the Application level it would turn off events for every workbook that is open, am I incorrect? Is there something else I need to do turn off events for the second workbook? Thanks in advance.

 

[Roderick_E]

Your reasoning seems sound. I suspect you are opening another instance of the Excel application? I don't know without seeing the code

 

[nyconfidential]

Your reasoning seems sound. I suspect you are opening another instance of the Excel application? I don't know without seeing the code

Here ya go Roderick - the Worksheet_change event in the workbook that is getting updated(ActiveWorkbook) is triggered even though Application.EnableEvents is set to false. (And yes, two instances of Excel are open) Thanks!

Public Sub ReplaceformulasUsedRange()


Dim rCell As Range
Dim rRng As Object
Dim WrkSht As Worksheet


Application.EnableEvents = False
Application.DisplayAlerts = False
Application.ScreenUpdating = False


For Each WrkSht In ThisWorkbook.Worksheets
    If SheetExists(WrkSht.Name) Then
    ActiveWorkbook.Sheets(WrkSht.Name).UsedRange.Formula =ThisWorkbook.Sheets(WrkSht.Name).UsedRange.Formula
End If
'Debug.Print WrkSht.Name
Next WrkSht


Application.DisplayAlerts = True
Application.ScreenUpdating = True
Application.EnableEvents = True
MsgBox "Updated"
End Sub

 

[Roderick_E]

Thanks. You said this is triggering or triggered by Worksheet_Change? If so, EnableEvents keeps getting turned back to True in this code. How/when is Worksheet_Change involved?

 

[nyconfidential]

When this line of code is executed:

[COLOR=#333333]    ActiveWorkbook.Sheets(WrkSht.Name).UsedRange.Formula =ThisWorkbook.Sheets(WrkSht.Name).UsedRange.Formula

[/COLOR]The Worksheet_change event on the workbook that is getting updated(copied to) is triggered. I'm setting enableevents to false in the OTHER workbook, the one I am copying data from.

 

[Roderick_E]

I'm thinking you should turn off the events in the Worksheet_Change event as well. Humor me