Excel form resizing according to screen resolution

harshi7

New Member
Joined
Jun 11, 2019
Messages
9
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>
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

CalcSux78

Well-known Member
Joined
Oct 15, 2013
Messages
1,120
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

Code:
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
 

harshi7

New Member
Joined
Jun 11, 2019
Messages
9
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

Well-known Member
Joined
Nov 9, 2002
Messages
2,791

ADVERTISEMENT

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

harshi7

New Member
Joined
Jun 11, 2019
Messages
9

ADVERTISEMENT

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.
 
Last edited by a moderator:

gravanoc

Board Regular
Joined
Oct 20, 2015
Messages
170
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
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.

Code:
Declare PtrSafe Function GetSystemMetrics Lib "user32" (ByVal nIndex As Long) As Long
 
Last edited:

Forum statistics

Threads
1,136,260
Messages
5,674,681
Members
419,520
Latest member
Jennifer4Dillon

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top