Adjust userforms to fit in screen

[Ryan H]

I have many different users for one of my workbooks. The workbook has many
different sized userforms. Some userforms are too large for the users
screen. Is there a way for VBA to automatically adjust the userform to fit
in the users screen properly like other software does?

 

[Jacob Skaria]

Try the below

Private Sub UserForm_Initialize()
With Me
.Height = Application.Height
.Width = Application.Width
.Left = 0
.Top = 0
End With
End Sub

 

[Ryan H]

I don't think I need to expand the userform to fit the screen. I need to
shrink the userform. Any ideas?

 

[Tom Hutchins]

Here is a variation:

Private Sub UserForm_Initialize()
With Me
If (.Height > Application.Height) Or (.Width > Application.Width) Then
.Height = Application.Height * 0.9
.Width = Application.Width * 0.9
.Left = 0
.Top = 0
End If
End With
End Sub

Please note that this doesn't shrink everything on the form proportionately
to fit; it truncates part of the form on the right and/or bottom to make it
fit on the screen. You might do better to check the form size vs. screen area
as above and notify your users to increase their screen resolution if
necessary. Or, build your form to a "lowest common denominator" resolution.

Hope this helps,

Hutch

 

[JLGWhiz]

If some users are having a screen overflow, it could be because they have
their screen resolution set to an unconventional setting. If so, they are
forcing you to alter a standard feature to meet their non-standard whims. I
would check their settings before trying to accommodate them.

 

[Ryan H]

That code does get me thinking and maybe I can think of something, but all
that does is cut off the controls on the userform if the userform is too big.
I think I need to adjust the size of the userform with the zoom property.

 

[Ryan H]

I did that. They have a lap top and the resolution is as high as it can go,
1280x800 @ 96 dpi. Would that be a problem?

 

[JLGWhiz]

It is one of the possibilities. Depends on the size of the monitor and what
the normal resolution should be. I am just thinking that trying to solve
the problem with code may not be the best answer, although it could probably
be done. If the resolution is set properly, they should get the same view
you had originally. Of course, this assumes your resolution is also set
properly.

I did that. They have a lap top and the resolution is as high as it can
go,
1280x800 @ 96 dpi. Would that be a problem?

 

[john]

Ryan,
I found this on the web sometime ago - never used it myself but may (or may
not) be useful to you. Paste all in standard module.

Type RECT
x1 As Long
y1 As Long
x2 As Long
y2 As Long
End Type
' NOTE: The following declare statements are case sensitive.
Declare Function GetDesktopWindow Lib "user32" () As Long
Declare Function GetWindowRect Lib "user32" _
(ByVal hWnd As Long, rectangle As RECT) As Long
'*****************************************************************
' FUNCTION: GetScreenResolution()
'
' PURPOSE:
' To determine the current screen size or resolution.
'
' RETURN:
' The current screen resolution. Typically one of the following:
' 640 x 480
' 800 x 600
' 1024 x 768
'
' AUTHOR:
' Tom Ogilvy
'*****************************************************************
Public Function GetScreenResolution() As String
Dim R As RECT
Dim hWnd As Long
Dim RetVal As Long

hWnd = GetDesktopWindow()
RetVal = GetWindowRect(hWnd, R)
GetScreenResolution = (R.x2 - R.x1) & "x" & (R.y2 - R.y1)
End Function

Sub test()
MsgBox GetScreenResolution

End Sub
--
jb

I did that. They have a lap top and the resolution is as high as it can go,
1280x800 @ 96 dpi. Would that be a problem?

 

[Jacob Skaria]

Hi Ryan

Please refer the below thread

http://www.microsoft.com/office/com...69a4&mid=8215b77d-68b5-4166-92dd-31817f6269a4


You can also set this to Application.UsableWidth

Private Sub UserForm_Initialize()
With Me
.Height = Application.UsableHeight
.Width = Application.UsableWidth
.Left = 0
.Top = 0
End With
End Sub

If this post helps click Yes