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

 

[MARK858]

See if the 2 links below help


VBA Express : Excel - Fine-Tune Userform Start Position

VBA Express : Multiple Apps - Set User Form Position to (Top Left, Top Right, Lower Left, or Lower Right) of the Application

 

[JonElCanche]

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.

 

[MARK858]

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.

Private Sub UserForm_Activate()
     
    Me.StartUpPosition = 0
    Me.Top = Application.Top + 25
    Me.Left = Application.Left + Application.Width - Me.Width - 25
     
End Sub

 

[JonElCanche]

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

 

[JuliusUmulius]

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

 

[GrouchySmurf]

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.

'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!

 

[tmusto]

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

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

 

[Veritan]

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.

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!

 

[IcelandicWarrior]

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:

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!