3

I am trying to build a macro in Excel which loops through all worksheets, and based on the largest sheet, sets the zoom level to the same level for all worksheets so they all fit on one page but have the same scale (which is needed in printing).

I am however having trouble with determining the zoom level which makes sure the biggest page fits to a 1 page width.

When setting a worksheets width to fit on one page by using .PageSetup.FitToPagesWide = 1 the .PageSetup.Zoom property automatically gets set to FALSE.

Setting the FitToPage properties back to false, the zoom level is unchanged from what it was before fitting to one page.

When manually setting the sheet so it fits to one page wide, Excel does show which zoom level corresponds to this, but it seems there is no way to read this in VBA. Could someone help me with this issue?

R3uK
  • 14,417
  • 7
  • 43
  • 77
user3029330
  • 43
  • 3
  • I might be misunderstanding your question but why not create one subroutine to size the columns and another that sets the print area for each page? – Part_Time_Nerd Mar 20 '17 at 14:55

1 Answers1

1

This post is getting rather old, but as I've been sitting with a similar problem, this question gave me a possible answer.

Using a slightly redone code posted by Tom Urtis (https://www.mrexcel.com/forum/excel-questions/67080-page-setup-zoom-property.html) the following code extract the zoom iteratively, and then sets the zoom of all pages.

Option Explicit
#If Win64 Then
    Declare PtrSafe Function LockWindowUpdate Lib "user32" (ByVal hwndLock As Long) As Long
    Declare PtrSafe Function FindWindowA Lib "user32" (ByVal lpClassName As String, ByVal lpWindowName As String) As Long
#Else
    Declare Function LockWindowUpdate Lib "user32" (ByVal hwndLock As Long) As Long
    Declare Function FindWindowA Lib "user32" (ByVal lpClassName As String, ByVal lpWindowName As String) As Long
#End If
Sub SetSameZoomOnAllWorksheets()
    On Error GoTo failed
    Dim initial_sheet As Worksheet, Sheet As Worksheet, minzoom As Double
    With Application
        'stuff to speed up the process and avoid any visible changes by the user
        .ScreenUpdating = False
        .DisplayStatusBar = False
        .EnableEvents = False
        '.Visible = false 'Uncomment on a really slow document to make people freak out. Make sure to have the on error so that you'll set it to visble again
        ActiveSheet.DisplayPageBreaks = False
    End With
    Set initial_sheet = ThisWorkbook.Worksheets(ActiveSheet.name)
    minzoom = 400 ' max value set by zoom
    'iterate over each sheet
    For Each Sheet In ThisWorkbook.Worksheets
        minzoom = Application.Min(minzoom, GetOnePageZoom(Sheet))
    Next Sheet
    'iterate over each sheet once more and set the zoom to the lowest zoom
    For Each Sheet In ThisWorkbook.Worksheets
        With Sheet
            If .Visible = xlSheetVisible Then
                .Select
                .PageSetup.Zoom = minzoom
            End If
        End With
    Next Sheet
    initial_sheet.Select
failed:
    With Application
        'Change it back so that the user may see any changes, perform calculations and so on
        .ScreenUpdating = True
        .DisplayStatusBar = True
        .EnableEvents = True
        ActiveSheet.DisplayPageBreaks = True
        '.Visible = True 'This one is very important to unmark if you have marked .visible = false at the top
    End With
End Sub
Function GetOnePageZoom(ByRef Sheet As Worksheet) As Double
    With Sheet
        If .Visible = xlSheetVisible Then
            .Select
            'LockWindowUpdate locks the specified window for drawing - https://learn.microsoft.com/en-us/windows/desktop/api/winuser/nf-winuser-lockwindowupdate
            'XLMAIN is the current active window in excel
            LockWindowUpdate FindWindowA("XLMAIN", Application.Caption)
            .PageSetup.FitToPagesWide = 1
            .PageSetup.Zoom = False
            'pre-send keys for next command to specify: On pagesetup Dialog Press P to open the 'Print', then press alt + A to set page setup to adjust (Automatically moves into the zoom field but keeps the value), press enter
            'This changes the pagesetup from 'fitstopageswide = 1' to 'automatic' but keeps the zoom at whatever level it was set to by the fitstopageswide
            SendKeys "P%A~"
            Application.Dialogs(xlDialogPageSetup).Show
            LockWindowUpdate 0
            GetOnePageZoom = .PageSetup.Zoom
            Debug.Print .PageSetup.Zoom
        Else
            GetOnePageZoom = 400
        End If
    End With
End Function
Oliver
  • 11
  • 1