Position userform on the same monitor excel is on

Question

Good afternoon,

I am creating a Dashboard in which when running Excel a userform is opened, maximized and Excel is hidden. My code works perfectly for a PC with only one monitor.

When using a PC with two monitors the VBA opens the userform on the main monitor, but to maximize it takes the information from the monitor on which Excel was open.

Someone knows how to make the VBA identify on which monitor Excel is open and open the userform on that monitor?

'Set the Windows style so that the userform has a minimise and maximise button
lngCurrentStyle = GetWindowLong(lngHWnd, GWL_STYLE)
lngNewStyle = lngCurrentStyle Or WS_MINIMIZEBOX 'Or WS_MAXIMIZEBOX
lngNewStyle = lngNewStyle And Not WS_VISIBLE And Not WS_POPUP 'And WS_MINIMIZEBOX
SetWindowLong lngHWnd, GWL_STYLE, lngNewStyle

'Set the extended style to provide a taskbar icon
lngCurrentStyle = GetWindowLong(lngHWnd, GWL_EXSTYLE)
lngNewStyle = lngCurrentStyle Or WS_EX_APPWINDOW
SetWindowLong lngHWnd, GWL_EXSTYLE, lngNewStyle
ShowWindow lngHWnd, SW_SHOW

'Remove a barra superior
HideTitleBar Me

'Maximiza o formulário
With Me
    .StartUpPosition = 1
    .Width = Application.Width
    .Height = Application.Height
    .Left = 0
    .Top = 0
End With

NOTE: I will not post the complete code here because it is not a very complex code, however, long.

Thank you all.

Answer 1

Code

You can use the following code in the desired Userform:

Private Sub UserForm_Initialize()
    With Application
        Me.StartUpPosition = 0
        Application.WindowState = xlMaximized
        Me.Top = .Top
        Me.Left = .Left
        Me.Height = .Height
        Me.Width = .Width
    End With
End Sub

Explanation

First, choose the starting position as manual with Startupposition

Then maximize Excel with Application.WindowState = xlMaximized, because when switching between different monitor resolutions, Excel is not maximized and the form size is not appropriate for that monitor.

And then initialize the form with the same Excel proportions:

Me.Top = .Top
Me.Left = .Left
Me.Height = .Height
Me.Width = .Width