vba to automatically resize user form depending on screen resolution

  • 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

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

  • 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

  • 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
    1. Private Sub CommandButton1_Click()
    2. Application.WindowState = xlMinimized
    3. UserForm1.Show vbModeless
    4. 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%:

    Best Regards,


    Tony


    [SIZE=1]A Peaceful Mine Is A Blessing[/SIZE]