Results 1 to 7 of 7
  1. #1
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I fear that I know the answer to this before asking, but I have done that previously, and been pleasantly surprised......

    Is it possible to control the zoom setting of a worksheet, in line with a users monitor size / settings. For example, A1:V39 is the range that is required to be 'seen', designed on my pc to fit perfectly to my screen size. However, other users may have smaller or larger screen's, can this then be manipulated to a perfect fit?

    Any tricks?

  2. #2
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts
    Do you mean something as simple as selecting A1:V39 and then Zoom to Selection?
    • Excel 2007 this is on the View tab, Zoom to Selection button
    • Excel 2003 this can be done with the arrow next to the toolbar control for changing zoom level
    • VBA
      • Activeworkbook.ActiveSheet.Range("A1:V39").Select
      • ActiveWindow.Zoom=True

  3. #3
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Yes, I think.... Thanks

    I have several worksheets within a workbook where I want to avoid the user needing to scroll horizontally. I would like to use the ws activate event, assess the used columns range, and fit accordingly. Something along the lines of:

    Code:
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    
    
    Application.WindowState = xlMaximized
    ActiveWindow.WindowState = xlMaximized
    ActiveWorkbook.ActiveSheet.Range("A1:W1").Select 'How to identify used column range?
    ActiveWindow.Zoom = True
    ActiveWorkbook.ActiveSheet.Range("A1").Select
    
    End Sub

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    I don't think you'd want to use the SelectionChange event - this would fire each time the user moves to another cell.

    You could change the line

    ActiveWorkbook.ActiveSheet.Range("A1:W1").Select

    to
    ActiveSheet.UsedRange.Rows(1).Select

  5. #5
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Yes, it's the sheet activate procedure I am using, sorry for the confusion.

    Is the 'select' necessary here?

    Code:
    Private Sub Worksheet_Activate()
    	Application.WindowState = xlMaximized
    	ActiveWindow.WindowState = xlMaximized
    	ActiveSheet.UsedRange.Rows(1).Select
    	ActiveWindow.Zoom = True
    	ActiveWorkbook.ActiveSheet.Range("A1").Select
    End Sub

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    The line

    ActiveSheet.UsedRange.Rows(1).Select

    is necessary here because the line

    ActiveWindow.Zoom = True

    adjusts the zoom percentage so that the current selection fits in the window.

  7. #7

Posting Permissions

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