Announcement

Collapse
No announcement yet.

UserForms with multiple monitors

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • UserForms with multiple monitors



    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.

  • #2
    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.

    Comment


    • #3
      Re: UserForms with multiple monitors

      I normally just use Excels Top & Left property to do it.
      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

      Comment


      • #4
        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)
        Regards,
        Barry

        My Favorite New Thing:
        Dynamic Named Ranges



        The alternative for
        "Press Any Key To Continue."

        and we all have one we'd like to use it on

        1. Cross Posting Etiquette
        2. Are You Here To Learn: What Have You Tried?
        3. Your Best Resource to begin learning VBA: RECORD A MACRO . . . Then Study It!

        Comment


        • #5
          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!

          Comment


          • #6
            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

            Comment


            • #7
              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.

              Comment


              • #8
                Re: UserForms with multiple monitors

                Originally posted by BillV View Post

                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.

                Comment


                • #9
                  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...
                  Reafidy

                  Forum Rules | Anatomy Of A Good Thread Title | What Have You Tried So Far? | Are You A Cross Poster?

                  Comment


                  • #10
                    Re: UserForms with multiple monitors

                    9 years later and still relevant. Thanks LackLogic!

                    Comment


                    • #11
                      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

                      Comment


                      • #12
                        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!!

                        Comment


                        • #13
                          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
                          Last edited by SamVan; March 30th, 2016, 10:05.

                          Comment


                          • #14


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

                            Comment

                            Working...
                            X