UserForms with multiple monitors

BillV · Sep 6th 2005

I don't delve into VBA very often and have only just discovered this forum so what follows may reveal that I'm a bit rusty. Please bear with me.

My system has two monitors. When I bring up Excel with the window maximized it will always come up on the monitor that it was last on. One of my spreadsheets runs a macro that displays a UserForm. I want this form to appear on the spreadsheet, i.e. on whichever monitor that displays the spreadsheet. However the UserForm always appears on the primary monitor, never the secondary monitor even if the spreadsheet comes up there. I can't find any way to detect which monitor that the spreadsheet is on or reposition the Userform to the desired monitor. The Left property only positions it on the primary monitor.

Can anyone provide some information on how to deal with these issues, both the UserForm position matter and how Excel and/or Windows knows which monitor to use? I'm running 98SE and Excel 2000 if that matters.

BillV · Sep 6th 2005

Re: UserForms with multiple monitors

P.S.

I'd expected that the GetSystemMetrics API function would have information on the number of monitors and their relative positions but the argument values for which I have information (0 through 44) do not provide this.

Insomniac · Sep 7th 2005

Re: UserForms with multiple monitors

I normally just use Excels Top & Left property to do it.
[vba]Private Sub UserForm_Activate()
'Position top/left of Excel App
Me.Top = Application.Top
Me.Left = Application.Left

'Approx over top/left cell (depends on toolbars visible)
Me.Top = Application.Top + 110
Me.Left = Application.Left + 25
End Sub[/vba]

**iwrk4dedpr** · Sep 7th 2005

Re: UserForms with multiple monitors

BillV,

I've got the same problem here at work. I don't think that Excel was written with dual monitor support in mind. By that I mean that when the workbook is maximized it will only maximize onto 1 screen. You normalize the size and stretch it across 2 monitors though.

I've never had much luck in controlling the forms though on my dual monitor setup. I always seem to get the form showing up on the monitor that doesn't have the workbook on it. I've learned to live with it.

The center properties on the show position seem to mean center of the total screen area????

I think that the only other alternative would be to write some code that would set the top and left positions so that the centers of each match. Then when displaying use the manual method and then on initialize set the form to the center.

Something like the following in the forms initialize routine:

Code

Me.Top = (Application.UsableHeight / 2) - (Me.Height / 2)
    Me.Left = (Application.UsableWidth / 2) - (Me.Width / 2)

BillV · Sep 7th 2005

Re: UserForms with multiple monitors

I just tried Insomniac's solution and it works fine. Thank's very much. My secondary monitor is to the left of the primary and I'd previously tried entering a negative value without any luck. Possibly Application.Left is a suitably large positive value that is wrapped around on setups like mine. In some quick snooping I note that Me.Left is Single while Application.Left is Double which also makes me wonder if it contains something special.

With regard to Iwrk4dedpr's observations, I've always assumed that this was some sort of safe default issue, i.e. Maximizing the window makes it fill one monitor rather than possibly being spread over a larger area on more than one. The term works well on a single monitor system but causes confusion on a multiple monitor setup.

In any event it works!

lacklogic · Oct 29th 2011

Re: UserForms with multiple monitors

This also works... it is the combination of the two suggestions shown above:

Code

Private Sub UserForm_Initialize()
    
    Dim TopOffset As Integer
    Dim LeftOffset As Integer
    
    TopOffset = (Application.UsableHeight / 2) - (Me.Height / 2)
    LeftOffset = (Application.UsableWidth / 2) - (Me.Width / 2)
    
    Me.Top = Application.Top + TopOffset
    Me.Left = Application.Left + LeftOffset
       
End Sub

Display More

BillV · Oct 31st 2011

Re: UserForms with multiple monitors

Well I still run Excel 2000 and that macro on the same 98SE system (with different motherboard and processor) as I have an investment in software (money and time) that I've always resisted abandoning.

BUT!

Lacklogic, did you look at how old this thread is? After ==>SIX YEARS<== I've forgotten all the details. My recollection is that the macro didn't work perfectly but you had to push it a bit to find any flaws and I don't remember them being associated with multiple monitors (but I've forgotten). It basically runs fine and I've put it out of my mind.

Thanks anyway.

lacklogic · Oct 31st 2011

Re: UserForms with multiple monitors

Quote from BillV;582640

Lacklogic, did you look at how old this thread is? After ==>SIX YEARS<== I've forgotten all the details. My recollection is that the macro didn't work perfectly but you had to push it a bit to find any flaws and I don't remember them being associated with multiple monitors (but I've forgotten). It basically runs fine and I've put it out of my mind.

Thanks anyway.

