Form too large on smaller monitors

[kennycwhite]

I have a form that when it's viewed on smaller monitors/resolutions, part of the form is cut off. Does anyone if there is a way so when the form size exceeds the monitor/resolution size that it scales down the full form to near maximum size and adds the scroll bars? I don't want to change the zoom. I basically want the form to behave like things do in Windows that they don't exceed the screen size and if the form is too large, scroll bars automatically show. I don't want the form to maximize on the screen when the monitor/resolution is large enough because then there is a bunch of blank space that looks funny. I was playing around with some code I found that might help (I don't have it anymore) but I kept getting errors about the code needing to be updated to 64 bit so that's something that needs to be taken into account. Thank you.

 

[bugmonsta]

I came across exactly the same issue a few years ago and all the advice at the time was to redesign the whole form(s) - I am too lazy (or time commitment was an issue). It turns out that Excel forms are not designed to resize.... so found this bit of code which worked for me.

It was designed for use on a Mac, but works fine on PC. I call this when I initialize the form and you can just try changing the 0.85 to see what works best for you (it may of course not work at all!!)

Public Sub AdjustSizeForMac()
Dim ControlOnForm As Object
    'Change size coefficient
Const SizeCoefForMac = 0.85
With MainForm
    'Change Userform size
    .Width = .Width * SizeCoefForMac
    .Height = .Height * SizeCoefForMac
    'Change controls/font on the userform
    For Each ControlOnForm In .Controls
    With ControlOnForm
    .Top = .Top * SizeCoefForMac
    .Left = .Left * SizeCoefForMac
    .Width = .Width * SizeCoefForMac
    .Height = .Height * SizeCoefForMac
On Error Resume Next
    .Font.Size = .Font.Size * SizeCoefForMac
On Error GoTo 0
End With
    Next
End With
End Sub

 

[kennycwhite]

That worked without a hitch. I added onto it so it would first check the screen resolution against the size of the form and if the form was larger it determine the scale to use based on whether the width or height was the determining factor.

Resolution_Width = Application.Width
Resolution_Height = Application.Height
If Resolution_Width < 934 Or Resolution_Height < 645 Then
Dim ControlOnForm As Object
'Change size coefficient
Width_Scale = Resolution_Width / 934
Height_Scale = Resolution_Height / 645
Scale_Coefficient = WorksheetFunction.Min(Width_Scale, Height_Scale)
With Validate_Installation_Form
'Change Userform size
.Width = .Width * Scale_Coefficient
.Height = .Height * Scale_Coefficient
'Change controls/font on the userform
For Each ControlOnForm In .Controls
With ControlOnForm
.Top = .Top * Scale_Coefficient
.Left = .Left * Scale_Coefficient
.Width = .Width * Scale_Coefficient
.Height = .Height * Scale_Coefficient
On Error Resume Next
.Font.Size = .Font.Size * Scale_Coefficient
On Error GoTo 0
End With
Next
End With
End If

 

[kennycwhite]

In the case of my form it is 934 x 645 which is where those numbers came from. I actually had to increase the values in the Height_Scale calculations. When I used 645, the form overlapped my taskbar slightly. Width wasn't actually a problem but this way the code is there if I add to the form and it gets wider. Thank you for the assistance.