Userform position on dual monitors

[ebeauch28]

Hi,

I use a macro to display a user form. This form is displayed properly on a single screen, but incorrectly on dual screens. Even though the application is at full screen on the primary monitor, the userform pops up on the secondary monitor.

This behavior does not seem to occur with msgbox popups.

I've tried setting the StartUpPosition property to CenterOwner or CenterScreen, but it does not make any difference.

Would anyone have sample code that can be used to properly calculate the position so that the form is displayed at the center of the screen where the application is running?

Note: this behavior occurs in Word, but I assume it's the same in Excel. (posting here because this Excel forum is so good for these VB questions).

Thanks
Erik

 

[hatman]

Welcome to the board!

I assume that you have your multiple screen set up so the desktop spans both monitors? It's been a long time since I had that set-up, so I am really reaching into the shadowy depths here.

I would investigate the use of the GetSystemMetrics API call to get the screen width and height. My thouht here is that the screen width is generally in the magnitude of 1.3 times the height... if your screen width to height is more like 2 or greater, you probably have a dual monitor setup, therefore the startup position should be at screen-width/4-formwidth/2 from the lft edge, converting from pixels to twips as needed...

 

[hatman]

It's possible it may not be quite as touch as I thought at first... this is an excerpt from MSDN.Microsoft.com:

You can specify where a form is to be displayed on the computer screen by entering values in the Location property. This specifies the position, in pixels, of the top-left corner of the form. Also, you need to set the StartPosition property to indicate the boundaries of the display area.

Note Keep in mind that screen size and resolution often vary depending on the user's system. Additionally, systems that have multiple monitors attached may have trouble recognizing the boundaries of the display area. These two situations will often cause a form's location to change unpredictably, despite the Location property setting.
For this reason, the default setting for the StartPosition property for a Windows Application is "WindowsDefaultLocation", which tells the operating system to compute the best location for the form at startup, based on the current hardware. Another alternative is to set the StartPosition property to Center and then change the location of the form in code. See "To position forms programmatically" below for more information.

 

[hatman]

BUT, after I went through the trouble of figuring it out the hard way, here is what I finally came up with... I believe it to be fairly bullet-proof... one of the REAL gurus here will probably tell me I did it the hard way, but none of them have answerred, so I'm doing my best:

Private Declare Function GetSystemMetrics Lib "user32" (ByVal nIndex As Long) As Long

Private Declare Function GetDeviceCaps Lib "gdi32" (ByVal hDC As Long, ByVal nIndex As Long) As Long
  
Private Declare Function GetDC Lib "user32" (ByVal hwnd As Long) As Long
  
Private Declare Function ReleaseDC Lib "user32" (ByVal hwnd As Long, ByVal hDC As Long) As Long

Private Const LOGPIXELSX As Long = 88
Private Const LOGPIXELSY As Long = 90
Private Const PointsPerInch = 72

Private Const sm_cxscreen As Long = 0
Private Const sm_cyscreen As Long = 1

Public Enum SystemMetricsConstants

    smScreenWidth = sm_cxscreen
    smScreenHeight = sm_cyscreen
    
End Enum



Public Function SystemMetrics(ByVal uindex As SystemMetricsConstants) As Long

    SystemMetrics = GetSystemMetrics(uindex)

End Function

Public Function PointsPerPixelX() As Double

    Dim hDC As Long
    Dim lDotsPerInch As Long
    
    hDC = GetDC(0)
    
    lDotsPerInch = GetDeviceCaps(hDC, LOGPIXELSX)
    
    PointsPerPixelX = PointsPerInch / lDotsPerInch
    
    ReleaseDC 0, hDC

End Function

Public Function PointsPerPixely() As Double

    Dim hDC As Long
    Dim lDotsPerInch As Long
    
    hDC = GetDC(0)
    
    lDotsPerInch = GetDeviceCaps(hDC, LOGPIXELSX)
    
    PointsPerPixely = PointsPerInch / lDotsPerInch
    
    ReleaseDC 0, hDC

End Function



Sub Display_Center()

    xheight = SystemMetrics(smScreenHeight)
    xwidth = SystemMetrics(smScreenWidth)
    
    With UserForm1
    
        If xwidth / xheight > 1.5 Then
        
            .Left = xwidth * PointsPerPixelX / 4 - .Width / 2
            
        Else
        
            .Left = xwidth * PointsPerPixelX / 2 - .Width / 2
                        
        End If

        .Top = xheight * PointsPerPixely / 2 - .Height / 2
        
        .Show
    
    End With

End Sub

 

[Unperterbed]

Sorry to dig up an ancient post, but I got here by googling the same problem.

I found a more elegant solution that's working great for me, so I wanted to close the loop on this.

I posted this right below my code to show the userform, and it forces the userform to open in the center of the active excel window.

(props to Spreadsheetpage.com)

With UserForm1
.StartUpPosition = 0
.Left = Application.Left + (0.5 * Application.Width) - (0.5 * .Width)
.Top = Application.Top + (0.5 * Application.Height) - (0.5 * .Height)
.Show
End With

Cheers!

 

[mcorbet]

Sorry to dig up an ancient post, but I got here by googling the same problem.

I found a more elegant solution that's working great for me, so I wanted to close the loop on this.

I posted this right below my code to show the userform, and it forces the userform to open in the center of the active excel window.

(props to Spreadsheetpage.com)

With UserForm1
.StartUpPosition = 0
.Left = Application.Left + (0.5 * Application.Width) - (0.5 * .Width)
.Top = Application.Top + (0.5 * Application.Height) - (0.5 * .Height)
.Show
End With

Cheers!

Thanks alot. This was the solution I was looking for.

 

[SantanaKRE8s]

Hello, I used this code you shared to center the userform but it loops a few times and also the first time it appears it appears on the right side goes away and comes back in the center
Can some one help?

 

[SantanaKRE8s]

 

[GAZZAT5]

Sorry to reanimate this old post, i was going crazy trying to come up with a complex solution but this code from @Unperterbed

Sub showform()
  With UserForm1 'replace with your form name
    .StartUpPosition = 0
    .Left = Application.Left + (0.5 * Application.Width) - (0.5 * .Width)
    .Top = Application.Top + (0.5 * Application.Height) - (0.5 * .Height)
    .Show
  End With
End sub

works well. The first time i used it the form appeared top left of the current screen but every other time even after closing excel it works perfectly.

Change the form name to match your form and you're good to go.

@SantanaKRE8s (i know this was a long time ago) but you'll have to post your actual code for anyone to be able to help you