Excel VBA Start Userforms Modeless and then go Modal

Question

Excel allows to start with a Modeless form and then display a Modal (but not the other way around)

I have an app with 4 Userforms : UF1 - Select a partner, UF2 - List existing transactions, UF21 - Display an existing transaction, UF22 - Make a new transaction. UF21 and UF22 both stem from UF2.

UF21 needs to be Modeless in order to display more than one transactions and compare side by side, therefore UF1, UF2 and UF21 are all Modeless. But I want UF22 to be Modal in order to issue one new transaction at a time.

My problem is that after I close UF22, even just ESCaping from the form right off the bat, all previous forms close. I should be able to return to UF2. If I make UF22 Modeless all is ok.

I have written a function to traverse the UserForms Collection and I am able to get a reference to the object of the Form I want to activate. So, I am able to return (in debug mode) to UF2 which is a listbox, activate the list box, but after the last pending statement both UF2 and UF1 close.

Is what I am trying to do impossible due to the nature of the Modal and Modeless forms or should I keep pushing for the correct code?

Since my original question is still open and my tested implementation of the proposed solution by @PeterT is not working properly, I include the code I have for the moment, based on @PeterT 's suggestion.

'===============
' Form UF1
'===============

Private Sub UserForm_Activate()

If ActivateUF22(FormID) = True Then Exit Sub

'.... more commands

End Sub

'============
' Form UF2
'============

Private Sub UserForm_Activate()

If ActivateUF22(FormID) = True Then Exit Sub

'.... more commands

End Sub

'----------------

Private Sub Cbn_OpenUF22_Click()

If ActivateUF22() = True Then
    Exit Sub
Else
    With New UF22
        .Show vbModeless
    End With
End If

End Sub

'================
' In a Module...
'================

Public Function ActivateUF22() As Boolean

Dim frm As Object

Set frm = GetFormFromID("UF22*") ' Custom function to get a form Object based on
                                 ' some criterion (FormID in a hidden TextBox)
If Not frm Is Nothing Then
    ' the only way I know to *Activate* an already .Show(n) form and compensate 
    ' for the fact that the Close CommandButton may already have Focus
    frm.TBx_UF22_CODE.SetFocus
    frm.CBn_UF22_CLOSE.SetFocus
    ActivateUF22 = True
Else
    ActivateUF22 = False
End If

End Function

Answer 1

Well I finally managed to get the workaround to behave.

The remaining problem was the fact that clicking twice in a row on the same userform, besides the "Modal" one, would succeed and allow the user to break out.

I even tried the "AppActivate Application.caption" approach found in another SO thread but that didn't work either.

The only solution that works and does not bother me is to insert a MsgBox with a warning to the user, as such:

Public Function ActivateUF22() As Boolean

Dim frm As Object

Set frm = GetFormFromID("UF22*") ' Custom function to get a form Object based on
                                 ' some criterion (FormID in a hidden TextBox)
If Not frm Is Nothing Then
    ' the only way I know to *Activate* an already .Show(n) form and compensate 
    ' for the fact that the Close CommandButton may already have Focus
    frm.TBx_UF22_CODE.SetFocus
    frm.CBn_UF22_CLOSE.SetFocus
    ActivateUF22 = True

MsgBox("You cannot move away from this form until it is either completed or cancelled")

Else
    ActivateUF22 = False
End If

End Function

Displaying the MsgBox does the trick internally, switches the focus to a different form from the one clicked and, upon return, the UserForm.Activate event fires normally and the ActivateUF22 function prevents the user from escaping the Pseudo-Modal form.

Thanks @PeterT for pointing me to a workaround. I managed to do what I set out to do, albeit in a different manner.

PS I still believe that there is a way to switch from a Modeless form to a Modal one. After all the MsgBox I use is obviously a Modal form and works just as I would like ;-)