How to run automatically the zoom lock code after reopen the Excel file?

Question

Using the following code inside VBA Excel it is possible to lock the zoom of worksheet. But the bad news is that this code is not automatically being run after reopening the Excel file.

Is there any way to run the code without referring to it every time we open the excel file?

Thank you

Sub LockZoomInExcel()
 Do
  If ActiveWindow.Zoom <> 100 Then
   ActiveWindow.Zoom = 100
  End If
  DoEvents
 Loop While True
End Sub

Answer 1

(1) Your routine implements a "busy wait" loop. That means it runs endless, checking if the zoom was changed. If yes, it sets it back to 100%. Then an DoEvents is triggered, giving Excel the possibility to handle user input.

This loop runs as fast as the computer can execute it (on my computer, > 10.000 times a second) and keeps the CPU busy (at least one core of it), which will consume energy and will slow down the computer. If you ask me, you should avoid such "busy wait" loops.

(2) When you start your code in the Workbook.Open-event, the loop is started immediately. The DoEvents are not sufficient to let Excel finish the workbook, Excel will wait until the routine is done - which is never because it is an endless loop.

(3) If you can live with the high CPU usage, use the OnTime-method to call the routine after a certain time, e.g. 10 seconds. This should give Excel enough time to finish the process of opening the file. Put the following line into the Workbook-module:

Private Sub Workbook_Open()
    Application.OnTime Now + TimeSerial(0, 0, 10), "LockZoomInExcel"
End Sub

(4) The Excel application object exposes quite a lot of events, but no OnZoom, so no way to catch the zoom event.

(5) https://stackoverflow.com/a/46423788/7599798 suggests to place a InkPicture ActiveX control and use the Resize-event of that. While that sounded promising, it didn't work for me (no event was fired).

(6) Best alternative I found was to use the OnTime, calling it every second. Put this in a regular module:

Option Explicit

Dim nextZoomCheck As Date
Sub checkZoom()
    If ActiveWindow.Zoom <> 100 Then ActiveWindow.Zoom = 100
    nextZoomCheck = Now + TimeSerial(0, 0, 1)
    Application.OnTime nextZoomCheck, "checkZoom"
End Sub

Sub stopCheckZoom()
    If nextZoomCheck = 0 Then Exit Sub
    Application.OnTime nextZoomCheck, "checkZoom", Schedule:=False
End Sub

Now you can call the checkZoom-routine from the Workbook_Open-event routine.

You can stop the zoom checking by calling stopCheckZoom, maybe when a button is clicked or at a Worksheet, or stop it at Workbook_SheetDeactivate and start it again at Workbook_SheetActivate