Adjust userforms to fit in screen

R

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?
 
J

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
 
R

Ryan H

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

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
 
J

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.
 
R

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.
 
R

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?
 
J

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.


Ryan H said:
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?
 
J

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


Ryan H said:
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?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top