New Excel Forum

This forum has been moved to

Ask all future questions in the New Excel Forum.


Positioning a UserForm on your screen

Free Excel Macros

Positioning a UserForm on your screen

Postby Sisyphus » Sat Jan 04, 2014 4:50 am

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
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
You do not have the required permissions to view the files attached to this post.
Have a great day! :D

I do this for "honour and country" - much less of the latter, actually.
If I helped you, award points, plenty of them.
If I bored you, deduct points for being too long-winded. (I know, :lol)
Former Moderator
Posts: 4454
Joined: Dec 7, 2011
Location: Shanghai
Reputation: 203
Excel Version: 2010

Return to Excel Macros

Who is online

Users browsing this forum: No registered users and 5 guests