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
Const SM_CXSCREEN = 0, SM_CYSCREEN = 1
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
Const SM_CXSCREEN = 0, SM_CYSCREEN = 1
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.
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.
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,
Dennis
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.
In Word
With Application.System
Me.Height = .VerticalResolution * 0.6
Me.Width = .HorizontalResolution * 0.6
End With
End Sub
in Excel
Application.ActivateMicrosoftApp xlMicrosoftWord
With getobject(,“Word.Application”).Application.System
Me.Height = .VerticalResolution * 0.6
Me.Width = .HorizontalResolution * 0.6
End With
End Sub
Nice script.
I simply assume a sensible width and work for that but this approach is cleaner.
Thanks for sharing!
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.