How to have VBA code wait until vbModeless userform is closed

Question

I'd like to use a modeless userform so the user can navigate the Excel sheet before answering the question on the userform. I need to pause or loop the code until the userform is closed (hidden or unloaded).

Similar issue: How can I wait for a specific code to run while when form is closed and is set to vbModeless?
The solution here does not work for my application. My userform is opened in the middle of a long subroutine which needs to finish executing after the userform is closed.

Dim popupActive as Boolean

popupActive = True
StartingSINT_Popup.Show vbModeless 'Open userform

'have VBA code wait until userform is closed
wait until popupActive = False 'set to false with OK button on userform

'continue code with info input inside StartingSINT_Popup userform

score 8 · Answer 1 · answered Apr 30 '19 at 18:42

My userform is opened in the middle of a long subroutine which needs to finish executing after the userform is closed.

Your procedure is doing too many things and needs to be broken down into smaller, more specialized procedures.

The correct way to do this, is to shift the paradigm from procedural to event-driven.

Instead of showing the form's default instance like this:

StartingSINT_Popup.Show vbModeless 'Open userform

Have a class module that holds a WithEvent instance of it:

Private WithEvents popup As StartingSINT_Popup

Private Sub Class_Initialize()
    Set popup = New StartingSINT_Popup
End Sub

Public Sub Show()
    popup.Show vbModeless
End Sub

Private Sub popup_Closed()
    ' code to run when the form is closed
End Sub

In the form's code-behind, declare a Closed event:

Public Event Closed()

And then raise it in the QueryClose handler:

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    If CloseMode = 0 Then 'controlbox was clicked (the "red X button")
        Cancel = True 'would otherwise destroy the form instance
        Me.Hide 'always hide, never unload
    End If
    RaiseEvent Closed
End Sub

Now say you named that class PopupPresenter, your procedure can now do this:

Private presenter As PopupPresenter

Public Sub DoStuff()
    Set presenter = New PopupPresenter

    'do stuff...

    presenter.Show

    'rest of the code in this scope will run immediately AND THIS IS FINE

End Sub

Keep the presenter at module level so that the object doesn't go out of scope when DoStuff finishes, and pass any variables/values or state that the presenter object needs to do its job when the form is closed. You can do this by exposing properties or public fields/variables (prefer properties though, but that's a whole other topic):

Private WithEvents popup As StartingSINT_Popup
Public Foo As String

Private Sub Class_Initialize()
    Set popup = New StartingSINT_Popup
End Sub

Public Sub Show()
    popup.Show vbModeless
End Sub

Private Sub popup_Closed()
    ' code to run when the form is closed
    MsgBox Foo
End Sub

Private presenter As PopupPresenter

Public Sub DoStuff()
    Set presenter = New PopupPresenter

    'do stuff...

    presenter.Show
    presenter.Foo = "some data"

    'rest of the code in this scope will run immediately AND THIS IS FINE

End Sub

