User Form Display Question

[Brian]

My problem is that the User Form is to big to fit on the Screen. It works
good on 1 Computer, but when I use A computer with a smaller Screen it
becomes way to big. When that happens 30% of the Form is cut off.

Is there a code that I can run on UserForm_Initialize that will size it for
the Screen. It would be nice if it would size according to the Display
settings.

I found this code here in the forumn but I can't get it to work.

Sub size_form_and_controls()

Dim start_height As Long
Dim start_width As Long
Dim new_height As Long
Dim new_width As Long
Dim height_ratio As Double
Dim width_ratio As Double
Dim ctl As Control

With Me
'default settings on your computer
start_height = 1024
start_width = 1280
'find current screen resolution
new_height = Application.Height
new_width = Application.Width
'ratio to apply to form and all Controls
height_ratio = new_height / start_height
width_ratio = new_width / start_width
'resize the whole form
.Height = new_height
.Width = new_width
'resize the controls and text
For Each ctl In .Controls
ctl.Top = ctl.Top * height_ratio
ctl.Left = ctl.Left * width_ratio
ctl.Height = ctl.Height * height_ratio
ctl.Width = ctl.Width * width_ratio
'if control has no font then skip
On Error Resume Next
ctl.Font.Size = ctl.Font.Size * height_ratio
On Error GoTo 0
Next ctl
End With

 

[Ryan H]

I built this code for the same issue you're having. Just call this procedure
in your userforms Intialize Event. Hope this helps! If so, let me know,
click "YES" below.

Sub AdjustScreenSize()

' move user form to upper left of screen and show scoll bars
With ProductForm

' if form is wider and higher than window width
If .Height > Application.Height And .Width > Application.Width Then

' increae height/width of userform so scroll bar doesn't cover
controls
.Width = .Width + 15
.Height = .Height + 15

' add scroll bars
.ScrollBars = 3 ' 3 show both hor./ver. bar
.ScrollHeight = .Height
.ScrollWidth = .Width

' adjust position of userform
.Top = Application.Top
.Height = ActiveWindow.Height
.Left = Application.Left
.Width = ActiveWindow.Width

Exit Sub
End If

' if form is taller than window height
If .Height > Application.Height Then

' increase height of userform so scroll bar doesn't cover controls
.Width = .Width + 15

' add horizontal scroll bars
.ScrollBars = 2 ' 2 show ver. bar
.ScrollHeight = .Height

' adjust position of userform
.Top = Application.Top
.Height = ActiveWindow.Height

Exit Sub
End If

' if form is wider than window width
If .Width > Application.Width Then

' increae height of userform so scroll bar doesn't cover controls
.Height = .Height + 15

' add horizontal scroll bars
.ScrollBars = 1 ' 1 show hor. bar
.ScrollWidth = .Width

' adjust position of userform
.Left = Application.Left
.Width = ActiveWindow.Width

Exit Sub
End If
End With

End Sub

 

[Brian]

It works.

I was really hoping to it would resize the user form according to the screen
size though. Can that be done? Is it possible to resize the user form
according to the screen size?


Thanks

 

[Ryan H]

I believe this code should work for what ever screen size you have. Plus, it
will adjust the userform size in case your user has the Excel application not
maximized.