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
1 Answer
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) –
5
