When there's a VB error, why won't any macros run again unless I close and reopen the workbook?

[Ironman]

This is a long-standing issue I've had and have always just accepted it's "one of those things with Excel".

Whenever I run a new bit of code and it errors, the calculation method always changes to manual. I then change the calculation method back to automatic. I also go into the code > Run > Reset.

But doing both of these things makes no difference - none of my macros will run until I close and reopen the workbook, which is pretty annoying.

Can anyone tell me if there's something else I should be doing to get macros to run, or is it just "one of those things with Excel" that I have to close the workbook?

Many thanks!

 

[jasonb75]

If the problem is with an event procedure (worksheet change, worksheet calculate, etc) then you have probably also set enableevents to false. If that is the case then it will not revert back to true on its own when the code is interrupted, you need to re-enable it with Application.EnableEvents = True in the immediate window (Ctrl g in the code editor).

 

[Ironman]

Ahhh, OK, thanks a lot Jason, that's been 'bugging' me for years.

I suppose by definition with all macros disabled then manually typing that line in there is the only way to restart them (yes, it's the worksheet_change event) or is there a shortcut?

 

[jasonb75]

I suppose by definition with all macros disabled then manually typing that line in there is the only way to restart them

Not all are disabled, only events. You can still run manual procedures (To the best of my knowledge, there is no shortcut).Normally I would have this procedure saved in standard module for convenience to enable / disable events and calculation as needed. I find it useful to be able to switch them off easily if it is necessary to correct anything in the worksheet without the code firing.

Sub toggleevents()
With Application
    .EnableEvents = (.EnableEvents = False)
    .Calculation = IIf(.EnableEvents = False, xlManual, xlAutomatic)
End With
End Sub

 

[Ironman]

Thanks Jason - does your code actually 'toggle' events on and off (as per its name)? If that's what it does then that's really useful.

 

[jasonb75]

does your code actually 'toggle' events on and off (as per its name)?

It toggles both events and calculation based on the current state of events. If events are true then the code will turn both off, if events are false then it will turn both on.