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

How is your userform launched (opened)?

If it is opened with a button, check condition (if there are any 'True' values in column(15) in 'Admin' sheet) before userform.show line. It's the easiest way I found.

If not, find another way to check your condition before triggering UserForm_Initialize event.

Example:

Option Explicit
Sub open_form()

Dim aDoc as Document
Set aDoc = ActiveDocument
         
'1. check if content controls are present
 
    On Error GoTo Errorhandler_deleted_content_control
        
        Set CCtrl = aDoc.SelectContentControlsByTitle("CC_values").Item(1)
        Set CCtrl = aDoc.SelectContentControlsByTitle("clean_version_generated").Item(1)
        Set CCtrl = aDoc.SelectContentControlsByTitle("Form_loaded").Item(1)
        Set CCtrl = aDoc.SelectContentControlsByTitle("Signature_type_pg_1").Item(1)
        Set CCtrl = aDoc.SelectContentControlsByTitle("Signature_type_6.12").Item(1)
        Set CCtrl = aDoc.SelectContentControlsByTitle("bank_signer_all").Item(1)
        Set CCtrl = aDoc.SelectContentControlsByTitle("bank_signer_name").Item(1)
        Set CCtrl = aDoc.SelectContentControlsByTitle("bank_signer_position").Item(1)
        Set CCtrl = aDoc.SelectContentControlsByTitle("bank_signer_power_of_attorney").Item(1)
        
    On Error GoTo 0
         
'2. open userform (cм. Userform_Initialize)
    Form.Show

    Exit Sub 'so that error handler isn't executed

'3. error handler if content controls are deleted
    
Errorhandler_deleted_content_control:
    
    On Error GoTo -1
    On Error GoTo 0

    MsgBox "your message", vbCritical + vbOKOnly, "ERROR"
    Exit Sub

End Sub

Answer 2

In order to avoid displaying the User Form, I tried almost everything, and the following worked fine, at least for me: In my Macros, I use both "UserForm_intitialize" and "UserForm_activate". Should I try to "unload me" within the "UF_initialize", an error is displayed by the system that I want to avoid. To achieve that, I am using "Error Handling"

• I used a global var named "gStopProgramExecution" that is set to TRUE via error handling.
• Please Note that VBA first executes UF_Initialize and then UF_activate

i.e.:

    Private Sub UserForm_Initialize()
    On Error GoTo abruptTermination

       gStopProgramExecution = False
       .......your code......
       Call anySub    ' upon return check if the var has been set to TRUE
         If gStopProgramExecution Then GoTo abruptTermination
       .......your code......
    Exit Sub
    
    abruptTermination:
    gStopProgramExecution = True
    MsgBox gAbruptProgramExecution, vbCritical
    ' If an error has happened, inform the user that program will close abruptly
    ' VBA jumps automatically to UF_activate sub
    ' DO NOT USE: unload me AT THIS POINT
    ..........
    End Sub
    ------------------------------
    Private Sub UserForm_Activate()
    On Error GoTo abruptTermination
    ' Check first if you are coming here due to an error and if so, GoTo "abruptTermination:"
    If gStopProgramExecution Then GoTo abruptTermination
    .......your code......
    ' exit procedure normally
    Exit Sub

    abruptTermination:
    gStopProgramExecution = True
    '
    ' unload here the User Form.
    ' No error is generated, and the Program closes without unwanted generation of further error messages, displayed automatically by the system
    '
    Unload Me
   End Sub

' I hope this approach helps

Answer 3

Please look at your code; you have put Unload Me is after Exit For

    'Here is something for you to ponder on .........


    'Public enum type to add a set of particular vbKeys to the standard key set
    Public Enum typePressKeys
        vbNoKey = 0
        vbExitTrigger = -1
        vbAnswerKey = 100
        vbLaunchKey = 102
        vbPrevious = 104
        vbNext = 106
        vbSpecialAccessKey = 108
    End Enum

    Public Sub doSomethingWithMyUserform()
    Dim stopLoop As Boolean, testVal As Boolean, rngX As Range, LR As Long

    LR = ThisWorkbook.Sheets("Project_Name").Cells(Rows.Count, "B").End(xlUp).Row
    Set rngX = ThisWorkbook.Worksheets("Admin")
    testVal = False
    With rngX 'Your sub can do the check here
        For i = 7 To LR
           If .Cells(i, 15) = "True" Then
                testVal = True
                Exit For
            End If
        Next i
    End With

    If testVal Then
        Load UserForm1
        With UserForm1
            .Caption = "Something"
            .Tag = vbNoKey
            .button_OK.SetFocus 'Assuming you have a OK button on Userform1
        End With
        UserForm1.Show
        stopLoop = False
        Do
            If UserForm1.Tag = vbCancel Then
                'Do something perhaps
                Unload UserForm1
                stopLoop = True
            ElseIf UserForm1.Tag = vbOK Then
                'Do something specific
                Unload UserForm1
                stopLoop = True
            Else
                stopLoop = False
            End If
        Loop Until stopLoop = True
    else
       MsgBox "No values found"
    End If

    'Here you can close the way you want
    Set rngX = Nothing

    End Sub

        enter code here

Answer 4

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