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.
Private Sub UserForm_Initialize() CenterFormAndSetScrollBars Me Me.Width = 100 End Sub
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.