Userform opening on wrong workbook

Question

I have a userform that has a command button to open another userform. This works well most of the time but there is a particular scenario where it does not work at all.

The users of said userforms typically work on multiple monitors and also have multiple workbooks open. These other workbooks are not associated with my workbook.

If the user is working in another workbook on monitor 1 and then clicks the button to open the secondary userform on monitor 2, the secondary userform opens on monitor 1 and behaves like it's parent is the other workbook. To correct this behavior, the user needs to click a cell on monitor 2's workbook and then click the button to open the secondary userform. So it seems to be a workbook activation issue but this is not the case.

I have tried different methods of activating the correct workbook before showing the secondary userform with code to no avail.

Here's the basic code I use to launch the secondary userform when the button is clicked:

Private Sub CommandButton1_Click()

    With UserForm2
        .StartUpPosition = 0
        .Left = Application.Left + (0.5 * Application.Width) - (0.5 * .Width)
        .Top = Application.Top + (0.5 * Application.Height) - (0.5 * .Height)
        .Show vbModeless

    End With

End Sub

I found this thinking I just need to explicitly set the userforms parent window before showing it but the userform will not open at all with this code: http://www.cpearson.com/excel/SetParent.aspx

I hope this makes sense. It's kind of hard to explain.

The form is modeless, so I'm confused as to what you mean by "parent window". Is it just an issue with positioning the form? — Comintern
what does "and behaves like it's (sic) parent is the other workbook" mean exactly? Also that With block is bloody misleading, once you realize that With New witholds an object reference that gets destroyed at End With (i.e. if you change your code to With New, form will be destroyed as soon as it's shown). — Mathieu Guindon
No, not an issue with positioning the form. If you click the 'other workbook' on the task bar, the secondary userform gets focus. I should click my workbook on the taskbar and the secondary userform should get focus. It goes like this: Open my userform on monitor 2> Open other workbook on monitor 1> Click a cell on monitor 1 workbook> Now click button to open secondary userform on monitor 2> Secondary userform will open on monitor 1 instead of monitor 2> Clicking the other workbook on the task bar will give secondary userform focus indicating that its parent is the other userform — Mathew Stewart
I'm currently at home with no access to dual monitors otherwise I could maybe do a screen recording to show what I mean. I apologize :( — Mathew Stewart

Comintern Comintern · Accepted Answer · 2018-10-03T01:37:50

If you have a reference to the Workbook that you want to set the form's parent to, you only need the two window handles to pass to SetParent. For the Workbook, you can simply use your g_workbook variable to get it's Window, which exposes an Hwnd property.

For the UserForm, you'll need to use a second API call (FindWindow). Just pass it the caption of your window (and optionally the class if you're worried about it not being unique) and it returns its window handle. Put this in the declarations at the top of the module:

#If VBA7 Then
    Private Declare PtrSafe Function SetParent Lib "user32" ( _
        ByVal hWndChild As Long, _
        ByVal hWndNewParent As Long) As Long

    Private Declare PtrSafe Function FindWindow Lib "user32" Alias "FindWindowA" _
        (ByVal lpClassName As String, _
        ByVal lpWindowName As String) As Long
#Else
    Private Declare Function SetParent Lib "user32" ( _
        ByVal hWndChild As Long, _
        ByVal hWndNewParent As Long) As Long

    Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" _
        (ByVal lpClassName As String, _
        ByVal lpWindowName As String) As Long
#End If

Then, make the call to SetParent before you show the form:

Private Sub CommandButton1_Click()
    With New UserForm2
        .StartUpPosition = 0

        Dim host As Window
        Set host = g_workbook.Windows(1)
        'You can also calculate these on the host position if you want.
        .Left = Application.Left + (0.5 * Application.Width) - (0.5 * .Width)
        .Top = Application.Top + (0.5 * Application.Height) - (0.5 * .Height)

        Dim handle As Long
        handle = FindWindow(vbNullString, .Caption)
        SetParent handle, host.Hwnd

        .Show vbModeless
    End With
End Sub

As you speculated in the question, this seems to work at sorting the focus out. Whether or not it is stable is a different question - it's conceivable that if you set the parent to the wrong workbook that closing the new parent workbook could cause unintended behavior.

Userform opening on wrong workbook

1 Answers