Userform initialize checks then close

Question

I have a userform. The idea is to check if there are any 'True' values in column(15) in 'Admin' sheet. If there is at least a single 'True' value, then the userform will remain open and continue its operation.

However, if there is not a single 'True' found, then the userform will display a message and close the userform automatically.

Private Sub Userform_initialize()

    Dim LR As Long
    LR = Sheets("Project_Name").Cells(Rows.Count, "B").End(xlUp).Row

    With Worksheets("Admin")
        For i = 7 To LR
            If .Cells(i, 15) = "True" Then
                Exit For
            Else
                MsgBox ("No values found")
                Exit For
                Unload Me
            End If
        Next i
    End With
    ''' more code'''
End Sub

Everything on my userform works as expected, except for the fact I am unable to make it close itself automatically. I.e. Unload Me is not working.

Any advice?

Answer 1

You should check your criteria before you even display the UserForm. You can add this as a condition wherever you are calling the UserForm. No need to open the form just to immediately close it when you can check before-hand.

On the first instance of True, the UserForm will open, and exit the sub. If the loop completes (finds no True values), the sub will proceed to your MsgBox

Sub OpenForm

With Worksheets("Admin")
    For i = 7 To LR
       If Cells(i,15) = "True" then 
         Userform.Show
         Exit Sub
       End If
    Next i
End With

MsgBox "No Values Found"

End Sub