Change Zoom levels on Excel Sheets using VBA

[Manuprasad]

I have a VBA that will set the zoom level based on the screen resolution.But its working only for ActiveWindow when you open workbook.
How can I add this across all worksheets in Excel?

<code>Declare Function GetSystemMetrics32 Lib "user32" _
    Alias "GetSystemMetrics" (ByVal nIndex As Long) As Long

Public Sub ScreenRes()
    Dim lResWidth As Long
    Dim lResHeight As Long
    Dim sRes As String

    lResWidth = GetSystemMetrics32(0)
    lResHeight = GetSystemMetrics32(1)
    sRes = lResWidth & "x" & lResHeight
    Select Case sRes
        Case Is = "800x600"
            ActiveWindow.Zoom = 75
        Case Is = "1024x768"
            ActiveWindow.Zoom = 125
        Case Else
            ActiveWindow.Zoom = 100
    End Select
End Sub</code>

I will call this module on the Workbook

<code>Private Sub Workbook_Open()
ScreenRes
End Sub</code>

 

[RedBeard]

Just loop through the worksheets?

Something like:

Dim ws As Worksheet

    For Each ws In Worksheets
        ws.Select
        ActiveWindow.Zoom = 85 //change as per your requirements
    Next ws

 

[My Aswer Is This]

Maybe this:

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
ScreenRes

 

[Manuprasad]

Thanks for your reply!! Not just loop through Sheets. I also want to make sure zoom level across all the worksheets are set based on screen resolution. As of now, with my above code Zoom level is only set for first page when the workbook is open. How do I loop through the sheets keeping screen resolution on mind?

 

[Manuprasad]

Wow!! that worked. BRAVO....Just that code will activate only when I click on sheet....but my intention was to activate zoom level when the workbook open. However, the code serves the purpose at the moment. If you have suggestions to activate zoom level when workbook opens, please let me know.