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.

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

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

  • 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)
  • 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!

  • Re: UserForms with multiple monitors


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


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

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

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

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


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



    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


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

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!