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.