How to bypass other subs on unload in a userform?

Question

This bit of code checks for duplicate values in a userform textbox and forces the user to fill in the information. Works great! My only problem is now I cannot unload the userform without triggering the event, and if I am trying to cancel completly then that is a problem... (I have a few of these to boot...)

Do you have any ideas on how to bypass or supress this?

    Duplicate check code
    Private Sub ItemName_exit(ByVal Cancel As MSForms.ReturnBoolean)  'checks for duplicate

    If Application.WorksheetFunction.CountIf(Worksheets(2).Range("B6:B505"), ItemName.Text) > 0 Then
    MsgBox ("Duplicate value, please change the name."), vbOKOnly, Title:="Duplicate"
       Cancel = True
        Exit Sub: End If

    End Sub

I have tried suppressing events as boolean, turning off display alerts to no effect...

Any ideas?

Answer 1

Daniel, using the TextBox1_Change event would be better. This event checks as you type, and 'Exiting Sub' in your IF statement won't close the userform, either - unless you want it to, of course. You can add a ControlTipText in Design mode for your TextBox, then make sure to change ShowModal property to False. The below example is different than what you had, but accomplishes the goal.

Code Ex:

Option Explicit

Private Sub TextBox1_Change()

Dim ws As Worksheet
Dim rng As Range
Dim intDupes As Integer

'set variables
Set ws = ThisWorkbook.Worksheets("sheetname")
Set rng = ws.Range("B6:B505")
intDupes = Application.WorksheetFunction.CountIf(rng, TextBox1.Value)

'changes color of textbox
'also, you can add a ControlTipText text to the textbox
'that informs the user what your message box did
If intDupes > 0 Then
    TextBox1.BackColor = vbRed
ElseIf intDupes = 0 Then
    TextBox1.BackColor = vbWhite
End If

'clean up
Set cell = Nothing: Set ws = Nothing

End Sub