CODEWITHSUNDEEP
vbaexcel
Manu
Manu

Reputation: 83

VBA to set Zoom level on Sheets

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?

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

I will call this module on the Workbook

Private Sub Workbook_Open()
ScreenRes
End Sub

Upvotes: 6

Views: 14311

Answers (2)

user3598756
user3598756

Reputation: 29421

building on @Jeeped answer you could place in ThisWorkbook code pane the following code:

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

Option Explicit

Private Sub Workbook_Open()
    With Worksheets
        .Select
        ActiveWindow.zoom = ScreenResToZoom
    End With
End Sub

Public Function ScreenResToZoom() As Long
    Select Case GetSystemMetrics32(0) & "x" & GetSystemMetrics32(1)
        Case Is = "800x600"
            ScreenResToZoom = 75
        Case Is = "1024x768"
            ScreenResToZoom = 125
        Case Else
            ScreenResToZoom = 100
    End Select
End Function

Upvotes: 5

user4039065
user4039065

Reputation:

Select all of the Worksheet Objects using the Worksheets collection and the Application.ActiveWindow property will point to them all.

With Worksheets
    .Select
    ActiveWindow.Zoom = 75
End With

Upvotes: 10

Related Questions