StartUp Position UserForm

A UserForm object has a property named StartUpPosition. By default, this property is set to 1 (CenterOwner), which means that it should appear in the center of Excel's window. In Excel for Windows this is working correct if you use one screen but not in Mac Excel. And if you use dual monitors this will not work correct in Excel for Windows and on a Mac.

On a Mac we have another problem because setting StartUpPosition to 1 will not work like in Windows
because it will center on your Mac and not in center of the Excel window, and also you have the same problem as in win Excel if you have more screens attached to your Mac.

Excel 2016 or higher

The code below will open a UserForm named UserForm1 and will work in Win and Mac Excel. The code for Mac and Windows are not the same for setting the top position of the UserForm because of a bug in Application.Top in Mac Office, see the two code blocks in the macro.

Sub OpenUserformWinMac()
'Ron de Bruin, 9-June-2020
'Open the userform in the center of the Excel screen, also if you have more screens attached to your Pc or Mac
'Note: because on a Mac the Application.Top value is not the same as in Windows we use two code blocks
#If Mac Then
With UserForm1 ' Name of the UserForm that you want to open
.StartUpPosition = 0
.Left = Application.Left + (0.5 * Application.Width) - (0.5 * .Width)
.Top = Application.UsableHeight - Application.Top - (0.5 * Application.Height) - (0.5 * .Height)
.Show
End With
#Else
With UserForm1 ' Name of the UserForm that you want to open
.StartUpPosition = 0
.Left = Application.Left + (0.5 * Application.Width) - (0.5 * .Width)
.Top = Application.Top + (0.5 * Application.Height) - (0.5 * .Height)
.Show
End With
#End If
End Sub
7-March-2021
Web design by Will Woodgate