Forum Discussion

Copper Contributor

Aug 06, 2018

Auto resizing worksheets to display correctly on different screens

Hi Everyone, I am having some difficulties finding some code to enable the worksheets I have developed to automatically resize when they are opened on different screen sizes (e.g. laptop, desktop scr...

excel

Macros and VBA

Lorenzo Kim

Bronze Contributor

Aug 06, 2018

Mr. Anderson

may I suggest that you make 2 SUB routines .

1. to activate zoom to all worksheets

2. to de-activate zoom to all worksheets

per codes below:

HTH

Sub zoomwindow()
    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
        ws.Activate
        ActiveWindow.Zoom = 150   'change number per your need
    Next ws
End Sub

Sub zoomoutwindow()
    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
        ws.Activate
        ActiveWindow.Zoom = 100   'can be changed to smaller window size
    Next ws
End Sub

Lorenzo Kim
Bronze Contributor
Aug 06, 2018
Mr. Anderson
If you would want zoom to activate as you open the workbook - then you have to put the codes of zoomwindow in ThisWorkbook > Private Sub workbook_open()
But I will suggest that you keep the zoomoutwindow (in a different module)- just in case you want all worksheet to revert to original size.
HTH