Results 1 to 4 of 4

Thread: Page sizing in a Macro
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular Dan Wilson's Avatar
    Join Date
    Feb 2006
    Location
    North Carolina
    Posts
    355
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Page sizing in a Macro

    Good day. I am using Excel 2013 on Windows 10 Pro. I have a workbook that contains 5 worksheets. Each worksheet allows the user to enter data into rows that reach out to Column EA. Column A contains data that is necessary to be visible while entering data out in the far end of the worksheet (for example - name). To make this happen I use the Control Key in conjunction with the mouse to reduce the viewable size of the page. Sometimes I forget to return the size to normal.
    In addition to this, there are Macros that allow each of the various worksheets to be sorted in as many as 5 different ways.
    As the entire workbook opens up for the first time, there is a Macro that runs to reset each worksheet to its nominal sort condition.
    Is there a statement that I can add to the Reset Macro to make each worksheet display with a preset Row Height and Cell Width?
    Thank you for any help with this.
    Have an Ice Cream Day!

  2. #2
    Board Regular
    Join Date
    Mar 2016
    Posts
    222
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Page sizing in a Macro

    To reset the size to normal you can use the following code. It's based on view percentage, not preset row height though. Put this code in the ThisWorkbook code section, and it will run every time the workbook is open.

    Code:
    Private Sub Workbook_Open()
        Dim ws As Worksheet, wOpen as Worksheet
        Set wOpen = ActiveSheet
        For Each ws In ActiveWorkbook.Worksheets
            ws.Activate
            ActiveWindow.Zoom = 100
        Next ws
        wOpen.Activate
    End Sub
    To reset the sorting, it's too hard to provide an answer without knowing more data about the sort range. However, you can probably get most of the answer yourself by recording a macro while setting the sorting how you want it. Then, you can use that code after the ActiveWindow.Zoom statement above (before the Next ws). If the recorded code is generic (e.g., no code identifying the specific worksheet, etc.), it should work on all pages without further modification. If not, you might have to change the specific code to generic code first.

  3. #3
    Board Regular Dan Wilson's Avatar
    Join Date
    Feb 2006
    Location
    North Carolina
    Posts
    355
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Page sizing in a Macro

    Good day shknbk2. Your fix is exactly what I wanted. It works perfectly. Thank you!
    Dan Wilson...
    Have an Ice Cream Day!

  4. #4
    Board Regular
    Join Date
    Mar 2016
    Posts
    222
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Page sizing in a Macro

    You're welcome. Glad I could help.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •