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?
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