Forum Discussion

Gregory ANDERSON's avatar
Gregory ANDERSON
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 screen, projector, ipad etc).

 

I have found the below code:

 

Sub sheetname ()

sheetnumber.Range("a1:ae1").select

activewindow.zoom = true

End sub

 

This works for the immediate sheet when the excel workbook is opened, but I have "command buttons" that take you to other worksheets within the workbook and they are not resizing when it is opened????

 

If anyone could help that would be very much appreciated.

Kind regards,

Greg.

 

Excel
Macros and VBA
  • Lorenzo Kim's avatar
    Lorenzo Kim
    Bronze Contributor
    Aug 07, 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's avatar
      Lorenzo Kim
      Bronze Contributor
      Aug 07, 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

       

  • Lorenzo Kim's avatar
    Lorenzo Kim
    Bronze Contributor
    Aug 09, 2018

    Hi there,

    did my suggestions helped?

    if not, this will bring up your query for others to see and maybe give you a more effective solution.

    thanks

    • Gregory ANDERSON's avatar
      Gregory ANDERSON
      Copper Contributor
      Aug 09, 2018

      Hi Lorenzo, thank you so much for your reply and assistance, I tried your code and it did what you said it would do, but wasn't exactly what I needed.

       

      I have created a worksheet that has command tabs that link to other sheets with relevant information (as per picture) and was wanting the sheets to automatically"fit" whatever screen that they are opened on (e.g. if you open the worksheet on an ipad it is the correct view, if you then open the same worksheet on a laptop it is the correct view and so on).

       

       

       

      • Lorenzo Kim's avatar
        Lorenzo Kim
        Bronze Contributor
        Aug 09, 2018
        There are many able person here at the forum, I'm sure they will see your query and help you.
        regards..

Resources