How to Lock Zoom in Excel (Using VBA with Detailed Steps)

This article highlights how to lock zoom in excel. The zoom feature in Excel is a great way to quickly maximize or minimize the view as required. This allows us to get a close-up view by zooming in and see more of the sheet at a reduced size by zooming out.


Download Practice Workbook

You can download the following file to practice along with it.


Steps to Lock Zoom in Excel Using a VBA Code

Now assume you have a worksheet containing many objects and shapes. Then zooming out the sheet may confuse users by making the objects smaller. This may lead to more complications especially if the objects are linked to macros. You need to lock the zoom feature for the users in such scenarios.

Unfortunately, there is no inbuilt or direct way to do that in excel. You can use a VBA code to change the zoom to a fixed level as soon as a user tries to zoom in or out. Follow the steps below to build the VBA code.


πŸ“Œ Step 1: Open VBA Window

  • First, press ALT+F11Β to open the VBA window. Then select Insert >> Module.

Lock zoom in excel


πŸ“Œ Step 2: Create Sub Procedure

Sub LockZoomInExcel()

End Sub
We will write code inside this subject procedure.

πŸ“Œ Step 3: Add Do Loop to Lock Zoom

Sub LockZoomInExcel()
Do

Loop While True
End Sub
We will write a statement inside this Do…Loop to force VBA to reset the zoom to a defined value the moment anyone changes it.

πŸ“Œ Step 4: Add IF Statement to Reset Zoom

Sub LockZoomInExcel()
Do
If ActiveWindow.Zoom <> 100 Then
ActiveWindow.Zoom = 100
End If
Loop While True
End Sub
The If statement sets the zoom to 100%.

Read More: How to Zoom Out in Excel (7 Quick Methods)


πŸ“Œ Step 5: Include DoEvents Statement

(Here you get the complete code)

Sub LockZoomInExcel()
Do
If ActiveWindow.Zoom <> 100 Then
ActiveWindow.Zoom = 100
End If
DoEvents
Loop While True
End Sub
The DoEvents statement will allow you to stop the running macro if required. Do not delete this statement. Otherwise, excel will become irresponsive until you close it.

πŸ“Œ Step 6: Run the Code

  • Now keep the cursor on the code. Then press F5 to run it. After that, return to your worksheet.

how to lock zoom in excel

Read More: Excel VBA: Zoom to Fit Screen (4 Suitable Examples)


πŸ“Œ Step 7: Observe Results

  • Now you won’t be able to zoom the page anymore. VBA will reset the zoom instantly. But, you need to rerun the code every time you reopen the document.

Read More: How to Zoom in When Zoom Slider Is Not Working in Excel


Things to Remember

  • Don’t forget to save the file as a macro-enabled workbook.
  • You need to rerun the code every time you reopen the document.

Conclusion

Now you know how to lock zoom in excel. Did this solve your problem? Do you have any further queries or suggestions?? Please let us know using the comment section below. You can also visit our ExcelDemy blog to explore more about excel. Stay with us and keep learning.


Related Articles

Md. Shamim Reza

Md. Shamim Reza

Hello there! This is Md. Shamim Reza. Working as an Excel & VBA Content Developer at ExcelDemy. We try to find simple & easy solutions to the problems that Excel users face every day. Our goal is to gather knowledge, find innovative solutions through them and make those solutions available for everybody. Stay with us & keep learning.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo