Excel form resizing according to screen resolution

[harshi7]

Hello All,

I have

an excel-vba userform that has some checkboxes, lables and buttons. I have given the specific height and width for the form which is almost 3/4 th size of my Screen. This form is used by my colleagues with different Screen resolutions. For few of my colleagues, the form is too big (Greater than Screen size) and for few , it is perfect.

So I thought of initializing my form with the Excel application size. But the Problem is I have Images in the form which doesnt fit properly if I fit the form with Screen size.

I have looked into different solutions but none of them seem to fit my Problem.
Please help me how to maintain the form which Looks exactly the same with all controls with different Screen resolutions.


Thanks in advance ​

_{^{<strike>
</strike>}}​

 

[CalcSux78]

Welcome to the forum!

I had a similar dilemma w/ a group I support. I polled the group and found 2 primary resolutions: the standard at the time was 1280 X 1024, the low-res option (to make the screen appear zoomed in) was 1024 X 768.

My solution was to identify screen resolution and called one of two userforms. My "ufStandard" was my original userform. "ufLoRes" was a copy of the standard one, but with everything sized for the lower screen resolution. I did add a third option to detect when a user would use settings outside the norm and prompt them to change their resolution.

Source: http://www.vbaexpress.com/kb/getarticle.php?kb_id=32

Option Explicit

Private Declare Function GetSystemMetrics Lib "user32.dll" (ByVal nIndex As Long) As Long
Const SM_CXSCREEN = 0
Const SM_CYSCREEN = 1


Sub VerifyScreenResolution(Optional Dummy As Integer)
Dim x%
Dim y%
Dim MyMessage$
Dim MyResponse As VbMsgBoxResult


x = GetSystemMetrics(SM_CXSCREEN)
y = GetSystemMetrics(SM_CYSCREEN)
Select Case True
    Case x = 1280 And y = 1024
        ufStandard.Show 0
    Case x = 1024 And y = 768
        ufLoRes.Show 0
    Case Else
        MyMessage = "Your current screen resolution is " & x & " X " & y & vbCrLf & _
            "This program  was designed to run with a screen resolution of 1280 X 1024" & _
            "or 1024 X 768 and may not function properly with your current settings." & _
            vbCrLf & "Would you like to change your screen resolution?"
        MyResponse = MsgBox(MyMessage, vbExclamation + vbYesNo, "Screen Resolution")
End Select
If MyResponse = vbYes Then
    Call Shell("rundll32.exe shell32.dll,Control_RunDLL desk.cpl,,3")
End If
End Sub


Sub butt*******()
VerifyScreenResolution
End Sub

 

[NdNoviceHlp]

Hi harshi7 and Welcome to the Board! In addition to screen resolution the dpi or personalize screen settings will also affect the userform display. Here's some code that should help. Dave
https://www.mrexcel.com/forum/excel-questions/1093205-userform-not-rendered-correctly-visually.html

 

[harshi7]

Hello,

This helps a lot. Thank you so much!

But I am wondering is there any way that form size will get updated automatically with respect to the screen resolution ? instead of setting different forms for different resolutions?

 

[NdNoviceHlp]

Yes.... Did U trial the code at the link posted? Dave

 

[harshi7]

Hi, Please check your inbox

 

[harshi7]

Hi Dave,

I tired your code. I am not able to see any changes in the user form even after changing the resolution. Please help me.

 

[gravanoc]

I just want to add that if for some reason CalcSux's subroutine isn't working, you may need to modify the Declare function based on the version of Excel you are using.

Declare PtrSafe Function GetSystemMetrics Lib "user32" (ByVal nIndex As Long) As Long

 

[harshi7]

It worked as expected. Thanks a lot

 

[NdNoviceHlp]

You are welcome. Thanks for posting your outcome. Dave