Position of a UserForm
 

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.

Can anyone help me ?

Regards,

MD

Position of a UserForm
 

"MD" wrote:
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

Position of a UserForm
 

Many thanks Shawn,

I found your post useful because it was the only reference I could find on
how to convert points to pixels.

Having found this, and consequently solved my problem, someone in this group
may be able to tell me why the problem exists in the first place...

I have a user form with 5 CheckBoxes arranged in a column. The top CheckBox
is an 'All' option called cbAll. The idea is that when a user clicks on cbAll
all the other CheckBoxes are set to the same value as cbAll. The other
CheckBoxes have 'Click' events that when set to False will also set cbAll to
False and when set to True will check the status of the CheckBoxes and set
cbAll to True if they all happen to be True.

Nice and straightforward methinks until I discover that the '_Click' event
behaves like a '_Change' event. Even though the CheckBoxes were not clicked
by the user the '_Click' events were running when the values were changed by
the cbAll code.

I now have a perfectly working 'work-around' but feel a bit miffed at having
to spend two days getting there.

I'll be happy to post my solution if anyone is interested.

Kind regards,
Nick.