Results 1 to 12 of 12
  1. #1
    Plutonium Lounger Leif's Avatar
    Join Date
    Dec 2000
    Location
    U.K.
    Posts
    14,010
    Thanks
    0
    Thanked 0 Times in 0 Posts

    View to fit (97 > 2002)

    Is there any way Excel can determine the screen resolution so that the view can be adjusted?
    i.e. if the screen resolution is 1024 x 768 then 100% is fine, but if it is 800 x 600 I need to change it to 75%
    TIA

  2. #2
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: View to fit (97 > 2002)

    I think not, but you might find the code in this post a start. HTH
    Gre

  3. #3
    Plutonium Lounger Leif's Avatar
    Join Date
    Dec 2000
    Location
    U.K.
    Posts
    14,010
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: View to fit (97 > 2002)

    Thanks, Unk - works a treat in combination with ActiveWindow.Zoom

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: View to fit (97 > 2002)

    Leif.

    If all you want is to make sure a certain range of the spreadsheet is displayed at once, you could do something like

    Range("A1:K1").Select
    ActiveWindow.Zoom = True

    which would ensure that Colums A to K are visible. If required you can incled the number of rows as well. Basically just select therange and then ActiveWindow.Zoom.

    This way also makes allowance for the arrangement of toolbars etc, so may be better than just setting a zoom based on resolution.

    Andrew

  5. #5
    Plutonium Lounger Leif's Avatar
    Join Date
    Dec 2000
    Location
    U.K.
    Posts
    14,010
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: View to fit (97 > 2002)

    Good point about toolbars, Andrew.
    (Just as I had managed to work out the previous method <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15> )

    Is there a method to use zoom on a non-active window?
    I'm trying the same trick on another worksheet at the same time, but putting
    Application.ScreenUpdating = False
    doesn't prevent the other worksheet from flashing quickly on the screen.

  6. #6
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: View to fit (97 > 2002)

    Is the worksheet is in the same workbook, you could try selecting both (or more) sheets as follows :

    Sheets(Array("Sheet1", "Sheet2")).Select
    Range("A1:K1").Select
    ActiveWindow.Zoom = True

    That should work. If there are different workbooks, you can only assign a specific zoom value to the inactive one as a %

    Hope taht gets you somewhere

    Andrew

  7. #7
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: View to fit (97 > 2002)

    Alternatively, you could test the Worksheet.Activate event.
    Gre

  8. #8
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: View to fit (97 > 2002)

    Leif,

    Unkmunka is probably correct - if you use the WorksheetActiave event you can the have different zooms for each sheet, and it also deals with another problem I should have mentioned, If you select multible sheets, you need to be sure to de-select them as you can unwittingly alter cell values etc.


    Andrew

  9. #9
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: View to fit (97 > 2002)

    Leif,

    Just the other day, I found in the WORD object model that it has a System class, which includes properties for HorizontalResolution and VerticalResolution. They exist in both Word 97 and 2000. But it looks like this does NOT exist in Excel 97 which I have here at home. I'll try it in 2000 when I'm at work.

    Fred

  10. #10
    Plutonium Lounger Leif's Avatar
    Join Date
    Dec 2000
    Location
    U.K.
    Posts
    14,010
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: View to fit (97 > 2002)

    I managed to get the screen width OK from the function Unkamunka pointed me to, but I've settled for the original plan with Andrew's zoom.
    Unfortunately I can't use the grouping of worksheets to simultaneously set the zoom on two worksheets as they have totally different layouts and selecting the same range on both makes one totally out of range.
    I've got round it by setting the original zoom level to something unlikely - the OnOpen event checks to see what the current zoom level is and if it is not that, it doesn't bother to change it. The workbook will be saved by the end user, so it is likely only to happen the once so I can put up with the screen flashing just the very first time.

    Thanks to all for your suggestions and guidance.

  11. #11
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: View to fit (97 > 2002)

    Leif,

    Did you try the SheetActivate event fore each sheet to select teh individual zoom value for that sheet ?.

    Private Sub Worksheet_Activate()
    Range("ZoomRange").Select
    ActiveWindow.Zoom = True
    [A1].Select
    End Sub

    Enter the above in each sheet code module (not a genera module) and set the ZoomRange to the appropriate range for that sheet.

    Andrew

  12. #12
    Plutonium Lounger Leif's Avatar
    Join Date
    Dec 2000
    Location
    U.K.
    Posts
    14,010
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: View to fit (97 > 2002)

    If only it were that simple!
    (For me, anyway!)
    The workbook consists of three worksheets, the tabs scroll bars, R&C headers are all hidden on the only one the user will see.
    The worksheet that is visible (#1) is totally locked with a few buttons that trigger macros for accessing some equipment throught the serial port. The data that is collected is entered onto sheet #2, suitable for printing, with #1 being formatted with lovely colours showing status of alarms and things - the 'data' cells being linked to #2.
    If the user wants a printout, sheet #2 is scaled to fit and prints out perfectly from a 'print' macro.
    If the user wants to keep a copy of the data, another macro simply copies #2 to a new (unprotected) workbook which I want to keep as vanilla as possible.

    I think I've got what I need - the most important one being what the user is confronted with on starting the 'program'.

    I'm quitting while I'm ahead <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

Posting Permissions

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