Centring Userform On Screen

[Ark68]

I am using (trying) this code to ensure a dynically sized userform stays centred on the user's screen without a static screen size being defined. (will centre regardless of the screen size).

For the most part it works pretty slick, expect on my lap top. The userform centres horizontally, but not vertically. The top of userform (with the X) is hidden by the worksheet column header. (so I caan't access the X or move the form should I wish to).

        frmservice.Top = Application.Top + (Application.UsableHeight / 2) - (frmservice.Height / 2)
        frmservice.Left = Application.Left + (Application.UsableWidth / 2) - (frmservice.Width / 2)

Can anyone suggest any improvements that would eliminate this issue while still serving its intended purpose for the most part?

 

[Rick Rothstein]

You don't have to use code to do this... simply select the UserForm in the VBA editor and then set its StartUpPosition to 2-CenterScreen in the Properties window (press F4 if you don't see it).

 

[Ark68]

Thanks Rick! Will this method maintain the centering as the userform is resized?

 

[Rick Rothstein]

When you show the UserForm, no matter what size it is at the time it is shown, that UserForm will be centered. If you mean you are going to change its size after it is on the screen, then no, that resized UserForm will not re-center itself. If it is this latter resizing method you are referring to, then how is it being resized? If you are doing it in code, then all you have to do is move the UserForm half the change in length and half the change in width that you are resizing it by from its current location.

 

[Ark68]

Thanks Rick .... is the code I posted in post #1 not doing this? It works on my desktop, and works horizontally on my laptop. It just doesn't appear to centre it vertically on my laptop. With the taptop, the top portion of the userform is cut off. (too high).

 

[diddi]

An alternative approach could be to use the following...

    Dim X  As Long
    Dim Y  As Long
    
    X = GetSystemMetrics(0)
    Y = GetSystemMetrics(1)
'this sets to full screen
    Me.Width = 3 / 4 * X
    Me.Height = 3 / 4 * Y
'or you can fiddle with Me.Top etc and position elsewhere.
'this is not dependent on the position of the excel application window

 

[Ark68]

Thanks diddi for your suggestion. I tried it, but GetSystemMetrics threw me a "Sub or Function Not Defined" error.

 

[diddi]

did you declare it? it s an API fn

Public Declare PtrSafe Function GetSystemMetrics Lib "user32.dll" (ByVal nIndex As Long) As Long

 

[Ark68]

So, I've had a chance to correct and test this and it's not doing as anticipated.
Here is the code that I am testing with that sets the size of the userform based on the value of 'srvsnum', and then is supposed to centre the userform on the screen.

If srvsnum > 4 Then
        frmservice.Height = 479
        frmservice.Width = 713
    Else
        frmservice.Height = 288
        If srvsnum = 4 Then
            frmservice.Width = 713
        ElseIf srvsnum = 3 Then
            frmservice.Width = 540
        Else
            frmservice.Width = 366
        End If
    End If
    'Stop
    x = GetSystemMetrics(0)
    Y = GetSystemMetrics(1)
    'this sets to full screen
    frmservice.Width = 3 / 4 * x
    frmservice.Height = 3 / 4 * Y
    'frmservice.Top = Application.Top + (Application.UsableHeight / 2) - (frmservice.Height / 2)
    'frmservice.Left = Application.Left + (Application.UsableWidth / 2) - (frmservice.Width / 2)
        /code]

With Didi's suggestion, the height and width of the userform as I had defined was lost, and the resulting userform was much bigger than needed. Although the top left corner of the userform was on the monotor screen, the size of the userform put it off the screen.

The way I had been doing it, although not centring the userform vertically on the monitor (on my laptop only), it did maintain the right size and was horizontally centred.

Perhaps I misunderstood how to applu Didi's suggestion?

 

[diddi]

x = GetSystemMetrics(0)
Y = GetSystemMetrics(1)
If srvsnum > 4 Then
        frmservice.Height = 479
        frmservice.Width = 713
    Else
        frmservice.Height = 288
        If srvsnum = 4 Then
            frmservice.Width = 713
        ElseIf srvsnum = 3 Then
            frmservice.Width = 540
        Else
            frmservice.Width = 366
        End If
    End If
    
    frmservice.Top = (3 / 4 * Y - frmservice.Height)/2 ' this halves the remaining vertical space not covered by window
    frmservice.Left = (3 / 4 * X - frmservice.Width)/2 ' this halves the remaining horizontal space not covered by window

hope this gets you moving in the right direction