Modeless UserForm appears on wrong Excel workbook

[mcomp72]

I built an Excel workbook a couple of years ago to help my Dad track golf scores for a senior golf league he is in. One of the things I added was a modeless userform that should only appear when one particular sheet (named "Roster") is the ActiveSheet. I put a button at the top of the sheet to load & show the userform. (If the userform is already loaded, it will only show it.)

Everything has been working great since I made it, but today he noticed a problem. He had a second Excel file open, and when he clicked the button to show the userform, rather than it appearing on the Roster sheet, it appears on the sheet on the other Excel file that was open. I tried it on my computer and the same problem happened.

I have looked at the code related to this userform, and I can't see anything that would be causing the issue. But, I don't have a ton of experience with modeless userforms, so there's probably something simple that I'm missing. Here is all the code related to the userform.

This is the code tied to the button on the Roster sheet.

Sub ShowMemberMenu_Button()

Call ShowMemberMenu

End Sub

Here is the sub called by the sub above. MattData is a hidden sheet that tracks many things. There are some cases where I don't want the userform to be loaded, so that's why I do this check.

Sub ShowMemberMenu()

With ThisWorkbook.Sheets("MattData")

    If .Range("AddOrEditMemberButtonPressed").Value = False And .Range("FileBeingImported").Value = False Then
   
        If IsFormLoaded("RosterButtons") = False Then Load RosterButtons
       
        ThisWorkbook.Activate
       
        RosterButtons.Show vbModeless
       
        ReturnFocusToExcelSheet
       
    End If
   
End With

End Sub

The code runs correctly. When I step through it, it goes through the entire IF block without causing an error -- it's just displaying the userform on the wrong file.

Here is the function IsFormLoaded called by the sub above.

Public Function IsFormLoaded(FrmName As String) As Boolean

Dim Frm As Object

On Error GoTo errorH

IsFormLoaded = False

For Each Frm In VBA.UserForms

    If VBA.LCase$(Frm.Name) = VBA.LCase$(FrmName) Then
       IsFormLoaded = True
       GoTo Ending
    End If
   
Next

errorH:
IsFormLoaded = False

Ending:
Set Frm = Nothing

End Function

Lastly, here is the sub ReturnFocusToExcelSheet, which is supposed to move the focus from the userform back to the Roster sheet.

Sub ReturnFocusToExcelSheet()

On Error Resume Next
AppActivate Excel.ThisWorkbook.Application.Caption
On Error GoTo 0

End Sub

Can you see any reason why the userform would be visible on the sheet in the other Excel file that is open instead of on the file that contains the code (ThisWorkbook)?

 

[mcomp72]

After I made my post, I realized I forgot to include a couple of subs.

Here is the Initialize sub for the userform.

Private Sub UserForm_Initialize()

Dim CenterUF As Boolean
Dim Top As Double
Dim Left As Double

FillInRosterButtonsUF

'The rest of this sub sets the position of the UF on the screen.
Me.StartUpPosition = 0

With ThisWorkbook.Sheets("MattData")

    Top = .Range("RosterButtonsUF_Top").Value
    Left = .Range("RosterButtonsUF_Left").Value

    If Top < Application.Top Then CenterUF = True
    If Left < Application.Left Then CenterUF = True
  
    If Top > Application.Top + Application.Height Then CenterUF = True
    If Left > Application.Left + Application.Width Then CenterUF = True

    If CenterUF = True Then
  
        Me.Top = Application.Top + (0.5 * Application.Height) - (0.5 * Me.Height)
        Me.Left = Application.Left + (0.5 * Application.Width) - (0.5 * Me.Width)

    ElseIf Not IsEmpty(.Range("RosterButtonsUF_Left")) And Not IsEmpty(.Range("RosterButtonsUF_Top")) Then
  
        Me.Top = Top
        Me.Left = Left
  
    Else

        Me.Top = ((Application.Height - Me.Height) / 2) + 90
        Me.Left = (Application.Width - Me.Width - 30)
  
    End If

End With

End Sub

Here's the FillInRosterButtonsUF sub. FYI - RosterButtons is the name of the userform.

Sub FillInRosterButtonsUF()

Dim FName As String
Dim LName As String
Dim SS As String
Dim MemSelected As Boolean
Dim Row As Integer
Const NA As String = "N/A"

If OneRowSelectedOnRoster = True Then MemSelected = True

If MemSelected = True Then

    With ThisWorkbook.Sheets("Roster")

        .Activate
        Row = Selection.Row
        FName = .Range("B" & Row).Value
        LName = .Range("A" & Row).Value
        SS = .Range("Z" & Row).Value
      
    End With

End If

