0

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
Stackexplorer
  • 13
  • 6
  • loop through all worksheets. http://stackoverflow.com/questions/25953916/excel-vba-looping-through-multiple-worksheets – cyboashu Jul 22 '16 at 18:00
  • 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 Jul 22 '16 at 18:03
  • 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 Jul 22 '16 at 18:19

1 Answers1

1

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.)

YowE3K
  • 23,852
  • 7
  • 26
  • 40
  • Thanks YowE3k, the 1st code is pretty close. However I need all 3 worksheets to adjust and zoom on open so the invoice forms show the same range no matter what resolution or zoom the user has set on their monitor. Would I just add a 'workbook_open()' to the top of each sheet activate? – Stackexplorer Jul 22 '16 at 19:32
  • You probably don't care what the zoom level is for the sheet when the user isn't looking at it. You probably only care what the zoom level is when the user wants to actually look at it. So you only need to ensure that it is at the right zoom level if the sheet is activated, not before. The Workbook_Open event just fires whenever the workbook is opened, but the Worksheet_Activate event will fire each time the user switches to that worksheet to look at it. – YowE3K Jul 22 '16 at 19:53
  • I used the 3rd code as well and it works. My only issue now is when then user selects a sheet for the 2nd time it resizes but highlights the whole sheet. Is it possible to have it just select the cell the user had it on last? – Stackexplorer Jul 22 '16 at 19:58
  • I had forgotten about that. I will edit in a slight change to do what you asked. – YowE3K Jul 22 '16 at 19:59
  • @Stackexplorer - I just thought of another issue - the sheet that is active when the workbook is saved is not "Activated" when the workbook is reopened. If your application can have a "splashscreen" worksheet (saying something like "This workbook has been created by StackExplorer - he (or she) deserves more money!"?) then you could have a Workbook_Open event that activates that splashscreen sheet - thus ensuring that all your normal sheets will go through the Worksheet_Activate event. – YowE3K Jul 22 '16 at 20:18
  • I have edited the answer to include a Workbook_Open event that gets around the need for a splash screen. (But feel free to have a splash screen if you like - I have always found that you can never remind people enough that you deserve more money!) – YowE3K Jul 22 '16 at 20:35
  • Thanks TowE3K! However I am getting the following run time error Run-Time Error '-2147352565 (8002000b)': Can't move focus to the control because it is invisible, not enabled, or of a type that does not accept the focus – Stackexplorer Jul 25 '16 at 13:16
  • I assume that error must be happening in the Workbook.Open event, and I assume it must be because one of the sheets that you are activating in that event had its Visible property set to False when you last saved it. Do you have other code to ensure that only one sheet is Visible at a time.? If so, it is a fairly simple change to the Open event to (a) store the current setting of the sheet's Visible property in a variable, (b) set the sheet's Visible property to True, (c) Activate the sheet, (d) set the sheet's Visible property to the variable you saved it in – YowE3K Jul 25 '16 at 16:23