vba to automatically resize user form depending on screen resolution

schimmey · Jan 29th 2016

Hi All,

This appears to be a common question but I cannot find an early clear answer that makes sense.

I have a user form that will be used by different users with different screen resolutions. My question is how do I code so that when the user form is initialised:

1) the user form defaults to the centre of the excel application window,
2) sizes so that it is 15% smaller by height and width of the excel application window?

Thanks.

Paul

Grimes0332 · Jan 29th 2016

Re: vba to automatically resize user form depending on screen resolution

Your question is not clear.

Resize depending on screen resolution is not the same as resizing to the application window less 15% all around.

The easy answer is resize to a proportion of the application size:

Code

Private Sub UserForm_Initialize()




   With Me
      .StartUpPosition = 1
      .Width = Application.Width * 0.85
      .Height = Application.Height * 0.85
      .Left = Application.Left + (Application.Width * 0.85) \ 2
      .Top = Application.Top + (Application.Height * 0.85) \ 2
   End With
      
End Sub

Display More

schimmey · Jan 29th 2016

Re: vba to automatically resize user form depending on screen resolution

Hi Grimes0332,
Thanks. I would like it to fit comfortably within the screen size rather than application as I intend to reduce excel when the user form is initialised. I tried the code above but it creates s small box in the middle of the screen. I can see where you are coming from through.
Paul

gn00588950 · Jan 30th 2016

Re: vba to automatically resize user form depending on screen resolution

Quote from schimmey;764316

Hi Grimes0332,
Thanks. I would like it to fit comfortably within the screen size rather than application as I intend to reduce excel when the user form is initialised. I tried the code above but it creates s small box in the middle of the screen. I can see where you are coming from through.
Paul

To minimized Excel & Showing userform, use below:

Code

Private Sub CommandButton1_Click()


Application.WindowState = xlMinimized
UserForm1.Show vbModeless


End Sub

Here is an example of getting Screen resolution (Win32-API)
http://www.exceltip.com/genera…a-in-microsoft-excel.html

And here, as screen resolution is not actual visual size, we may need to calculate the DPI as well:
http://www.informit.com/articles/article.aspx?p=366892&seqNum=2

Thus, to resize according to Screen Size, it may need to get Screen resolution * DPI * 85%:

Code

Option Explicit
'Function to get screen resolution
Private Declare Function GetSystemMetrics32 Lib "user32" Alias "GetSystemMetrics" (ByVal nIndex As Long) As Long 


'Functions to get DPI
Private Declare Function GetDC Lib "user32" (ByVal hwnd As Long) As Long
Private Declare Function GetDeviceCaps Lib "gdi32" (ByVal hDC As Long, ByVal nIndex As Long) As Long
Private Declare Function ReleaseDC Lib "user32" (ByVal hwnd As Long, ByVal hDC As Long) As Long
Private Const LOGPIXELSX = 88  'Pixels/inch in X
Private Const POINTS_PER_INCH As Long = 72 'A point is defined as 1/72 inches


'Return DPI
Public Function PointsPerPixel() As Double
 Dim hDC As Long
 Dim lDotsPerInch As Long


 hDC = GetDC(0)
 lDotsPerInch = GetDeviceCaps(hDC, LOGPIXELSX)
 PointsPerPixel = POINTS_PER_INCH / lDotsPerInch
 ReleaseDC 0, hDC
End Function


'Resize when Userform Initialize
Private Sub UserForm_Initialize()


Dim w As Long, h As Long
    w = GetSystemMetrics32(0) ' Screen Resolution width in points
    h = GetSystemMetrics32(1) ' Screen Resolution height in points
    With Me
        .StartUpPosition = 1
        .Width = w * PointsPerPixel * 0.85 'Userform width= Width in Resolution * DPI * 85% 
        .Height = h * PointsPerPixel * 0.85 'Userform height= Height in Resolution * DPI * 85%
    End With
     
End Sub

Display More

vba to automatically resize user form depending on screen resolution

Participate now!

Share