[ 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