Center VBA userform and set scroll bars

When you open forms, it is nice that they are always centered within the application window. For example, if you open Excel on a second screen and you open the VBA editor on screen 1, it is possible that a form is displayed somewhere halfway between these 2 screens. It is also nice that forms automatically get scroll bar(s) if the form is larger than the application window. You can enable the scroll bar(s) for forms by default, but that does not look nice if the screen is large enough. In that case the scroll bar(s) will be grayed out on the form and that is very ugly.

The code below ensures that forms are always automatically displayed centered in the application window and horizontal and / or vertical scroll bars are also automatically added when needed.

Public Sub CenterFormAndSetScrollBars(frm As Object)
    With frm
        .StartUpPosition = 0
        .ScrollBars = fmScrollBarsNone
        If .Width < Application.Width Then
            .Left = Application.Left + 0.5 * Application.Width - 0.5 * .Width
        Else
            .ScrollBars = fmScrollBarsHorizontal
            If .ScrollWidth = 0 Then
                .ScrollWidth = .Width
                .ScrollLeft = 0
            End If
            .Left = Application.Left
            .Width = Application.Width
        End If
        If .Height < Application.Height Then
            .Top = Application.Top + 0.5 * Application.Height - 0.5 * .Height
        Else
            If .ScrollBars = fmScrollBarsHorizontal Then
                .ScrollBars = fmScrollBarsBoth
            Else
                .ScrollBars = fmScrollBarsVertical
            End If
            .ScrollHeight = .Height
            .ScrollTop = 0
            .Top = Application.Top
            .Height = Application.Height
        End If
    End With
End Sub

This code can be called in the form at the UserForm_Initialize or UserForm_Activate event:

Private Sub UserForm_Initialize()
    CenterFormAndSetScrollBars Me
End Sub

If when opening or activating a form dynamically the size of the form is adjusted, CenterFormAndSetScrollBars must be called after this adjustment.

So not:

Private Sub UserForm_Initialize()
    CenterFormAndSetScrollBars Me
    Me.Width = 100
End Sub

But:

Private Sub UserForm_Initialize()
    Me.Width = 100
    CenterFormAndSetScrollBars Me
End Sub

The code also ensures that the scroll bars are always positioned in the home position. So all the way to the left and all the way at the top. However, it can still happen that the scroll bars are positioned differently when opening the form. That's because after UserForm_Initialize event, it is always ensured that the control that currently has the focus is always in view. So if that is, for example, a button that is normally out of view, then the scroll bars are automatically set so that this button is in view. You can prevent this behavior by using the UserForm_Activate event.

Questions / suggestions

Hopefully this article has helped you automatically center and set scroll bars of VBA forms. If you have any questions about this topic or suggestions for improvement, please post a comment below.