Macro Hangs Second Time Showing a Userform when toggling between two userforms

Question

I have two userforms. The first form has a checkbox and when checked true it causes the form to disappear (hide) and the second form to appear. The second form asks some questions and when it is closed (unload) it writes the answer to a string variable located within the code of the first form. (The part of writing to another variable in another form doesn't factor into my issue since I have commented it out and the issue persists.)

As a part of form 2's userform_terminate function it reshows form 1.

The first time it works as expected; Check box, form 1 disappears, form 2 appears, close form 2, form 1 appears.

On the second attempt the second form will show, but its userform_initialize doesn't seem to trigger (no debug message and break point doesn't activate), and the macro stalls at the line userform2.show contained in the userform1's code (see below).

The close button and the top right x will animate when they are pressed, but nothing happens. Only way out is to reset the code.

Second clue is that the second debug message in Userform2, the one after Userform1.show does not print.

Third clue is that the debug message set up immediately after Userform2.show in Userform1 does not appear until both userforms are closed (2 then 1).

It seems like Userform2 is not fully terminating because of Userform1.show.

Here are the code snippets.

Userform1 Sub that calls Userform2 and where it seems to stall:

Private Sub InputCheckBox_Change()
    If InputCheckBox.Value = True Then
        Userform1.Hide
        Userform2.Show  'This is the line where it is stalled at if I break the execution
        debug.print "This message does not appear until after I've closed both"
    End If
End Sub

Userform2, this is everything in it:

Private Sub Userform2CloseButton_Click()
    Unload Userform2
End Sub
    
Private Sub UserForm_Initialize()
    Debug.Print "Userform2 initialized"
End Sub
    
Private Sub UserForm_Terminate()
    'Some stuff happens here but for testing I
    'commented it out and the issue is still there
    debug.print "This message will show up"
    Userform1.Show
    debug.print "But this message will not"
End Sub

I tried various combinations of the order of shows and hides which I think was answer given in this post.

As a workaround I can use userform2.hide instead and it seems to work. But it involves extra code to handle the case of using the top right x instead of the close button.

FYI to avoid ambiguity you can/should use Me inside a form to refer to the form itself. You're using the "default instance" of your forms and that's best avoided if you can - see for example books.google.co.uk/… — Tim Williams
@TimWilliams. Thanks for taking the time to respond. My actual code doesn't use Userform1 and Userform2, I just changed them to that for the sake of the example. I'll take your advice on using Me more. — MidnightRover

MidnightRover MidnightRover · Accepted Answer · 2020-11-26T14:47:56

So what I think I have learned is that execution of userform1 is paused while userform2 is being shown, and execution of userform2 is paused when userform1 is reshown. Thus when when userform1 tries to reshow userform2, it can't load because it never finished unloading.

This is the code that got me working.

Userform1

Private Sub InputCheckBox_Change()
    If InputCheckBox.value = True Then
        Userform1.Hide
        Userform2.Show 'While Userform2 is being show, the code seems to halt here
        Userform1.Show 'Used to be in Userform2_Terminate or _QueryClose
    End If
End Sub

Userform2, again in its entirety

Private Sub DefineRepeatableCloseButton_Click()
    Unload DefineRepeatable
End Sub

Private Sub UserForm_Initialize()
    Debug.Print "DefineRepeatable initialized"
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    'Bunch of stuff happens here
End Sub

However I had found examples of code that showed putting Userform1.show in Userform2's terminate function or examples with the two forms being active at the same time. So I'm sure there is still something I don't understand about VBA forms so if anyone has a good reference that might explain some of these things it would be appreciated.

Thanks

Macro Hangs Second Time Showing a Userform when toggling between two userforms

1 Answers