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
You do not have the required permissions to view the files attached to this post.
Have a great day!
![Very Happy :D](./images/smilies/icon_e_biggrin.gif)
Sisyphus
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)