How to maximize a window on a specific monitor with Excel VBA?

Question

I want to maximize a window on a specific screen using Excel VBA.

I used this code:

With ActiveWindow
    .WindowState = xlNormal
    .Left = 1200
    .WindowState = xlMaximized       
End With

The code works if monitor 2 is right-hand to monitor 1. If it's the other way around, the approach fails (then I have to use -1200).

This macro should work on different PCs, where I don't know how the systems are configured. Is there any possibility to detect how many monitors are attached and to directly address the respective monitor on which I want to maximize the window?

Answer 1

As mentioned in the comments you need to use Windows APIs; this is another (relatively simple to use) API that helped me determine if a user form was moved off the visible area of the screen(s): GetSystemMetrics Lib "User32"

to declare the function based on the version of Office:

#If Win64 Then  'Win64=true, Win32=true, Win16= false
    Private Declare PtrSafe Function apiGetSystemMetrics Lib "User32" Alias "GetSystemMetrics" (ByVal nIndex As Long) As Long
    Private Declare PtrSafe Function apiGetSystemMetrics32 Lib "User32" Alias "GetSystemMetrics" (ByVal nIndex As Long) As Long
#ElseIf Win32 Then  'Win32=true, Win16=false
    Private Declare Function apiGetSystemMetrics Lib "User32" Alias "GetSystemMetrics" (ByVal nIndex As Long) As Long
#Else   ' Win16=true
#End If

generic functions:

Public Function dllGetMonitors() As Long
    Const SM_CMONITORS = 80
    dllGetMonitors = apiGetSystemMetrics(SM_CMONITORS)
End Function


'The width of the virtual screen, in pixels.
'The virtual screen is the bounding rectangle of all display monitors

Public Function dllGetHorizontalResolution() As Long
    Const SM_CXVIRTUALSCREEN = 78
    dllGetHorizontalResolution = apiGetSystemMetrics(SM_CXVIRTUALSCREEN)
End Function

Public Function dllGetVerticalResolution() As Long
    Const SM_CYVIRTUALSCREEN = 79
    dllGetVerticalResolution = apiGetSystemMetrics(SM_CYVIRTUALSCREEN)
End Function

.

more info: http://msdn.microsoft.com/en-us/library/ms724385(VS.85).aspx

The function I used to determine if the form is off screen:

Private Sub checkOffScreen(ByRef frm)
    Dim maxTop As Long, minLeft As Long, maxLeft As Long
    Dim defaultOffset As Byte, monitors As Byte

    monitors = celTotalMonitors.Value
    defaultOffset = 11
    minLeft = 0 - (frm.Width - defaultOffset)
    If monitors = 1 And celScreenResolutionX.Value > 1280 Then
        maxTop = 1180 - defaultOffset
        maxLeft = 1900 - defaultOffset
    Else
        maxTop = 750 - defaultOffset
        maxLeft = (960 * monitors) - defaultOffset
    End If
    With frm
        'If (celFormTop.Value < 0 Or celFormTop.Value > maxTop) Or _
            (celFormLeft.Value < minLeft Or celFormLeft.Value > maxLeft) Then
        'If .top < 0 Or .top > maxTop Or .Left < minLeft Or .Left > maxLeft Then
        If celFormTop.Value > maxTop Or celFormLeft.Value > maxLeft Then
            celFormTop = defaultOffset
            celFormLeft = defaultOffset
        End If
        If .Top > maxTop Or .left > maxLeft Then
            .Top = defaultOffset
            .left = defaultOffset
        End If
    End With
End Sub