Zoom Worksheets To Fit Used Range

    Could someone please assist with helping to apply a zoom to Fit Selection on all worksheets upon file opening? I've read many posts on this and understand how to apply this via worksheet activation, but haven't discovered how to make this to happen to all worksheets when the file is opened.


    Zooming the selection needs to happen in the workbook module due to additional sheets being added at random and because it will facilitate the flow of the meeting so that we don't have to wait for the user to select and zoom.



    Thanks for helping.

    Re: Zoom All Worksheets To Fit Selection On File Open


    Why dont you put it in the workbook_Sheetactivate event:


    Code
    Option Explicit
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    Dim rCell As Range
    With Sh
        Set rCell = .Cells.Find("*", Range("A1"), xlFormulas, , xlByColumns, xlPrevious)
        If rCell Is Nothing Then Exit Sub
        .Range(.Cells(1, 1), .Cells(1, rCell.Column)).EntireColumn.Select
    End With
    ActiveWindow.Zoom = True
End Sub

    Re: Zoom All Worksheets To Fit Selection On File Open


    Thanks Reafidy.


    My newbie colors are showing again. I didn't think to place it there.


    The code works great as long as the sheet has some data, but gives a "run-time error 91, object variable or With block variable not set" if the sheet is empty.


    My knowledge has not yet developed enough to fix this as I'm not sure what variable to set.
    I guess the approach is an IF/THEN to test for empty cells in the range and skip the zoom if so?


    I will appreciate any further help you can provide.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login