I want to elaborate a bit the comment of mine which was correct suggestion.
First, let me repeat that- you need to switch on events in this way
Application.EnableEvents = true
which you can be run once in Immediate Window in VBA/IDE Editor. Now we know that was it!
Second, if you decided to switch on events using any other subroutine (or event, which is however strange) please keep in mind that there could be some other subroutines, functions or add-ins which require events to be switched off. As long as you are not sure why events are not working you should keep them not working right after your macro doesn't need them any more. Therefore, my suggestion is to switch events off each time you will close your file. Therefore you could add this event to ThisWorkbook module
:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.EnableEvents = False
End Sub
Extra tip. The best option would be to read events status at the beginning, keep this information until you close your file. You could do it in the following steps:
A) declare public variables in your file
Public boEventsStatus as Boolean
B) read status when opening file (you need to figure it out where put this line of code)
boEventsStatus = Application.EnableEvents
C) switch on events as described at the beginning
D) use this BeforeClose
event:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.EnableEvents = boEventsStatus
End Sub
与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…