Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: Macro to zoom to fit window, then apply that zoom factor to other sheets

  1. #1
    Board Regular Gerald Higgins's Avatar
    Join Date
    Mar 2007
    Location
    Edinburgh
    Posts
    8,759
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Macro to zoom to fit window, then apply that zoom factor to other sheets

    Hi all.

    I have set up a workbook that is sent out to lots of different users. They each keep and use their own copy.

    I have set it up so that everything looks OK and is visible on MY screen, but I'm conscious that some users may have different screen sizes, different toolbars set up, and so on, which might make some parts not immediately visible to them.

    I have set up an auto-execute macro which automatically sets the zoom factor to best fit, for several of the worksheets, and this works fine.
    Here's the code that does it.
    Code:
    Sheets("WELCOME").Select
        Range("A1:N18").Select
        ActiveWindow.Zoom = True
    By repeating this code for each worksheet, I can make each one be zoomed just right.

    However, the file contains 8 sheets that are all identically laid out, except the number of rows is different.
    What I want to do is go to the worksheet that has the largest number of rows (it's always the same worksheet, so I know which one it is), set the zoom factor for THAT worksheet (which I can do, and it always has the same number of rows), and then take THAT zoom factor, whatever it is - and it will vary depending on the user - and apply that to the other worksheets that have a similar layout.

    I could just go through each worksheet and zoom it automatically, but that would mean that some of the sheets looked very large, others very small, and I'd like them to have a consistent appearance.
    I could also specify a range on each worksheet that was similar to the appropriate range on the longest worksheet, and zoom that automatically, but that's not ideal either, because some of the row heights vary from sheet to sheet, and again I'll end up with different font sizes.

    Anyone know how to do this ?
    The following is my SIGNATURE. It's not part of any question or solution I'm posting. If it IS your solution, you've got a very weird problem !

    Sub Macro()
    ActiveCell = "IY" & Right(Application.Name, 5)
    With ActiveCell.Characters(Start:=2, Length:=1).Font
    .Name = "Webdings"
    .Color = 255
    End With
    End Sub

  2. #2
    Board Regular cornflakegirl's Avatar
    Join Date
    Nov 2004
    Location
    UK
    Posts
    2,023
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro to zoom to fit window, then apply that zoom factor to other sheets

    After you've auto-zoomed, set a variable eg iZoom = ActiveWindow.Zoom

    Then on other sheets just set ActiveWindow.Zoom = iZoom

  3. #3
    Board Regular Gerald Higgins's Avatar
    Join Date
    Mar 2007
    Location
    Edinburgh
    Posts
    8,759
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro to zoom to fit window, then apply that zoom factor to other sheets

    Thanks very much cornflakegirl.

    I should have also said that I know virtually nothing about macros - most of the ones I use are built up using the recorder, and I don't really understand what the coding does.

    I've tried doing what you suggested but couldn't get it to work.

    Here's the code
    Code:
    Dim Izoom As ActiveWindow.Zoom
        Sheets("COLLABORATE").Select
        Set ActiveWindow.Zoom = Izoom
    I'm sure I'm making a very stupid mistake here - can you tell me what it is please ?
    The following is my SIGNATURE. It's not part of any question or solution I'm posting. If it IS your solution, you've got a very weird problem !

    Sub Macro()
    ActiveCell = "IY" & Right(Application.Name, 5)
    With ActiveCell.Characters(Start:=2, Length:=1).Font
    .Name = "Webdings"
    .Color = 255
    End With
    End Sub

  4. #4
    Board Regular cornflakegirl's Avatar
    Join Date
    Nov 2004
    Location
    UK
    Posts
    2,023
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro to zoom to fit window, then apply that zoom factor to other sheets

    Um... not sure what you're doing with your Dim statement...

    Something like:

    Code:
    Dim iZoom as integer
    Sheets("WELCOME").Select
    Range("A1:N18").Select
    ActiveWindow.Zoom = True
    iZoom = ActiveWindow.Zoom
     
    Sheets("COLLABORATE").Select
    ActiveWindow.Zoom = iZoom

  5. #5
    Board Regular Gerald Higgins's Avatar
    Join Date
    Mar 2007
    Location
    Edinburgh
    Posts
    8,759
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro to zoom to fit window, then apply that zoom factor to other sheets

    Um... not sure what you're doing with your Dim statement...
    Well like I said, I really don't know what I'm doing !

    But thanks very much - your code works perfectly !
    The following is my SIGNATURE. It's not part of any question or solution I'm posting. If it IS your solution, you've got a very weird problem !

    Sub Macro()
    ActiveCell = "IY" & Right(Application.Name, 5)
    With ActiveCell.Characters(Start:=2, Length:=1).Font
    .Name = "Webdings"
    .Color = 255
    End With
    End Sub

  6. #6
    Board Regular cornflakegirl's Avatar
    Join Date
    Nov 2004
    Location
    UK
    Posts
    2,023
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro to zoom to fit window, then apply that zoom factor to other sheets

    My (limited) understanding of Dim statements is that they're just to tell the macro what type of variable you're creating - you can't do anything functional with them.

    (Why the new disclaimer on your sig? Was there actual confusion? I really like the "another day" one btw - would have voted for it in the poll if it had been there )

  7. #7
    MrExcel MVP
    Moderator
    Smitty's Avatar
    Join Date
    May 2003
    Location
    Redmond, WA
    Posts
    29,535
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Macro to zoom to fit window, then apply that zoom factor to other sheets

    Gerald,

    This may be an option, but note that there might be some problems with Using Range depending on where you want to zoom.

    Private Sub Workbook_SheetActivate(ByVal Sh As Object)
        ActiveSheet.UsedRange.Select
        ActiveWindow.Zoom = True
    End Sub



    Hope that helps,

    Why the new disclaimer on your sig? Was there actual confusion?
    Sadly, yes.

  8. #8
    Board Regular Gerald Higgins's Avatar
    Join Date
    Mar 2007
    Location
    Edinburgh
    Posts
    8,759
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro to zoom to fit window, then apply that zoom factor to other sheets

    Thanks cornflakegirl and pennysaver.

    Cornflakegirl's suggestion does seem to do what I want.

    I will take a look at pennysaver's as well, to see if it does something different.

    Was there actual confusion about the signature ?
    Yes indeed . . .
    http://www.mrexcel.com/forum/showthread.php?t=314320
    The following is my SIGNATURE. It's not part of any question or solution I'm posting. If it IS your solution, you've got a very weird problem !

    Sub Macro()
    ActiveCell = "IY" & Right(Application.Name, 5)
    With ActiveCell.Characters(Start:=2, Length:=1).Font
    .Name = "Webdings"
    .Color = 255
    End With
    End Sub

Some videos you may like

User Tag List

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
  •