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

I would set a flag in your UF21 form that indicates UF22 is open (and modeless). If the user tries to interact with UF21, check the flag and if it shows that UF22 is open, you can "flash" UF21 as an indication the user should complete the UF22 form. — PeterT, Dec 12 '19 at 13:53
I apologize @PeterT, obviously I haven't made clear that both UF21 and UF22 are shown by UF2. Anyway I see what you mean, go Modeless with UF22 and check if ANY other form gets Activated in order to block the Activation and return to UF22, effectively implementing the Modal behavior. I already have the mechanism in place to check the Userforms Collection, so it would be fairly easy to find if UF22 is active. I am not sure what happens in the Terminate procedure though. i.e. will UF2 run the check in it's Activate Event after UF22 has terminated or before. I'll let you know. — gthalassinos, Dec 12 '19 at 14:07
@PeterT it's like I feared. If I click on UF1 the routine works and "activates" UF22 again. Same with UF2. Same if I sequentially click UF1-UF2-UF1-UF2 etc. But if I click on the SAME form twice in a row, the Activate even does not fire up, hence the routine does not fire up. It's like the Focus is still on UF1(or UF2 for that matter) even when the active window (judging by the OS's active window color) is UF22. Can you tell me how you activate a form on demand? The only way I have found is to SetFocus to some CommandButton or TextBox on that form. — gthalassinos, Dec 12 '19 at 16:45

score 0 · Answer 1 · answered Dec 13 '19 at 21:59

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 ;-)

Excel VBA Start Userforms Modeless and then go Modal

1 Answers1