Excel

Set your workbook to adjust to users screen resolution.

Ease of Use

Intermediate

Version tested with

2000, 2003 

Submitted by:

johnske

Description:

On opening, the users screen resolution is checked, then the workbooks properties are adjusted so that it's always viewed in the same proportion on all PCs. In the attached example, the workbook is positioned in the top LH corner of the monitor and its height and width are set at approximately 2/3 of the user's monitor's height and width. 

Discussion:

When the workbook contains graphics, these may not appear as they should when viewed at a different screen resolution. In such cases all the required settings can be made by the person providing the workbook so that the graphics should appear in the same proportion to all viewers whatever their screen resolution may be. (Even when not used on another machine, this is handy when you want the book to always open at the same size and position on your monitor). 

Code:

instructions for use

			

'<< CODE FOR THE "ThisWorkbook" MODULE >> '<< NOTE THAT SCREEN HEIGHT & WIDTH ARE >> '<< MEASURED IN PIXELS, WHILE APPLICATION >> '<< & WINDOW HEIGHTS AND WIDTHS ARE >> '<< MEASURED IN POINTS. (pixel ~ 3/4 point) >> Option Explicit Private Sub Workbook_Open() 'N.B. a screen resolution of 800x600 pixels was used for this E.G. '//Obtain current users screen width & height (in pixels) Run ("MonitorInfo") With Application 'cancel any xlMaximized .WindowState = xlNormal '<<Set values for the application window>> '//POSITION WITH RESPECT TO MONITOR .Top = 1 '< points .Left = 1 '< points '//WIDTH 'replace 400 with the width you want 'replace 800 with your screen width .Width = 400 * ScrWidth / 800 '//HEIGHT 'replace 300 with the height you want 'replace 600 with your screen height .Height = 300 * ScrHeight / 600 With .ActiveWindow 'cancel any xlMaximized .WindowState = xlNormal '<<Set values for the book inside the application>> '//POSITION WITH RESPECT TO APP. WINDOW .Top = 1 '< points .Left = 1 '< points 'ZOOM 'replace 800 with your screen width .Zoom = 100 * ScrWidth / 800 '< 100 is % 'WIDTH 'replace Application.UsableWidth 'with a number for the width you want .Width = Application.UsableWidth 'HEIGHT 'replace Application.UsableHeight 'with a number for the height you want .Height = Application.UsableHeight End With End With End Sub '<< CODE FOR THE STANDARD MODULE >> Option Explicit Public ScrWidth&, ScrHeight& Declare Function GetSystemMetrics32 Lib "User32" _ Alias "GetSystemMetrics" (ByVal nIndex&) As Long Private Sub MonitorInfo() ScrWidth = GetSystemMetrics32(0) '< in pixels ScrHeight = GetSystemMetrics32(1) End Sub

How to use:

  1. Open an Excel workbook
  2. Select Tools/Macro/Visual Basic Editor
  3. In the VBE window, select Insert/Module
  4. Copy and paste the "code for the standard module" into this Module
  5. Select View/Project Explorer
  6. Select the ThisWorkbook module
  7. Copy and paste the "code for the ThisWorkbook module" to this Module
  8. Now select File/Close and Return To Microsoft Excel
  9. Don't forget to save your changes...
 

Test the code:

  1. By dragging...
  2. Manually re-size and reposition your application window (the outer frame)
  3. Manually re-size and reposition your "workbook"window (the inner frame)
  4. Save and close the book
  5. On re-opening, the book will re-size and re-position itself according to your coded settings
  6. (The example "resolution.xls" will be around 2/3 the height & width of your monitor)
 

Sample File:

resolution.zip 9.58KB 

Approved by mdmackillop


This entry has been viewed 413 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2014 VBA Express