MD said:
I would like my UserForm to be positioned at the lower left corner of the
screen no matter what resolution or screen size the user has.
Excel doesn't know about screen resolution or screen size. If you really
want to place your form down in the left corner, you can do it by making some
Windows API calls from VBA. First, you get the screen height in pixels.
Then you find out how many pixels per inch on your display. Then you convert
the height info to points (Excel understands points, not pixels.) Finally,
set the form's .Top property so that its bottom rests near the bottom of the
screen.
There's a good section on Windows API calls in the new book by Bullen, Bovey
& Green "Professional Excel Development." This code is based on theirs. If
you've never worked with the Windows API, this stuff looks very cryptic.
Here it is, shorn of Hungarian notation.
First, place these function declarations at the top of a code module:
Private Declare Function GetSystemMetrics Lib "user32" (ByVal nIndex As
Long) As Long
Private Declare Function GetDC Lib "user32" (ByVal hwnd As Long) As Long
Private Declare Function GetDeviceCaps Lib "gdi32" (ByVal hDC As Long, ByVal
nIndex As Long) As Long
Private Declare Function ReleaseDC Lib "user32" (ByVal hwnd As Long, ByVal
hDC As Long) As Long
Private Const POINTS_PER_INCH As Long = 72 ' a real world number
Private Const LOGPIXELSX = 88 ' tell GetDeviceCaps to return horiz
pixels/inch
Private Const SM_CYSCREEN = 1 ' tell GetSystemMetrics to return screen height
Then you'll need the following functions:
Public Function PointsPerPixel() As Double
Dim deviceContextHandle As Long
Dim DotsPerInch As Long
deviceContextHandle = GetDC(0)
DotsPerInch = GetDeviceCaps(deviceContextHandle, LOGPIXELSX)
PointsPerPixel = POINTS_PER_INCH / DotsPerInch
ReleaseDC 0, deviceContextHandle
End Function
Public Function ScreenHeightInPoints() As Long
Dim HeightInPixels As Long
HeightInPixels = GetSystemMetrics(SM_CYSCREEN)
ScreenHeightInPoints = HeightInPixels * PointsPerPixel()
End Function
' Finally, in the form's Activate handler, you can put the following
Me.Left = 5
Me.Top = ScreenHeightInPoints() - Me.Height - 25
' 5 should get you close enough to the left side, and
' the extra 25 leaves room for a single-row task bar