VBA error shuts down ALL scripts

[fenfool]

I've been running across this problem lately, I have a spreadsheet that has a couple of scripts...one simply records the date when a users selects their name from a drop-down, the other composes an email. I'll be fine-tuning the email portion, and maybe I'll target the wrong cell when trying to pull a date. So I try to compose the email through my spreadsheet, get an error, fix it, then try to send it again, and absolutely nothing happens, no email, no error, nothing. Then I'll try to enter a name, the date is not recorded. I did nothing that would affect this part, but suddenly all my VBA scripts are doing nothing. Sometimes, I'll shut down Excel, bring it back up, and it will be fine. This last time, none of my scripts in ANY spreadsheet were working. I had to restart my machine...that got them working again. But I have no idea why a silly little error is shutting everything down. Any idea what could be happening? I'm looking at my code, and there is no reason why anything should not be working, so it's very frustrating.

 

[starl]

Do you have an Application.EnableEvents = False?

If you stop your code after that line, then events don't get turned back on. You have to restart Excel. If you had to restart the machine, check the Task Manager for any Excel.exe processes still live.

You can also turn your events back on with Application.EnableEvents = True typed into the Immediate window.. but if you didn't realize you had events off, then you may have other settings getting turned off that you didn't know about. Best to restart Excel.

 

[fenfool]

That's got to be it, thanks so much! This was driving me absolutely nuts. I DID have an Excel crash yesterday, so I'll be there was a process that wasn't terminated. I feel so much better, thanks!!

 

[starl]

Honestly - happens to me all the time

If I have to stop code before it runs to the end, I try to remember to drag the highlight to the end so it can finish "properly."
Else, manually turn the events back on.
Finally - restart Excel. If you've had a crash, check the Task Manager for another instance. Check even if you haven't had a crash and Events aren't working. Stuff to add to your troubleshooting Excel list.

 

[Joe4]

You can also turn your events back on with Application.EnableEvents = True typed into the Immediate window.. but if you didn't realize you had events off, then you may have other settings getting turned off that you didn't know about. Best to restart Excel.

Yes, I remember the first time this happened to me, it drove me crazy too! I could not figure out what was going on!

I do it a little differently. Instead of using the Immediate Window, I just create a one-line script to turn it back on and run that, i.e.

Sub TurnItBackOn()
    [COLOR=#333333]Application.EnableEvents = True
End Sub[/COLOR]

Same concept, just a different way to get there.

 

[starl]

and stick it on the QAT. good idea

 

[fenfool]

I swore I replied to this, but Joe4, that was a great idea, thanks! I had need for something similar today and returned to this post.