Resize Userform to current screen size

[Fred Wayne]

Hello, I have a concern about the following: I have a user form called XTEST32. It works perfectly; the only issue that I have is that when I open it on my computer screen, it is displayed correctly.

I mean, the size of the userform fits to have a have set, and the ComboBoxes, images, and CommandButtons are with the correct size.



But, when I open the same form on another computer, it works and runs perfect; the only issue is that the screen size is a little smaller than mine, so the form is displayed but does not fit the screen size; in other words, the form turns too big for that screen size (which is smaller than mine, as I mentioned before), and the buttons and ComboxBoxes do not fit in the same userform.

I am trying to find out a way to make it fit; it has been very hard for me to do so.

Is there a way to make my Userform (or any) fit on all screen sizes?



I would appreciate all your valuable help.



Thank you in advance.

 

[Marc L]

Hello, that's one of the classic usual Excel mess, wasting time with an UserForm randomly 'working', see workarounds on web.​

Not a good idea to use an UserForm for data, the reason why it's far more efficient to use a form worksheet rather than an UserForm,​

Excel is very not a database software …​

A classic workaround is to create / fit the UserForm on the smaller screen size.​

​

 

[Fred Wayne]

Thank you so much for your support and time. Have an amazing day.

 

[rlv01]

Is there a way to make my Userform (or any) fit on all screen sizes?

@Fred Wayne, something like this will solve the problem 90% of the time. Add it to your form's UserForm_Activate event.

Private Sub UserForm_Activate()
    'Adjust UserForm size & position if needed.
    With Me
        .StartUpPosition = 0

        'See if width is too wide, and adjust if it is.
        If .Width > Application.Width Then
            .Width = Application.Width * 0.8
            .Height = Application.Height * 0.8
        End If

        'See if height is too high, and adjust if it is.
        If .Height > Application.Height Then
            .Height = Application.Height * 0.8
            .Width = Application.Width * 0.8
        End If

        'Center form on whatever monitor excel is running on.
        .Left = Application.Left + (0.5 * Application.Width) - (0.5 * .Width)
        .Top = Application.Top + (0.5 * Application.Height) - (0.5 * .Height)
    End With
End Sub