Userform shows up on the wrong monitor

[smkyle]

I have two userforms that I use. Recently, they have started showing up on the left monitor even though Excel is open on the right monitor. Any ideas?

 

[Jerry Sullivan]

Here's some code you can use to center your Userform on the Excel Application.

Sub Launch_UserForm1()
    With UserForm1
        .StartUpPosition = 0
        .Left = Application.Left + (0.5 * Application.Width) - (0.5 * .Width)
        .Top = Application.Top + (0.5 * Application.Height) - (0.5 * .Height)
        .Show
    End With
End Sub

 

[smkyle]

Thank you for the reply. Right now I have my userform show up in a very specific place. Centering on the application is a vast improvement, but is there anyway to keep my start position too? Would playing with the .5 values do the trick? Currently the placement is in pixels perhaps? From the categorized Userform Properties:

Height 540
Left 500.25
StartUpPosition 0-Manual
Top 99.75
Width 348

I think the bold settings are the only ones that matter

 

[Jerry Sullivan]

You could use the code below to have the Userform displayed on the correct monitor in the same position that it would show up if it were a single monitor with the Excel Application Window Maximized.

Sub Launch_UserForm()
'--Addresses Dual Screen problem by launching Userform in Maximized Application
'  --TopLeft position saved in Userform Properties at Design Time are used
'  --in relation to Maximized Application

    Dim lDsnLeft As Long, lDsnTop As Long
    
    With Application
        If .WindowState <> xlMaximized Then _
            .WindowState = xlMaximized
    End With
    
    With UserForm1
        '---Read UserForm Settings saved at Design Time
        lDsnLeft = .Left
        lDsnTop = .Top
      
        .StartUpPosition = 0
        .Left = Application.Left + lDsnLeft
        .Top = Application.Top + lDsnTop
        .Show
    End With
End Sub

Getting the userform to show at "a very specific place" requires defining that phrase.

The .Left and .Top Properties of the UserForm are expressed in Pixels so depending on the User's screen resolution; placing the UserForm at .Left = 500.25 could place it Left of Center or far Right of Center.

If your application tool is just for your use; or users that all have the same screen resolution then this might not be a problem.

 

[smkyle]

You could use the code below to have the Userform displayed on the correct monitor in the same position that it would show up if it were a single monitor with the Excel Application Window Maximized.

Sub Launch_UserForm()
'--Addresses Dual Screen problem by launching Userform in Maximized Application
'  --TopLeft position saved in Userform Properties at Design Time are used
'  --in relation to Maximized Application

    Dim lDsnLeft As Long, lDsnTop As Long
    
    With Application
        If .WindowState <> xlMaximized Then _
            .WindowState = xlMaximized
    End With
    
    With UserForm1
        '---Read UserForm Settings saved at Design Time
        lDsnLeft = .Left
        lDsnTop = .Top
      
        .StartUpPosition = 0
        .Left = Application.Left + lDsnLeft
        .Top = Application.Top + lDsnTop
        .Show
    End With
End Sub

Getting the userform to show at "a very specific place" requires defining that phrase.

The .Left and .Top Properties of the UserForm are expressed in Pixels so depending on the User's screen resolution; placing the UserForm at .Left = 500.25 could place it Left of Center or far Right of Center.

If your application tool is just for your use; or users that all have the same screen resolution then this might not be a problem.

Thank you for the response. The problem spontaneously corrected itself before I had a chance to try it. However, the problem came back this morning so I finally got my chance. I inserted the code starting with "With Application" and ending with "End With" and now my userforms show up exactly where I want them to on the correct monitor.

 

[MikeDBMan]

I would like to pass the userform name as a variable to this sub but when I try it I get an error message Runtime Error 424 Object Required on the .StartupPosition = 0 Line. Suggestions on how to pass the form name so I could use this as a generic launcher for forms? I tried using the Indirect Function but that would not help.

Sub LaunchIT(WhichOne)
    With WhichOne     'SelectLocation
        .StartUpPosition = 0
        .Left = Application.Left + (0.5 * Application.Width) - (0.5 * .Width)
        .Top = Application.Top + (0.5 * Application.Height) - (0.5 * .Height)
        .Show
    End With
End Sub

 

[Jerry Sullivan]

Mike, Below is one way. Paste this into a standard code module (not a UserForm module).

Public Sub LaunchIT(sWhichOne As String)
    With VBA.UserForms.Add(sWhichOne)
        .StartUpPosition = 0
        .Left = Application.Left + (0.5 * Application.Width) - (0.5 * .Width)
        .Top = Application.Top + (0.5 * Application.Height) - (0.5 * .Height)
        .Show
    End With
End Sub


Sub Test()
    Call LaunchIT("UserForm1")
End Sub

 

[MikeDBMan]

Jerry, I get a message "Run Time Error 424. Object Required". The ProcessIT() function is in a module of a .xlam file. I thought perhaps I needed to refer to the location of the object more but I can't seem to get that to work. Thoughts?

 

[Jerry Sullivan]

How you call that depends on where each of the parts is located.

You noted that ProcessIT() is in the .xlam
Where are the Userforms and where is the Procedure that calls ProcessIT()?

 

[MikeDBMan]

The forms and calling procedure are both in a different file, Projects.xlsm.