Microsoft Excel

Ron de Bruin
Excel Automation

Microsoft MVP Program

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. but if you use dual monitors this will not work correct in Excel for Windows and on a Mac also not if you use one screen.

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 and 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.

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

 

Excel 2011

You can try the code below on a Mac in Excel 2011 to always open the userform in the center of your Excel window. Note: The code below will not work in 2016 and higher because the Macscript function is not working anymore in a lot of situations because of Apple Sandbox requirements.

The macro OpenUserform below will open a userform named "UserForm1"

Note: The macro also use the function named GetExcelPositionAndSize, copy both the macro
and the function in a normal module of your workbook.

Sub OpenUserform()
    Dim GetInfo As Variant
    GetInfo = GetExcelPositionAndSize(ActiveWorkbook.Name)
    If IsArray(GetInfo) Then
        With UserForm1    ' Name of the UserForm that you want to open
            .StartUpPosition = 0
            .Left = GetInfo(0) + (0.5 * (GetInfo(2) - GetInfo(0))) - (0.5 * .Width)
            .Top = GetInfo(1) + (0.5 * (GetInfo(3) - GetInfo(1))) - (0.5 * .Height)
            .Show
        End With
    Else
        MsgBox GetInfo & " [No result - Is your window name correct?]"
    End If
End Sub

Function GetExcelPositionAndSize(ByVal WinName As String) As Variant
'Original Function created by BlueCactus, see :
'http://www.vbaexpress.com/kb/getarticle.php?kb_id=452
'Returns the Left, Top coordinates, as well as Width, Height for a Excel window on a  Mac.
'Changed by Ron de Bruin on 18-Oct-2012

    Dim ScriptToRun As String, ScriptResult As String, MySplit As Variant

    ScriptToRun = "tell application """ & Application.Name & """" & Chr(13)
    ScriptToRun = ScriptToRun + "try" & Chr(13)
    ScriptToRun = ScriptToRun + "set rect to bounds of window """ & WinName & """" & Chr(13)
    ScriptToRun = ScriptToRun + "on error" & Chr(13)
    ScriptToRun = ScriptToRun + "set rect to ""error""" & Chr(13)
    ScriptToRun = ScriptToRun + "end try" & Chr(13)
    ScriptToRun = ScriptToRun + "end tell" & Chr(13)
    ScriptToRun = ScriptToRun + "return rect"

    ' ScriptResult will be set to "error" if the window name is bad.
    ScriptResult = MacScript(ScriptToRun)

    If ScriptResult <> "error" Then
        GetExcelPositionAndSize = Split(ScriptResult, Chr(44))
    Else
        GetExcelPositionAndSize = "error"
    End If
End Function