1

I have a macro in place that toggles the following:

 Application.ScreenUpdating = False
 Application.EnableEvents = False
 Application.DisplayAlerts = False
 Application.Calculation = xlCalculationManual

I turn them all on/off at the same time, but I noticed that sometimes EnableEvents does not turn back on, and screenUpdating does not turn off.

Is there a specific order I should follow because they somehow affect each other when toggling them?

Vityata
  • 42,633
  • 8
  • 55
  • 100
Dumitru Daniel
  • 571
  • 4
  • 19
  • Why would you want to re-enable them unless you are done? – ThunderFrame Jul 20 '18 at 08:23
  • I use the macro setProgramAlerts( onOrOff ) at the begining and end of other macros, turn off at the begining and back on at the end. And after that I have some other actions linked to Sheet select events, that are disabled because Application.EnableEvents remains False even after setting it to True. – Dumitru Daniel Jul 20 '18 at 08:27

2 Answers2

1

Is there a specific order I should follow because they somehow affect each other when toggling them?

No.

Concerning non-toggling off or on, probably somewhere in the code you are having On Error GoTo Somewhere and you are skipping the lines with the toggling? (Just guessing).

Vityata
  • 42,633
  • 8
  • 55
  • 100
  • There is a know issue that "ScreenUpdating = False fails in Excel 2013 and 2016", you can look for this title on stackoverflow, and on Microsoft forums :(. The 3 lines of code i ran 1 after the other, no other subroutines between them, so i don't think that is the problem. I will be back with a reply if I find the problem. – Dumitru Daniel Jul 20 '18 at 08:21
  • But `ScreenUpdating` automatically reverts to `True` as soon as the code is done, so it doesn't really matter – ThunderFrame Jul 20 '18 at 08:24
  • @DumitruDaniel - Found this - https://stackoverflow.com/questions/35301178/screenupdating-false-fails-in-excel-2013-and-2016, but I actually think the same as this answer - https://stackoverflow.com/a/37927302/5448626 – Vityata Jul 20 '18 at 08:28
1

Rather than setting all of those back to True, which can cause problems for people that have explicitly set calculation to manual, you should record the current settings, before changing anything, and then restore the settings to their original values when you are done. See CodeReview for an example of a class that manages this for you.

The only interaction might come about with Calculation and EnableEvents, but only if there are events configured for worksheet/workbook calculate.

ThunderFrame
  • 9,352
  • 2
  • 29
  • 60
  • Actually it was me who has updated the question to what it looks like. Initially the OP has only posted the settings. @Dumitru Daniel and ThunderFrame - Feel free to revert the question update :) – Vityata Jul 20 '18 at 08:30