Setting the Zoom Level by User

James's department has a workbook that is used by multiple people, but not at the same time. Each person has a different preference for what zoom setting should be used with the workbook. James wonders if there is a way to automatically set the zoom based on which person is opening the workbook.

There is no way to do this without enlisting the help of a macro. You can set up the Workbook_Open event handler to execute anything you want when the workbook is opened. For instance, you could use a very simple macro such as this:

Private Sub Workbook_Open()
    ActiveWindow.Zoom = InputBox("Enter desired workbook Zoom level", "Zoom Factor")
End Sub

This prompts the user, when the workbook is opened, for the desired zoom level. This macro needs to be part of the ThisWorkbook object, so make sure you add it into the proper place in the VBA Editor. The easiest way to do this is to follow these steps:

1. Press Alt+F11 to display the Visual Basic Editor.
2. Using the Project window, double-click the ThisWorkbook object. (Make sure you double-click on the ThisWorkbook object associated with the workbook where you want to add the macro.) You'll see a code window for the ThisWorkbook object appear.
3. Put the above code in the code window.

The major drawback to the macro is that the user is prompted for a zoom level every time the workbook is opened. If James wants a more automatic way, then the macro needs to determine the name of the person opening the workbook. This is done, most reliably, by checking the Windows login name for the person's system:

Private Sub Workbook_Open()
    Dim userName As String

    userName = Environ("Username")
    Select Case LCase(userName)
        Case "tom"
            ActiveWindow.Zoom = 90
        Case "dick"
            ActiveWindow.Zoom = 110
        Case "harry"
            ActiveWindow.Zoom = 100
        Case Else
            ActiveWindow.Zoom = InputBox("Enter desired workbook Zoom level", "Zoom Factor")
    End Select
End Sub

The toughest trick here is to discover what each person's login name is in Windows. It is critical that each Case statement utilize the lowercase version of the person's login name, exactly. You can find out their login name by running this short macro on each system:

Sub GetUserName()
    MsgBox Environ("Username")
End If

Jot down the names, and then put them into the Workbook_Open event handler noted above.

Realize that the macros discussed so far affect the zoom level for only whatever worksheet is active when the workbook is opened. If you want to affect the zoom level for all worksheets in the workbook, then you can modify the macro slightly:

Private Sub Workbook_Open()
    Dim userName As String
    Dim iLevel As Integer
    Dim s As Worksheet

    userName = Environ("Username")
    Select Case LCase(userName)
        Case "tom"
            iLevel = 90
        Case "dick"
            iLevel = 110
        Case "harry"
            iLevel = 100
        Case Else
            iLevel = InputBox("Enter desired workbook Zoom level", "Zoom Factor")
    End Select

    Application.ScreenUpdating = False

    For Each s In Worksheets
        s.Activate
        ActiveWindow.Zoom = iLevel
    Next s

    Application.ScreenUpdating = True
End Sub

This approach works well if you have a relatively small number of users. If you have more users, or the number of users changes often, then you may want to create a version that reads a list of user names from a worksheet. Just set up a worksheet, put the user login names into a column and, to the right of each name, the desired zoom level. Select the names and levels and assign the name UserLevels to the range. You can then change the macro to this:

Private Sub Workbook_Open()
    Dim userName As String
    Dim rUsers As Range
    Dim iLevel As Integer
    Dim s As Worksheet
    Dim c As Range

    Set rUsers = ThisWorkbook.Names("UserLevels").RefersToRange
    userName = Environ("Username")

    iLevel = 0
    For Each c In rUsers.Columns(1).Cells
        If c.Value = userName Then
            iLevel = c.Offset(0, 1).Value
            Exit For
        End If
    Next c

    If iLevel = 0 Then
        iLevel = InputBox("Enter desired workbook Zoom level", "Zoom Factor")
    End If

    Application.ScreenUpdating = False

    For Each s In Worksheets
        s.Activate
        ActiveWindow.Zoom = iLevel
    Next s

    Application.ScreenUpdating = True
End Sub

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (3077) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, 2021, 2024, and Excel in Microsoft 365.