Auto-adjust According to Resolution

[Risky Dave]

Hi,

I have an application that forces my users to navigate about it using a set
of buttons. When this is run on different users machines (with different
screen resolution settings) the formatting of the page gets knocked off.

Is it possible to have XL recognise the resolution that a given instance is
set to and auto adjust the % slider (this is in Office '07 under Vista) to
ensure that a specific area of the sheet is displayed?

TIA

Dave

 

[Trevor Williams]

Hi Dave - this is a 'quick and dirty' method, and sombody may give a cleaner
answer:

You can use the 'zoom to selection' option e.g. if your sheets used columns
A-S you can select the columns and 'zoom to selection' - maybe trigger it
using the Workbook_open event

Sub ResizeSheets()
Dim sh as worksheet
For each sh in activeworkbook.worksheets
sh.select
Range("A1:S1").Select
ActiveWindow.Zoom = True

 

[Trevor Williams]

Sorry Dave - a bit premature there!
Here's the code...

Sub ResizeSheets()
Dim sh as worksheet
Application.Screenupdating = False
For each sh in activeworkbook.worksheets
sh.select
Range("A1:S1").Select
ActiveWindow.Zoom = True
Next sh
Application.Screenupdating = True
End sub

As far as I'm aware you have to use the 'select' option on the sheets and
the range.

HTH - Trevor Williams