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 Windows.
For more info about this problem read this webpage for Excel for Windows
On a Mac we have a 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.
You can try the code below on a Mac to always open the userform in the center of your Excel window.
If correct it will also work if you have two monitors (please Let me know if this work correct)
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