Target a screen resolution

I run on a wide screen 22? monitor (1680 x 1050). More than once I’ve delivered a prototype to the customer, and they’ve needed to scroll to find all the buttons, because their monitor was smaller than mine.

Here is a procedure for resizing the Excel Application to a specified dimension (measured in pixels).
Now I can develop on my screen, knowing what it will look like on the customers.

Declare Function GetSystemMetrics Lib “user32” (ByVal nIndex As Long) As Long
Sub ChangeApplicationSize()
    Const cDesiredWidth = 1280, cDesiredHeight = 1024
    Dim lngSystemWidth As Long, lngSystemHeight As Long
    Dim dblWidthRatio As Double, dblHeightRatio As Double
    Dim dblWidth As Double, dblHeight As Double
    Dim dblLeft As Double, dblTop As Double
    Application.WindowState = xlMaximized
    lngSystemWidth = GetSystemMetrics(SM_CXSCREEN)
    lngSystemHeight = GetSystemMetrics(SM_CYSCREEN)
    dblWidthRatio = Application.Width / lngSystemWidth
    dblHeightRatio = Application.Height / lngSystemHeight
    dblWidth = cDesiredWidth * dblWidthRatio
    dblHeight = cDesiredHeight * dblHeightRatio
    dblLeft = (Application.Width – dblWidth) / 2
    dblTop = (Application.Height – dblHeight) / 2
    Application.WindowState = xlNormal
    Application.Width = dblWidth
    Application.Height = dblHeight
    Application.Left = dblLeft
    Application.Top = dblTop
End Sub

Simply change the constants cDesiredWidth and cDesiredHeight to the target screen resolution.

Note: It’s an approximate resize – it resizes to a few pixels wider than it should.

Posted in Uncategorized

6 thoughts on “Target a screen resolution

  1. I use a more low-tech approach.
    I’ve created a besktop background picture with rectangles superimposed. 640*480, 800*600, 1024*768. When creating spreadsheets, forms, webpages, whatever, I resize the app over the rectangles from time to time to see what it looks like. It has been very useful.

  2. Rob – Nice approach :) Personally I use a software shipped with the graphic card that I can easily switch resolution with.

    Harald – Smart solution but do You still use 640*800? For me it’s outdated and gone.

    Kind regards,

  3. I don’t think we have any 640 screens left in the organization. It’s there nowadays more to remind me that my users may not run my creations in full screen all the time and that the work should resize so that scrolling is unneeded.

  4. In Word

    Private Sub UserForm_Initialize()
      With Application.System
        Me.Height = .VerticalResolution * 0.6
        Me.Width = .HorizontalResolution * 0.6
      End With
    End Sub

    in Excel

    Private Sub UserForm_Initialize()
        Application.ActivateMicrosoftApp xlMicrosoftWord    
        With getobject(,“Word.Application”).Application.System
            Me.Height = .VerticalResolution * 0.6
            Me.Width = .HorizontalResolution * 0.6
        End With
    End Sub
  5. Excellent idea. I run the same resolution as you, but almost all the rest of the people at our work have decent 19? screens that run 1280×1024 natively. However, this doesn’t stop many of them driving their monitors in SuperFuzz-O-Vision at 1024×768 or even [shudder] 800×600, claiming that they can “see things better” at those resolutions.

    While I’m perfectly happy to develop for 1280×1024 or even 1024*768, squashing stuff down so it fits neatly on a 800×600 screen is I think, in 2009, not worth considering.

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.