+ Reply to Thread
Results 1 to 4 of 4

Position of a UserForm

  1. #1
    MD
    Guest

    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



  2. #2
    Forum Expert dominicb's Avatar
    Join Date
    01-25-2005
    Location
    Lancashire, England
    MS-Off Ver
    MS Office 2000, 2003, 2007 & 2016 365
    Posts
    4,867

    Smile

    Good evening MD

    To be quite honest positioning a userform seems to be more trouble than it's worth, but if it's that important to you a trip to Mr Pearson's fine pages should put you on the right path.

    http://www.cpearson.com/excel/FormPosition.htm

    HTH

    DominicB

  3. #3
    Shawn O'Donnell
    Guest

    RE: 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



  4. #4
    Nick H
    Guest

    RE: 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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1