Application.EnableEvents= True . Does not work with Worksheet_Activate ! Vba

user_15586050 0
Viewed 692

I have workbook (1) with many codes depend on events to be enabled to run, by chance I opened workbook (2) and disabled events in it but I surprised that events also disabled in Workbook (1) . So , I tried to to put this code in Workbook (1) Application.enableEvents= True in Workbook_Activate but does not work even Window_Activate and Worksheet_Activate . Any help please

vba excel
1 Answers

Let's say you live in an apartment which has 3 rooms. When you do not need lights in one room, you do not switch off power of the entire apartment and then wonder why is there no light in the 2nd or 3rd room? For this purpose you have switches in those rooms so that you can switch off light only for the relevant room.

Once you undertsand the above logic, then try and understand this.

Let's say your Excel Application (Apartment) has 3 workbooks (Rooms). If you switch off the event for the application and not for the (procedures/functions) in that workbook, then how will it function for other workbooks (Room)?

It is advisable to switch off events inside the function/procedure and turn them back on in the end (just like using ON/Off switches). Here is an example

Private Sub Sample()
    Application.EnableEvents = False
    
    '
    '~~> Your code here
    '
    
    Application.EnableEvents = True
End Sub

Also whenever you are switching off events, use error handling to turn it back on, else if you get an error, the code will not run the next time. Here is an example

Private Sub Sample()
    On Error GoTo Whoa
    
    Application.EnableEvents = False
    
    '
    '~~> Your code here
    '
    
Letscontinue:
    Application.EnableEvents = True
    Exit Sub
Whoa:
    MsgBox Err.Description
    Resume Letscontinue
End Sub

Note: Links worth reading Application.EnableEvents property (Excel) and Application object (Excel) –

user_11405790
Related