[ Checking If String Already Exists in Worksheet Column ]

I have a userform that's supposed to validate new record entries to avoid duplicating data. I'm using Chip Pearson's FindAll (http://www.cpearson.com/excel/findall.aspx) method to prevent a user from entering a record name if a matching record already exists in a worksheet column (Column "D"). If FindAll determines a matching string is already in the column, I want to disable the save button, change the background of the textbox containing the duplicate record string, and display an informational label asking the user to change offending record entry. The problem that I'm having is that FindAll is not working as expected although I've confirmed a string matching the new record entry exists in the target worksheet column. Could someone please explain to me why my FindAllMatches subroutine doesn't work as expected:

Const sDefaultRecordMessage As String = "Enter record name."

Private Sub tbRecord_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    'Calls the FindAllMatches routine as user types text in the textbox
    Call FindAllMatches
End Sub

Private Sub tbRecord_Enter()
    With Me.tbRecord
        If .Text = sDefaultRecordMessage  Then .Text = vbNullString
    End With
End Sub

Private Sub tbRecord_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    With Me.tbRecord
        If .Text = vbNullString Then .Text = sDefaultRecordMessage 
    End With

    Call FindAllMatches
End Sub

Private Sub FindAllMatches()
    Dim SearchRange As Range
    Dim FindWhat As Variant

    FindWhat = Trim(Me.tbRecord.Value)

    Set SearchRange = ActiveSheet.Range("D3").End(xlDown)

    'Calls Chip Pearson's FindAll function
    Set FoundCells = FindAll(SearchRange:=SearchRange, _
                            FindWhat:=FindWhat, _
                            LookIn:=xlValues, _
                            LookAt:=xlPart, _
                            SearchOrder:=xlByColumns, _
                            MatchCase:=True, _
                            BeginsWith:=vbNullString, _
                            EndsWith:=vbNullString, _
                            BeginEndCompare:=vbTextCompare)
    If FoundCells Is Nothing Or Len(Me.tbRecord.Value) = 0 Then
        Me.lblDuplicateMessage.Visible = False
        Me.cbSaveRecord.Enabled = True
        Me.tbRecord.BackColor = &H80000005
    Else
        Me.lblDuplicateMessage.ForeColor = RGB(255, 0, 0)
        Me.lblDuplicateMessage.Visible = True
        Me.tbRecord.BackColor = RGB(255, 204, 204)
        Me.cbSaveRecord.Enabled = False
    End If
End Sub

What am I doing wrong?

Answer 1

ActiveSheet.Range("D3").End(xlDown) sets the search range to a single cell...

However, since you don't really need to count the number of occurences, it would be faster/easier to use Match() on the entire column:

If Not IsError(Application.Match(Trim(Me.tbRecord.Value), _
                                 ActiveSheet.Range("D:D"), 0)) Then
    'have an existing match in ColD
End If