How can we have an event be triggered after `presenter.Show` executes with this approach? Currently UserForm_Activate does nothing, primarily looking to load a model into the form — PP8, May 11 '20 at 23:22
@Jose not sure I follow. If you have a `Private WithEvents popup As SomeForm` then the activate handler would be `popup_Activate` - you should never type event handler signatures by hand... select the provider from the code pane top-left dropdown, then pick the event you want to handle from the top-right dropdown. See [this post](https://stackoverflow.com/a/47291028/1188513) (and the linked article) for more info on *Model-View-Presenter*. — Mathieu Guindon, May 11 '20 at 23:37
@MathieuGuindon Thanks for providing that clarity. What if I have some code inside of my `UserForm` that sets the form sizing and loads `Model` values into `ComboBoxes`, How can I make that code execute? Would I need to declare a `Public Sub` inside of the `UserForm` and call the Sub via `popup_Activate` event? — PP8, May 12 '20 at 00:51
I would have a `Public Property Set Model(ByVal value As Object)` and have the presenter property-inject the model there. The property has all rights to do something like `Set viewModel = value` and then to invoke some `InitializeView` procedure before returning, where things like `Me.NameBox.Text = viewModel.Name` happen. Meanwhile the `NameBox_Change` handler does `viewModel.Name = Me.NameBox.Text`, and optionally invokes model validation (which can then determine whether the OK button is enabled). — Mathieu Guindon, May 12 '20 at 01:00
@MathieuGuindon thanks!! That makes total sense! I am still learning about MVP and OOP thanks to your RubberDuck blog! I was the same person who commented on your CarFactory post 2 weeks ago. Thanks for all you do for us! — PP8, May 12 '20 at 01:36
It took me a minute to "generalize" this and I'm looking to functionalize it to utilize the same code for multiple userforms. Still learning for sure, but I realize this is the way to go. I tried using a Boolean and it just was a nightmare. I have userforms, call userforms, and then I'd need layers of booleans.... Just felt wrong. Here is my question w/ generalized code from this Answer. Thanks! __> https://stackoverflow.com/questions/66113380/functionalize-event-driven-modeless-userform-class — FreeSoftwareServers, Feb 09 '21 at 05:19

score 2 · Accepted Answer · edited Apr 30 '19 at 23:21

2

I didn't author the following function, but I have used it for a long time and it works.

Private Function IsLoaded(ByVal formName As String) As Boolean
    Dim frm As Object
    For Each frm In VBA.UserForms
        If frm.Name = formName Then
            IsLoaded = True
            Exit Function
        End If
    Next frm
    IsLoaded = False
End Function

You will need to hardcode the string name, and not use the .Name property of the form because the form may not be loaded yet and not contain this property.

Here is a small snippet of how you can use this function:

Do While IsLoaded("StartingSINT_Popup")
    Debug.Print Time; " StartingSINT_Popup Is Loaded!"
Loop

edited Apr 30 '19 at 23:21

Mathieu Guindon

69,817
8
107
235

answered Apr 30 '19 at 18:08

K.Dᴀᴠɪs

9,945
11
33
43

This crashes VBA because (I think) the while loop is executing so fast and always seeing its still loaded. – Nick Apr 30 '19 at 18:25
You probably would need to add a `DoEvents` Line in the loop as well and/or a timer that waits to call the function. – K.Dᴀᴠɪs Apr 30 '19 at 18:29
1

@Nick FWIW `While`-waiting like this is really, *really* stretching the procedural paradigm well beyond what's acceptable IMO. – Mathieu Guindon Apr 30 '19 at 18:47
1

@Nick - FYI I recommend reading the related topic [destroy a modeless userform instance properly](https://stackoverflow.com/questions/47357708/vba-destroy-a-modeless-userform-instance-properly) based on Mathieu's outstanding overview ["UserForm1.Show?"](https://rubberduckvba.wordpress.com/2017/10/25/userform1-show/) – T.M. May 01 '19 at 08:40

score 0 · Answer 3 · answered Dec 17 '19 at 18:58

Here is an alternative...

1. In the original [public] module (the one that calls userform 1), declare a public Boolean variable.

Public done As Boolean

2. In userform 1,

a. Assign a default value to the Boolean variable

b. Call Userform 2

c. Have a do while loop that...

checks for that default value
includes the DoEvents method (allows userform 2 to push through despite loop)

Code

    Private Sub event_click()

    done = False

    Dim userform2 As New userform
    userform2.Show Modeless

    'This will loop through until userform2 changes done variable to "True"
    Do While done = False
    DoEvents
    Loop

    'Code after done with userform2
    dataSource.Refresh

    End Sub

3. In userform 2, change value of Boolean to break loop

Code

    Private Sub submit_Click()

    'Userform submit code
    Dim name As String        
    name = TextBox.Value
    sql = "INSERT INTO table (field) VALUES ('" & name & "')"        
    Call query(sql)

    'IMPORTANT: change Boolean variable to break loop before exiting userform
    done = True

    Unload Me

    End Sub

How to have VBA code wait until vbModeless userform is closed

3 Answers3

Linked