Positioning a UserForm on your screen

[Sisyphus]

For some reason the StartUpPosition property of the UserForm object doesn't seem to function as advertised with multiple monitors. Excel often insists on showing the form on a monitor other than the worksheet. This little procedure will ensure that it is shown on the same screen, and it will do more.

Code: Select all

Private Sub SetFormPosition(Frm As Object, _
                            ByVal Top As Double, _
                            ByVal Left As Double)
    '' 0087.01       Created 04 Jan 2014

    ' Top and Left define the position of Frm's top left corner
    ' as a percentage of the total size of the application's window.
    '     0 / 0 places the form in the top left,
    '   100 / 100 in the bottom right corner,
    '    50 / 50 in the screen's center
   
    Frm.StartUpPosition = 0
    With Application
        Frm.Top = (.Height - Frm.Height) * Top / 100 + .Top
        Frm.Left = (.Width - Frm.Width) * Left / 100 + .Left
    End With
End Sub

Whereas the StartUpPosition property seeks to position your form in the center of the screen, the above code allows you to place it anywhere you like, meaning in such a way as not to cover the part of the screen that you may need to refer to while filling the form. The two paramters, Top and Left can be defined statically or dynamically.This capability makes the sub suitable to handle any number of UserForms that your project might have. When deploying the procedure for use with multiple forms you may need to declare it as Public.

The attached workbook demonstrates the use. You will find the function call in the procedure SourceCode.GetDate.

Code: Select all

    Set frmDatePicker = New DatePicker
    SetFormPosition frmDatePicker, Top, Left         ' THIS IS THE FUNCTION CALL
    With frmDatePicker
        .Value = CalVal
        .Show

In this case the UserForm object is 'frmDatePicker'. Observe that the call is placed after the object was created and before it is shown.

The parameter variables Top and Left originate from the procedure Test_Code.TestCalendar which reads them from the screen where you may enter them.

Code: Select all

    NextDate = GetDate(NextDate, Cells(3, 2).Value, Cells(4, 2).Value)

This function call passes them to GetDate where they are optional.

Code: Select all

Function GetDate(Optional Prepick As Variant = 0, _
                 Optional Top As Double = 50, _
                 Optional Left As Double = 50) As Date

That means that Test_Code.TestCalendar might call the GetDate function without parameters, thus,

Code: Select all

NextDate = GetDate(NextDate)
' or even
NextDate = GetDate

In the first instance the default values for Top = 50 and Left = 50 would position the form in the screen center, in the second the calendar would default to today's date as initial value in place of next week's date the first call would display. While this construct demonstrates the flexiblity the above procedure offers when combined with Optional Top and Left in an intermediate procedure the variable default date is merely incidental to this demonstration.

Get the latest version of the calendar UserForm used in this demonstration from this link,

Code: Select all

http://www.excelkey.com/forum/viewtopic.php?f=5&t=4841