Bill, maybe I don't fully understand your intention with your reply to my recent post, but I actually stumbled across this 6 YEAR OLD post while seeking some related answers for a client of mine. The theory is... this stuff is on the internet because other people read it. I was not answering this question for your specific benefit, BillV. I was simply contributing to the information here, as others seeking answers (as I was last night) will find these 6 YEAR OLD POSTS, and take from it whatever benefit they can. I didn't go browsing through six years of old posts to arbitrarily come across this question and decide "maybe Bill could use some additional ideas on this question he has" This thread just happened to be one of the first results on the top of a keyword search that I did on Google, so I figured that if I found it, others may too.

As a member of the OZGRID community already, I just put in my two cents. If you do have any actual suggestions on how to improve this code, or maybe insight as to why this still may not be the best solution, I encourage you to amend my recent contribution with your expertise... otherwise, I guess I am still struggling to understand the point you are making with your reply. P.S. I did see that this thread originated in 2005. Thanks.

**Reafidy** · Oct 31st 2011

Re: UserForms with multiple monitors

Thanks lackllogic,

As you mentioned, it doesn't matter how old the thread is, new solutions are always welcome. Software and hardware change over time as so do solutions, plus new solutions can be discovered. This thread will show up in many searches and could possibly provide help to thousands of people...

iMadejoolaff · Jun 20th 2013

Re: UserForms with multiple monitors

9 years later and still relevant. Thanks LackLogic!

milotman · Aug 13th 2014

Re: UserForms with multiple monitors

Found that this works form most situations. Some minor manipulation can allow you to have your userform start anywhere you want.

Code

If Application.Left > 1000 Then
    With Me
        .Top = (Application.UsableHeight / 2) + (Me.Height / 2)
        .Left = (1.5 * (Application.UsableWidth)) - (Me.Width / 2)
        .StartUpPosition = 3
    End With
Else
    With Me
        .Top = (Application.UsableHeight / 2) + (Me.Height / 2)
        .Left = (Application.UsableWidth / 2) - (Me.Width / 2)
        .StartUpPosition = 3
    End With
End If

Display More

schickens · Mar 4th 2015

Re: UserForms with multiple monitors

^ think you mean 8 years. But either way, 10 years later and this is still very relevant, even on Excel 2010!! Thank you lacklogic!!

SamVan · Mar 29th 2016

Re: UserForms with multiple monitors

Still relevant!

I have some additional information (using Excel 2013).
The .Left property doesn't work for the second monitor when called in UserForm_Initialize(), however it does work when called in UserForm_Activate().

Code

Private Sub UserForm_Activate()
    With Application
        Me.Top = (.UsableHeight / 2) + (.Top) - (Me.Height / 2)
        Me.Left = (.UsableWidth / 2) + (.Left) - (Me.Width / 2)
    End With
End Sub

Hyrumdrums · Aug 9th 2017

Re: UserForms with multiple monitors

I'm Running a Dashboard system in Excel on the production floor at work. Multiple monitors has been a tricky situation for me as well. Each production line has a monitor and an extended display with a userform on it.

Like BillV said, the form shows on whichever screen last had excel.

After lots of investigation and experimentation, I've come up with the following. It's not perfect, but it's a working start.

Components included:
1 x Userform
3 x Command Buttons
1 x Custom Class

UserForm Code: 1 sub for each button click, and form.Initialize

Code

Option Explicit
 
Public ED As ExtendedDisplay    'Object to handle positionin on screen
 
 '===============================================================
'Move to primary screen
'===============================================================
Private Sub cmdCenterPrimary_Click()
    
    ED.CenterFormOnPrimaryScreen
    
End Sub




'===============================================================
'Move to secondary screen
'===============================================================
Private Sub cmdCenterSecondary_Click()
    
    ED.CenterFormOnSecondaryScreen
    
End Sub




'===============================================================
'Try moving form to center of whichever screen you're not on.
'===============================================================
Private Sub cmdSwap_Click()
    
    ED.SwapScreens
    
End Sub




'===============================================================
'Create new extended display object, pair this form
'===============================================================
Private Sub UserForm_Initialize()
     
    Set ED = New ExtendedDisplay   'Your custom class
    
    Set ED.frmPairedForm = Me       'Pair this form to pairedForm Property
    
End Sub

Display More

Custom Class Code:
Sub Setup: Figure out display count(1 or 2) and which screen the userform is on
Sub CenterFormOnPrimaryScreen: Obvious
Sub CenterFormOnSecondaryScreen: Obvious
Sub SwapScreens: Uses property blnMainScreen to call either of the above two subs to move form to the other screen.

Sub PointsPerPixel: pulled from http://www.ozgrid.com/forum/showthread.php?t=198442

Code

'Function to get screen resolution
Private Declare Function GetSystemMetrics32 Lib "user32" Alias "GetSystemMetrics" (ByVal nIndex As Long) As Long
 '======================================================================================
 '======================================================================================
 '==============This object is used to return positioning information on a sytem with
 '==============one extended display. My primary screen is the left one, I have not
 '==============tried it with another set up.
 '======================================================================================
 '======================================================================================
 'Notes
 
    'x = 0   'This is width of primary display
    'x = 1   'This is height of primary display
    'x = 78  'Total width of both displays
    'ValueYouWant = GetSystemMetrics(x)




