How can I wait for a specific code to run while when form is closed and is set to vbModeless?

Question

I have a macro that generates a report to show that our bank and accounting records tie out for the day. In the end of the macro a UserForm displays only if a discrepancy is detected and allows the user to remove it from the ListBox once they have taken care of the discrepancy.

In order for the user to analyze the input data (which is imported into the workbook on a separate sheet) while keeping the UserForm open (to track the discrepancies), I simply set the UserForm.Show vbModeless. My issue is now that I'm trying to implement a new function to run after the UserForm is closed but with it in a Modeless state, it simply checks if the condition is met and moves on.

I tried to implement a GoTo X in the OKButton event listener, but I'm not sure how to have the code GoTo a line in the main Module when referenced from within the UserForm.

My Question: How can I keep this UserForm set as Modeless, and allow the user to navigate through the data with it open, but not continue code unless it's closed / unloaded / OkButton is pressed?

Here's the button Listener:

Private Sub OKButton_Click()
    '.... Code here
    Me.Hide
    LeftoverValues.Closed = true '(other UserForm if discrepancies not taken care of)
    Closed = true 'boolean to flag this as closed
    GoTo UnmatchedSummaryClosed: '(name of form, GoTo Line in module)
End Sub

Here's the code for the end of the macro - from calling UserForm to end:

    '... Approx 2000 lines worth of code leading to this

    Application.ScreenUpdating = True

    Beep 'To alert users that it's 'finished'

    If WireValues.Count > 0 Or BWGPValues.Count > 0 Then
        Call DifferenceForm    'Discrepancy UserForm - Named UnmatchedSummary
    End If

    Beep   'alert to show discrepancy check is done - shows if needed

    Call warnForm

UnmatchedClosed:   'INTENDED GoTo LINE FROM USERFORM
    If UnmatchedSummary.Closed And LeftoverValues.Closed And WarningForm.Closed Then
        Call ARSummary
    End If

    Call StopTimer(time1, Started1)  'Timer to show how long macro took
    Debug.Print "Total Time: " & ShowTime(time1) & "ms"

    If UnmatchedSummary.Closed And WarningForm.Closed And ARSummaryDone Then
        End             'Also want this to happen only if everything's done
    End If              ' Don't want data to hang if user doesn't close macro template
End Sub

Please don't use the [macros] tag for Excel/VBA questions. The tag info for it says, `*Not for MS-OFFICE / VBA / macro languages. Use the respective tags instead.*` — Alex Knauth, Jul 22 '16 at 00:02

score 1 · Accepted Answer · answered Jul 21 '16 at 19:15

1

You can try putting a call to the function you want to run in the UserForm_Terminate() which fires when the userform is closed.

answered Jul 21 '16 at 19:15

Michael Russo

442
6
14

Does the `Terminate` event fire when `Me.Hide` is called? – Munkeeface Jul 21 '16 at 21:20
No, only when closed or unloaded – Michael Russo Jul 26 '16 at 15:14

How can I wait for a specific code to run while when form is closed and is set to vbModeless?

1 Answers1

Linked