Code executing twice

Bryan021 · Jul 7th 2017

I have the below code in a UserForm to check if the ComboBox1 entry is new... or if it does exist, check if the associated records are current.
As a side note: I used Frame1_Exit instead of ComboBox1_Exit because if a user skips the other fields in Frame1 and goes straight to the Frame2 fields, the ComboBox1_Exit is not triggered.

The issue I'm having is;
With the three If statements following Else, they cause the MsgBox to come up twice... once when the criteria is met, and a second when the UserForm unloads.
If I use Me.Hide instead of Unload Me, the issue doesn't happen, but the problem with that is, when the UserForm is called again, it is as it was left and you may not get to trigger the Frame1_Exit whereby missing the required checks.

Any thoughts on how to stop the message triggering for the second time?

Code

Private Sub Frame1_Exit(ByVal Cancel As MSForms.ReturnBoolean)


End Sub
    'Check if New Sub, and check if WC GL & W9 are current
    
    Dim r As Range
    Set r = Sheets("WC & GL").Columns("B").Find(What:=ComboBox1.Value, LookIn:=xlValues, LookAt:=xlWhole, _
        After:=Range("B5"), SearchOrder:=xlByColumns, SearchDirection:=xlPrevious)
    If r Is Nothing Then 'New Sub
        MsgBox "This is a New Subcontractor." & vbNewLine & vbNewLine & _
        "Please Enter Workers Comp, General Liability, & W9 Information.", vbExclamation, "New Entry"
        'Request WC, GL, & W9
        AddNewSub.TextBox1.Value = ComboBox1.Value
        AddNewSub.Show
    Else 'Check if WC, GL, & W9 are current
        If r.Offset(0, 2).DisplayFormat.Font.ColorIndex = 3 Then 'WC expired
            MsgBox "This Subs Workers Compensation has Expired." & vbNewLine & vbNewLine & _
            "Please update your records before continuing.", vbExclamation, "Expired Workers Compensation"
            Sheets("WC & GL").Activate
            r.Offset(0, 2).Activate 'Goto expired WC
            Unload Me
            Exit Sub
        End If
        If r.Offset(0, 3).DisplayFormat.Font.ColorIndex = 3 Then 'GL expired
            MsgBox "This Subs General Liability has Expired." & vbNewLine & vbNewLine & _
            "Please update your records before continuing.", vbExclamation, "Expired General Liability"
            Sheets("WC & GL").Activate
            r.Offset(0, 3).Activate 'Goto expired GL
            Unload Me
            Exit Sub
        End If
        If r.Offset(0, 4).DisplayFormat.Font.ColorIndex = 3 Then 'W9 expired
            MsgBox "This Sub hasn't provided a W9 for this year." & vbNewLine & vbNewLine & _
            "Please update your records before continuing.", vbExclamation, "Missing W9"
            Sheets("WC & GL").Activate
            r.Offset(0, 4).Activate 'Goto expired W9
            Unload Me
            Exit Sub
        End If
    End If
    
    Sheets("App Log").Activate


End Sub

Display More

**KjBox** · Jul 7th 2017

Re: Code executing twice

Can you attach your workbook.

**mikerickson** · Jul 7th 2017

Re: Code executing twice

The code in the OP has one Sub declaration line and two End Sub lines.

Bryan021 · Jul 8th 2017

Re: Code executing twice

File attached...

Click the "New Payment Application" button... select "ERIC Inc" from the "Sub Name" dropdown... click in the "Project Name" field.
You should get the Expired WC message.
When you click OK to continue, the message comes up again.

**rory** · Jul 10th 2017

Re: Code executing twice

The Exit event for your Frame is being called again when you try to unload the form from the original exit event. You can avoid that with a static variable like this:

Code

Private Sub Frame1_Exit(ByVal Cancel As MSForms.ReturnBoolean)    'Check if New Sub, and check if WC GL & W9 are current
    Static bQuitting As Boolean
    If bQuitting Then Exit Sub
    Dim r As Range
    Set r = Sheets("WC & GL").Columns("B").Find(What:=ComboBox1.Value, LookIn:=xlValues, LookAt:=xlWhole, _
        After:=Range("B5"), SearchOrder:=xlByColumns, SearchDirection:=xlPrevious)
    If r Is Nothing Then 'New Sub
        MsgBox "This is a New Subcontractor." & vbNewLine & vbNewLine & _
        "Please Enter Workers Comp, General Liability, & W9 Information.", vbExclamation, "New Entry"
        'Request WC, GL, & W9
        AddNewSub.TextBox1.Value = ComboBox1.Value
        AddNewSub.Show
    Else 'Check if WC, GL, & W9 are current
        If r.Offset(0, 2).DisplayFormat.Font.ColorIndex = 3 Then 'WC expired
            MsgBox "This Subs Workers Compensation has Expired." & vbNewLine & vbNewLine & _
            "Please update your records before continuing.", vbExclamation, "Expired Workers Compensation"
            Sheets("WC & GL").Activate
            r.Offset(0, 2).Activate 'Goto expired WC
            bQuitting = True
            Unload Me
            Exit Sub
        End If
        If r.Offset(0, 3).DisplayFormat.Font.ColorIndex = 3 Then 'GL expired
            MsgBox "This Subs General Liability has Expired." & vbNewLine & vbNewLine & _
            "Please update your records before continuing.", vbExclamation, "Expired General Liability"
            Sheets("WC & GL").Activate
            r.Offset(0, 3).Activate 'Goto expired GL
            bQuitting = True
            Unload Me
            Exit Sub
        End If
        If r.Offset(0, 4).DisplayFormat.Font.ColorIndex = 3 Then 'W9 expired
            MsgBox "This Sub hasn't provided a W9 for this year." & vbNewLine & vbNewLine & _
            "Please update your records before continuing.", vbExclamation, "Missing W9"
            Sheets("WC & GL").Activate
            r.Offset(0, 4).Activate 'Goto expired W9
            bQuitting = True
            Unload Me
            Exit Sub
        End If
    End If
    
    Sheets("App Log").Activate




End Sub

Display More

Bryan021 · Jul 10th 2017

Re: Code executing twice

Rory,
Thank you!... that did the job nicely.
As always, your time is appreciated.

Best regards,

Code executing twice

Participate now!

Share