Userform Position | Mac Excel Automation

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

Bug when you use two screens attached to your Mac

If you have Excel open on your second screen and open a UserForm with a combobox you will notice that the combobox will open on the right side of your first screen if you click on the down arrow. This is a known problem but because of other high priority things in Mac Office we must not think this will be fixed soon.