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.
"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.