excelvbauserformmodeless

Code keeps running after modeless userform is opened


When a modeless userform gets loaded, the code in it (except for the Userform_Initialize sub) sometimes does not execute and the main code which calls the userform continues running.

I had it solved, somehow, but this was in the latest update of my program which unfortunately got corrupted.

Sub start() 'shortened drastically, but accurate
'....

If UBound(rs.GetRows(1000000), 2) + 1 < 6 Then
    .Close SaveChanges:=False
    ThisWorkbook.Sheets("Start").Range("DA1").Value = "1"
    ThisWorkbook.Sheets("Start").Range("DA2").Value = MachineNr
    UserForm1.Show vbModeless
    GoTo ExSub
End If 

'...
ExSub:
End Sub

And in the userform module:

Private Sub UserForm_Initialize()
Dim wb As Workbook

If ThisWorkbook.Sheets("Start").Bijwerken = "ja" Then
    Me.CommandButton2.Caption = "Cancel"
    Me.Label1.Caption = "Select a file to update"

    bestand = ""
    With Me.ComboBox1
        For Each wb In Application.Workbooks
        If Not wb.Name = ThisWorkbook.Name Then
            For Each sht In wb.Sheets 
                If sht.Name = "AssetTypeTask" Then
                    .AddItem wb.Name
                    Exit For
                End If
            Next sht
        End If
        Next wb
    End With

Else
    bestand = ""
    With Me.ComboBox1
        For Each wb In Application.Workbooks
        If Not wb.Name = ThisWorkbook.Name Then
            .AddItem wb.Name
        End If
        Next wb
    End With

End If
End Sub

The code runs through the Userform_Initialize sub without issues, but the userform does not appear at the front of the screen and the code continues at GoTo ExSub which then ends the code execution. The userform stays open but closes as I press one of the commandbuttons on it.


Solution

  • "Code execution will also continue in the background while a modeless form is shown."

    This is what brought me back on my feet. I knew this but had forgotten about it as I firmly believe and still believe that in previous versions of my program I had a modeless UF running that DID interrupt the code.

    I ended up solving the issue of the running code by adding a simple loop to check the state of the UF

    Do Until Userform1.Visible = False
        DoEvents
    Loop
    

    This is a slight drawback for the CPU of course, so not ideal, but since this is not a very intensive part of the program, it will do.

    The UserForm I use in this instance has to be modeless, because the user needs to be able to scroll through the userform to make sure what they are populating the controls in the UF with is correct. This is not programmable.

    If anyone has any other ways of achieving this, please let me know.