Userform Startup Position

Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: Userform Startup Position

  1. #1
    Board Regular
    Join Date
    Aug 2011
    Posts
    59
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Userform Startup Position

     
    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

  2. #2
    Board Regular MARK858's Avatar
    Join Date
    Nov 2010
    Location
    Southern England
    Posts
    8,969
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Test VBA on a copy of your data (remember you can't normally reverse the action)

    Please follow the forum Rules and Guidelines & please use Code tags around your code i.e. [CODE]your code[/CODE]

    To post a screenshot try one of these links
    MrExcel HTML Maker, Excel jeanie, RoryA addin (Win & Mac) or Borders-Copy-Paste

  3. #3
    Board Regular
    Join Date
    Aug 2011
    Posts
    59
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Userform Startup Position

    I had actually found these two pages as well, but it looks like it only adjusts the userform from the left side over. I would need the VBA to find the viewable area of the workbook and change the startup position of the userform accordingly so it shows the entire form and appears in the top right.

  4. #4
    Board Regular MARK858's Avatar
    Join Date
    Nov 2010
    Location
    Southern England
    Posts
    8,969
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Userform Startup Position

    For me something like below places it consistently in the same position (in the top right) in the application window no matter what size the window is.

    Code:
    Private Sub UserForm_Activate()
         
        Me.StartUpPosition = 0
        Me.Top = Application.Top + 25
        Me.Left = Application.Left + Application.Width - Me.Width - 25
         
    End Sub
    Test VBA on a copy of your data (remember you can't normally reverse the action)

    Please follow the forum Rules and Guidelines & please use Code tags around your code i.e. [CODE]your code[/CODE]

    To post a screenshot try one of these links
    MrExcel HTML Maker, Excel jeanie, RoryA addin (Win & Mac) or Borders-Copy-Paste

  5. #5
    Board Regular
    Join Date
    Aug 2011
    Posts
    59
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Userform Startup Position

    Looks like that did the trick! MARK858, you are both a gentleman and a scholar.

  6. #6
    New Member
    Join Date
    Mar 2015
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Userform Startup Position

    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.


    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.)

  7. #7
    New Member
    Join Date
    Feb 2016
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Userform Startup Position

    I realize this is an old post but I came across it today when I faced a similar problem. I wanted to put the userform in the middle of the workbook, as on my dual monitor workstation they were appearing on my primary screen even though the workbook was open on my secondary screen. I figured out how to place them in the center of the workbook, and from there it was a small leap to put them in the top right corner. The code for both solutions is below. Pick the one you need and paste it into the code for the offending userform.

    Code:
    'opens userform at the top right of the workbook
    Private Sub UserForm_Activate()
    Dim AppXPoint, AppYPoint As Long
    AppXPoint = Application.Left + (Application.Width - Me.Width)
    AppYPoint = Application.Top
    With Me
    .StartUpPosition = 0
    .Left = AppXPoint
    .Top = AppYPoint
    End With
    End Sub
    
    'opens userform in the center of the workbook
    Private Sub UserForm_Activate()
    Dim AppXCenter, AppYCenter As Long
    AppXCenter = Application.Left + (Application.Width / 2)
    AppYCenter = Application.Top + (Application.Height / 2)
    With Me
    .StartUpPosition = 0
    .Top = AppYCenter - (Me.Height / 2)
    .Left = AppXCenter - (Me.Width / 2)
    End With
    End Sub
    I have read many posts like this to learn how to overcome challenges, but this is the first time I have tried to answer a question. I hope this helps someone!

  8. #8
    New Member
    Join Date
    Mar 2016
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Userform Startup Position

    I had a problem with a userform opening in the center of screen (1) on a dual monitor setup. I had to place the positioning code in the initialize and activate subs for it to open on screen (2).
    Code:
    Private Sub UserForm_Activate()
    With UserForm1
    .StartupPosition = 0
    .Top = 0
    .Left = 0
    End With
    End Sub
    
    
    Private Sub UserForm_Initialize()
    With UserForm1
    .StartupPosition = 0
    .Top = 0
    .Left = 0
    End With
    End Sub

  9. #9
    Board Regular
    Join Date
    Jun 2016
    Location
    Florida, United States
    Posts
    365
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Userform Startup Position

    This is probably a really simplistic way of doing things, but it might help someone looking for a quick and easy bit of code. I used this to open my user form in the middle of the Excel screen when the button that it is linked to on the sheet is clicked.

    Code:
    Private Sub UserForm_Initialize()
        'Puts the user form in the middle of the Excel screen when opened.
        Me.Top = Application.Height / 2
        Me.Left = Application.Width / 2
    End Sub
    Hope this helps!
    Last edited by Veritan; Aug 16th, 2016 at 10:06 AM.

  10. #10
    New Member
    Join Date
    Dec 2016
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Userform Startup Position

      
    The last post positions the top left corner of the form in the center of the Excel screen. As not all forms are the same size, an improvement that places the centre of the dialog in the center of Excel would be as follows:

    Code:
    Private Sub UserForm_Initialize()
        'Puts the user form in the middle of the Excel screen when opened.
        Me.StartupPosition = 0
        Me.Top = (Application.Height / 2) - (Me.Height / 2)
        Me.Left = (Application.Width / 2) - (Me.Width / 2)
    End Sub
    Hope this helps!

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com