Userform Startup Position

[JonElCanche]

I need the userform I made to display in the top right corner of the worksheet. I can manually set the position but when the workbook is opened on a different computer with a different screen resolution the userform is almost completely off the screen.
What VBA code could have the userform position itself so that the whole thing is showing but in the top right of the screen?
Thanks for the help

 

[cmcintyre]

Hi there, is it possible to adjust this to have the form position itself based on a cell reference? So maybe the top corner of the form always locates itself in say D9. Apologies if this is an amateurish question...

 

[Bret Sutton]

This thread might just be the answer to your problem...

https://www.mrexcel.com/forum/excel-questions/69527-getting-cells-position-points.html

 

[cmcintyre]

Many thanks!

 

[awsmitty]

Very kewl. I use a lot of dynamic forms. Your code can be used there too. Very kewl!

 

[Lummo]

A different approach to this problem is to save the position of the userform when it is closed and load that position once the userform is opened again.
With this solution the user can position the userform wherever they want, and expect it to appear at that exact position when opened again.
It solves every problem related to screen sizes, frozen views an do on. One caveat however might be if positioned far right on a big screen, saved and then opened again on a small screen. I haven't tested it, but it could of course be solved with some additional code.

Private Sub UserForm_Activate()    
    Me.Top = [UF_TOP_OFFSET]
    Me.Left = [UF_LEFT_OFFSET]
    
End Sub


Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    
    If Not [UF_TOP_OFFSET] = Me.Top Or Not [UF_LEFT_OFFSET] = Me.Left Then


        [UF_TOP_OFFSET] = Me.Top
        [UF_LEFT_OFFSET] = Me.Left
        
    End If
    
End Sub

(Variables in brackets "[variable]" point to named ranges, in case you havn't seen this before.)

I thought that this was a great idea but couldn't get the QueryClose() to work. It gave me "Run-time error '424' Object required" with Excel 2007. I replaced the code with:

    If Not [LedgerFormTop] = Me.Top Or Not [LedgerFormLeft] = Me.Left Then
        ActiveWorkbook.Names("LedgerFormLeft").RefersToR1C1 = "=" & Me.Left
        ActiveWorkbook.Names("LedgerFormTop").RefersToR1C1 = "=" & Me.Top
    End If

 

[donwiss]

A different approach to this problem is to save the position of the userform when it is closed and load that position once the userform is opened again.
With this solution the user can position the userform wherever they want, and expect it to appear at that exact position when opened again.
It solves every problem related to screen sizes, frozen views an do on. One caveat however might be if positioned far right on a big screen, saved and then opened again on a small screen. I haven't tested it, but it could of course be solved with some additional code.

Better than saving in the spreadsheet, is saving in the User's Registry. This also solves the problem of the user not saving the WorkBook. I had a Microsoft link to the macros I'm using, but the link has gone dead. And the Internet Archive is not currently working. My Dead Microsoft Link. Possibly this replaces it? winreg.h header.

Then I put these macros around in my code:

Sub LocateForm(MyForm As Object)
' place in form's Initialization (or Activate) macro. argument to this is Me
    Dim L, T As String
    L = QueryKey("Software\BondCalc\Settings", MyForm.Name & "Left")
    T = QueryKey("Software\BondCalc\Settings", MyForm.Name & "Top")
    If L = "" Or T = "" Then Exit Sub
    MyForm.StartUpPosition = 0
    MyForm.Left = L
    MyForm.Top = T
End Sub

Sub SaveFormLocation(MyForm As Object)
' place before closing form. argument to this is Me
    SaveRegKeyValue MyForm.Name & "Left", MyForm.Left
    SaveRegKeyValue MyForm.Name & "Top", MyForm.Top
End Sub

 

[donwiss]

After the previous post I kept improving the macros. And I realized that people with laptops could be switching monitors. So I took care of that:

Sub LocateFormOnScreen(MyForm As Object, Optional ByVal OptStr As String)
' place in form's Initialize (or Activate) macro. argument to this is Me
' if you are Hiding the form, then this needs to be in an Activate macro
' use optional argument when form has multiple personalities
    Dim MaxPoints As Single
    Dim L As String, T As String
    With MyForm
        L = QueryKey("Software\BondCalc\Settings", .Name & OptStr & "Left")
        T = QueryKey("Software\BondCalc\Settings", .Name & OptStr & "Top")
        If L = "" Or T = "" Then Exit Sub
' check that form will be on screen
        MaxPoints = 0.75 * GetSystemMetrics32(0)
        If L + MyForm.Width > MaxPoints Then L = MaxPoints - MyForm.Width
        MaxPoints = 0.75 * GetSystemMetrics32(1)
        If T + MyForm.Height > MaxPoints Then T = MaxPoints - MyForm.Height
' position
        .StartUpPosition = 0
        .Left = L
        .Top = T
    End With
End Sub

Sub SaveFormLocation(MyForm As Object, Optional ByVal OptStr As String)
' place before Unloading or Hiding form. argument to this is Me
' or place in a UserForm_Terminate macro to also capture clicking on the X in the upper right
' use optional argument when form has multiple personalities
    SaveRegKeyValue MyForm.Name & OptStr & "Left", MyForm.Left
    SaveRegKeyValue MyForm.Name & OptStr & "Top", MyForm.Top
End Sub

' https://www.exceltip.com/general-topics-in-vba/determine-the-screen-size-using-vba-in-microsoft-excel.html
' for 64-bit put PtrSafe between Declare and Function
Declare Function GetSystemMetrics32 Lib "User32" Alias "GetSystemMetrics" (ByVal nIndex As Long) As Long

It isn't perfect. The height is the full screen height, so the form ends up partly under the taskbar.

And I'd rather not have something that is 32-bit/64-bit specific, but in my case it is not a big deal, as my entire application is dependent on the bit of an Active X server.