VBA: UserForms: Manipulating through VBA

  • Good morning everyone,

    Just a quick question here. This is somewhat related to a post that I had made on here some time ago relating to adding controls to a userform using VBA.

    My question is this:

    Is it possible to to code your form in such a way so that when activated, it will detect the users screen resolution and resize itself so that it is always say 80% of both the x and y axis? For example, is the user has their screen resolution set at 800X600, then the userform would resize itself to 640X480.

    There is a second part to this query, but I will leave that for a little later. My thanks to anyone who is able offer any insight on this.

  • Hi DG,

    Two options here:

    1. Test for the screen resolution and have a different form for each resolution.

    Something like this:

    2. Resize the form based upon the height and width of the application window.

    Like this:


  • This approach works for me on 800x600 but I haven't tested on other PCs & other resolutions.

    Private Sub ResizeUserform()
    Load UserForm1
    UserForm1.Width = Application.Width * 0.8
    UserForm1.Height = Application.Height * 0.8
    UserForm1.StartUpPosition = 2 '(center on screen)
    End Sub

  • Good afternoon,

    Sorry for me delayed reply. I suffered a system crash that took out my internet for the weekend.

    Richie - I tried both your methods, but was only able to get the second one to work. Is the code in your first example specific to a particular version of windows or excel? I am running Windows XP Pro and Excel 2002 at home and at work, it is Windows 95 and Excel 2000. It is my home system that I attempted your first example with. Any suggestions?

  • Hi DG,

    Sorry, I should have explained further. In the first example the forms "frm800600" and "frm640480" are forms that you have sized for the appropriate screen resolution. So, if the resolution is 800x600 you display the form that you have created for that resolution, otherwise you display the form that you created for the other resolution.


  • Hi Richie,

    Yes, I understood that from your initial reply. Is there no way to just have one form that resizes itself based on the system screen resolution as opposed to being based on the application window parameters?

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!