'======================================================================================
'This portion found at http://www.ozgrid.com/forum/showthread.php?t=198442
'======================================================================================
 'Functions to get DPI
Private Declare Function GetDC Lib "user32" (ByVal hwnd As Long) As Long
Private Declare Function GetDeviceCaps Lib "gdi32" (ByVal hDC As Long, ByVal nIndex As Long) As Long
Private Declare Function ReleaseDC Lib "user32" (ByVal hwnd As Long, ByVal hDC As Long) As Long
Private Const LOGPIXELSX = 88 'Pixels/inch in X
Private Const POINTS_PER_INCH As Long = 72 'A point is defined as 1/72 inches
 
Private blnMainScreen       As Boolean
Private blnMultipleScreens  As Boolean
Private blnSetUp            As Boolean  'True after being set up
Public frmPairedForm        As Object








 'Return DPI
Private Function PointsPerPixel() As Double




    Dim hDC As Long
    Dim lDotsPerInch As Long
     
    hDC = GetDC(0)
    lDotsPerInch = GetDeviceCaps(hDC, LOGPIXELSX)
    PointsPerPixel = POINTS_PER_INCH / lDotsPerInch
    ReleaseDC 0, hDC
    
End Function
'======================================================================================




'=================================================================
'Return the right position of extended display
'=================================================================
Public Sub CenterFormOnSecondaryScreen()
    
    Dim dblScreen1Width As Double                               'Primary display width
    Dim dblTotalWidth   As Double                               'Total width of both screens
    Dim dblScreen2Left  As Double                               'Left of second screen
    
    Me.SetUp
    
    dblScreen1Width = GetSystemMetrics32(0) * PointsPerPixel    'Get it
    
    dblTotalWidth = GetSystemMetrics32(78) * PointsPerPixel     'Get it




    dblScreen2Left = dblTotalWidth - dblScreen1Width            'Get middle of displays
    
    frmPairedForm.Left = ((dblScreen2Left) + ((dblTotalWidth - dblScreen2Left) / 2)) - (frmPairedForm.Width / 2)
    
End Sub




'=================================================================
'Position the passed user form reference in center screen of primary display
'=================================================================
Public Sub CenterFormOnPrimaryScreen()
    
    Dim dblScreen1Width As Double                                       'Primary display width
    Dim dblTotalWidth   As Double                                       'Total width of both screens
    Dim dblScreen2Left  As Double
    
    Me.SetUp
    
    dblScreen1Width = GetSystemMetrics32(0) * PointsPerPixel            'Get it
    
    dblTotalWidth = GetSystemMetrics32(78) * PointsPerPixel             'Get it




    dblScreen2Left = dblTotalWidth - dblScreen1Width                    'Get middle of displays




    frmPairedForm.Left = (dblScreen2Left / 2) - (frmPairedForm.Width / 2)




End Sub




'=======================================================================
'Figure out whether or not to make changes based on if the user has multiple screens or not
'=======================================================================
Public Sub SetUp()
    
    Dim dblScreen1Width As Double                               'For width of main screen
    Dim dblTotalWidth   As Double
    
    dblTotalWidth = GetSystemMetrics32(78) * PointsPerPixel     'Get it
    dblScreen1Width = GetSystemMetrics32(0) * PointsPerPixel    'Get it
    
    If Not dblScreen1Width = dblTotalWidth Then                 'If primary display width <> total width then
    
        blnMultipleScreens = True                               'You have multiple displays
    
    Else
        
        blnMultipleScreens = False                              'You do not have multiple displays
    
    End If
    
    If frmPairedForm.Left < dblScreen1Width Then                'If form start up left pos is on the primary display then
        
        blnMainScreen = True                                    'Userform is on the main screen
    
    Else
    
        blnMainScreen = False
    
    End If
    
End Sub




'=======================================================================
'If on the main screen, and there are multiple displays, move to secondary. Vice Versa
'=======================================================================
Public Sub SwapScreens()
    
    Me.SetUp                                'If this hasn't been set up yet, then do it
    
    If blnMultipleScreens Then              'Only continue if the user has multiple screens
        
        If blnMainScreen Then               'If on primary display
        
            Me.CenterFormOnSecondaryScreen  'Move to secondary
        
        Else                                'Else
        
            Me.CenterFormOnPrimaryScreen    'Move to primary
            
        End If




    End If
        
End Sub

Display More

pjduck33 · Dec 28th 2020

And here we are, the end of 2020 (15 years from the original post) and it is still relevant. I just came across this while creating a userform that I want to control its position on multiple monitors. This has been very helpful, thanks lacklogic!

UserForms with multiple monitors

Participate now!

Share