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

Get FREE Advanced Excel Exercises with Solutions!

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.


How to Lock Zoom in Excel: Steps 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%.

📌 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


📌 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.


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.

Download Practice Workbook

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


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.


Related Articles


<<Go Back to How to Zoom in Excel | Excel Worksheets | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Md. Shamim Reza
Md. Shamim Reza

Md. Shamim Reza, a marine engineer with expertise in Excel and a fervent interest in VBA programming, sees programming as a time-saving tool for data manipulation, file handling, and internet interaction. His diverse skill set encompasses Rhino3D, Maxsurf C++, AutoCAD, Deep Neural Networks, and Machine Learning. He holds a B.Sc in Naval Architecture & Marine Engineering from BUET and has transitioned into a content developer role, generating technical content focused on Excel and VBA. Beyond his professional pursuits,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo