VBA: Zoom…Zoom

November 8th, 2004 | Categories: VBA | Tags:

You can use VBA to change the Zoom of a worksheet. Here’s code to change the Zoom of the ActiveWindow to 50%:

ActiveWindow.Zoom = 50

You can also loop through all the worksheets in your workbook to set a standard Zoom. The following Macro will set the Zoom for all worksheets to 50%:

Sub ZoomAll()
Dim ws As Worksheet

Application.ScreenUpdating = False

For Each ws In Worksheets
    ActiveWindow.Zoom = 50

Application.ScreenUpdating = True
End Sub

And finally a magically growing worksheet. The following macro will loop through the Zooms for Sheet1, going from 10% to 200%, incrementing by 10%, pausing a second between changes, and then it will restore Sheet1 back to it’s original state.

Sub ZoomZoom()

Dim x As Integer 'variable for loop
Dim OriginalZoom As Integer 'variable for original zoom

Sheet1.Activate 'let's work with sheet1

OriginalZoom = ActiveWindow.Zoom 'get current zoom

'loop through zoom 10 to 200 by 10
    For x = 1 To 20
        ActiveWindow.Zoom = x * 10
        Application.Wait Now + TimeValue("00:00:01")
    Next x
'restore original zoom
ActiveWindow.Zoom = OriginalZoom

End Sub
  1. Ali
    May 10th, 2009 at 13:29
    Reply | Quote | #1

    in my vba code, zooming to 50% works fine, but not back to 100% any ideas why?
    For Each ws In Worksheets
    ActiveWindow.Zoom = 100

  2. Sam
    May 16th, 2009 at 10:35
    Reply | Quote | #2

    If you are using Excel 97, you may have to write the zoom size that will be the equivalent ratio of your active zoom. In this case, since you reduced it to 50%, you would now have to increase it by 200% to bring it to normal i.e. 100%. I’m not sure if I’m right, and I didn’t have an IDE environment to check this, but let me know.

    For Each ws In Worksheets
    ActiveWindow.Zoom = (100/ActiveWindow.Zoom)*100

  3. Cheryl
    September 24th, 2009 at 11:46
    Reply | Quote | #3

    I had tried. It works by using the following code

    ActiveWindow.Zoom = 100%