With RosterButtons

    .EditMember.Enabled = MemSelected
    .ScoreSheet.Enabled = MemSelected
    .EnterScore.Enabled = MemSelected
    .AddToSignIn.Enabled = MemSelected
    .DeleteMember.Enabled = MemSelected

    If MemSelected = False Then
  
        .MemberName.Caption = NA
      
    Else
  
        .MemberName.Caption = FName & " " & LName

        If DoesScoreSheetExist(SS) = True Then
            .ScoreSheet.Caption = "Open Score Sheet"
            .EnterScore.Enabled = True
        Else
            .ScoreSheet.Caption = "Create Score Sheet"
            .EnterScore.Enabled = False
        End If
      
        If IsMemberOnSignInSheet(FName, LName) = True Then
            .AddToSignIn.Enabled = False
        Else
            .AddToSignIn.Enabled = True
        End If
      
    End If

End With

End Sub

 

[NdNoviceHlp]

This looks wrong...

AppActivate Excel.ThisWorkbook.Application.Caption

Remove the on error resume next to see what happens. I think it should be just this

AppActivate Application.Caption

If that doesn't change anything, then maybe be more specific with you workbook name instead of using ThisWorkbook ie. Workbooks("Bart").Sheets("Sheet1") etc.
HTH. Dave

 

[mcomp72]

Thanks for the reply.

I changed the ReturnFocusToExcelSheet sub to this:

Sub ReturnFocusToExcelSheet()

AppActivate Application.Caption
ThisWorkbook.Activate
ThisWorkbook.Sheets("Roster").Activate

End Sub

Unfortunately, the problem still occurred. Were those the changes you were suggesting?

 

[mcomp72]

I decided to ask ChatGPT 01-preview about this, to see if had any insight. I uploaded all of the same code I put in this tread. It came back with an answer that would certainly explain the issue. Though, I have no clue if it is accurate.

The problem you're experiencing is due to how Excel handles modeless UserForms in the Single Document Interface (SDI) environment introduced in Excel 2013. In SDI, each workbook window is a separate instance, and modeless UserForms are not inherently tied to a specific workbook window, which can cause them to appear over any open workbook.

Understanding the Issue:

• SDI Environment: Starting from Excel 2013, each workbook has its own window, and Excel does not automatically associate modeless UserForms with a specific workbook window.
• UserForm Ownership: By default, modeless UserForms are owned by the Excel application, not a specific workbook window.

Solution: Explicitly Set the UserForm's Owner to the Desired Workbook Window

To ensure the UserForm appears over the correct workbook, you need to set its owner window to the window handle (hWnd) of the desired workbook. This requires using Windows API functions to manipulate window properties.

It went on to give me a bunch of code for trying this approach. However, I'm a bit leery since I have never tried using Windows API in VBA before. Plus, I know that these LLM's can sometimes make mistakes, so who knows if the code it is giving me will actually work.

What I've decided to do is this: when my Dad clicks the button to open the userform, I will have the code check to see if any other Excel files are open. If yes, it will show a MsgBox saying the userform cannot be displayed while other Excel files are open. Not the most elegant solution, but the userform is rarely needed, so I think in this case, it will an acceptable solution.

 

[Jaafar Tribak]

Actually, in my excel 2016 edition, the opposite is what happes. ie:- the userform is always tied to the owner workbook.

Anyways, the code you need is very easy. Try adding the following to the UserForm module:

Option Explicit

#If VBA7 Then
    #If Win64 Then
        Private Declare PtrSafe Function SetWindowLong Lib "user32" Alias "SetWindowLongPtrA" (ByVal hwnd As LongPtr, ByVal nIndex As Long, ByVal dwNewLong As LongPtr) As Long
    #Else
        Private Declare PtrSafe Function SetWindowLong Lib "user32" Alias "SetWindowLongA" (ByVal hwnd As Long, ByVal nIndex As Long, ByVal dwNewLong As Long) As Long
    #End If
    Private Declare PtrSafe Function IUnknown_GetWindow Lib "shlwapi" Alias "#172" (ByVal pIUnk As IUnknown, ByVal hwnd As LongPtr) As Long
#Else
    Private Enum LongPtr
        [_]
    End Enum
    Private Declare Function SetWindowLong Lib "user32" Alias "SetWindowLongA" (ByVal hwnd As Long, ByVal nIndex As Long, ByVal dwNewLong As Long) As Long
    Private Declare Function IUnknown_GetWindow Lib "shlwapi" Alias "#172" (ByVal pIUnk As IUnknown, ByVal hwnd As LongPtr) As Long
#End If

Public Property Let OwnerHwnd(ByVal Owner As LongPtr)
    Const GWL_HWNDPARENT = (-8&)
    Dim hwnd As LongPtr
    Call IUnknown_GetWindow(Me, VarPtr(hwnd))
    If SetWindowLong(hwnd, GWL_HWNDPARENT, Owner) = 0 Then
        MsgBox "Failed to set owner window"
    End If
End Property

Private Sub UserForm_Initialize()

    Me.OwnerHwnd= ThisWorkbook.Windows(1&).hwnd
 
    'Rest of your existing code goes here ...

End Sub