How to apply workbook_open to multiple sheets

Question

I have a excel workbook that a number of users interact with daily and on multiple montiors with different resolutions, screen zooms etc.. I need all worksheets to adjust to the ranges on each sheet I want the user to see each time.

Below works for 1 worksheet, but how would I get it to apply to all worksheets (Sheet1,Sheet2,etc.)

Private Sub Workbook_Open()
With Sheets("Sheet1")
    Columns("A:P").Select
    ActiveWindow.Zoom = True
    Range("A1").Select
End With
End Sub

loop through all worksheets. stackoverflow.com/questions/25953916/… — cyboashu
You can loop through the existing worksheets, visible and hidden, with (at least) four different methods: Dim WS As Worksheet For Each WS In ThisWorkbook.Worksheets ' do something with worksheet WS Next WS ' OR Dim N As Long For N = 1 To ThisWorkbook.Worksheets.Count ' do soemthing with Worksheets(N) Debug.Print ThisWorkbook.Worksheets(N).Name Next N ' OR Dim WS As Worksheet Set WS = Worksheets(1) Do Until WS Is Nothing ' do something with WS Debug.Print WS.Name Set WS = WS.Next Loop — skkakkar
Thanks cyboashu, I am not a vba guru, how would I go about applying the loop to each worksheet when each has a different range. — Stackexplorer

YowE3K YowE3K · Accepted Answer · 2016-07-22T18:52:54

You can use the Worksheet_Activate event, and place code such as

Private Sub Worksheet_Activate()
    Columns("A:P").Select
    ActiveWindow.Zoom = True
    Range("A1").Select
End Sub

on each sheet, editing the range as required.

That code will execute every time the sheet is activated, which may or may not be what you would like, so you may need to use something a bit more complicated and use:

Private AlreadyRun As Boolean
Private Sub Worksheet_Activate()
    If Not AlreadyRun Then
        Columns("A:P").Select
        ActiveWindow.Zoom = True
        Range("A1").Select
        AlreadyRun = True
    End If
End Sub

which will only do something the first time the sheet is activated (as the AlreadyRun variable will originally be False, but will be changed to True once it is run once), or

Private AlreadyRun As Boolean
Private Sub Worksheet_Activate()
    Dim CurRng as Range
    Set CurRng = Selection
    Columns("A:P").Select
    ActiveWindow.Zoom = True
    CurRng.Select
    If Not AlreadyRun Then
        Range("A1").Select
        AlreadyRun = True
    End If
End Sub

which will resize the sheet every time it is activated, but only move the selected cell to A1 the first time.

To avoid the issue caused by the sheet which is current when the Workbook is saved not going through the Worksheet_Activate event when the workbook is reopened, you can include a Workbook_Open event that says

Private Sub Workbook_Open()
    Application.Screenupdating = False
    Dim ws As Worksheet
    Set ws = Activesheet

    'For the next two lines, just pick any two of your worksheets
    'All it is trying to do is to ensure whichever sheet was active at open
    'is deactivated before being activated again in the "ws.Activate" command
    Worksheets("Sheet1").Activate
    Worksheets("Sheet2").Activate

    ws.Activate
    Application.Screenupdating = True
End Sub

(Disabling Screenupdating while the event is run will avoid the users seeing any "flickering" of worksheets.)

How to apply workbook_open to multiple sheets

1 Answers