VBA check multiple textbox entries against list of data for duplicates

[Laurawillow]

Hi
I am trying to write some code which will check text entered in each of 5 textboxes on a form, against a column of data already entered, starting cell B2.
I'm a bit of a newbie so please excuse if my terminology isn't very good! I can get the result I want checking one textbox against one cell, but don't know how to code to say check all textboxes against all entries in the column without my Excel just getting stuck in a loop.

I've been working on variations of:

Dim x
Dim dup As Boolean


x = 2


If TextBox2.Value = Cells(x, 2) Then
dup = True


x = x + 1
Do Until x = 1000
Loop


End If


If dup = True Then
MsgBox "Duplicate found. Please check and re-enter"
Else
MsgBox "No duplicates found."

End If

However, this overwrites dup each time so will only work if the duplicate is in the last cell it checks, so need to add a End Sub. And then make it neater, quicker, and go through and check textbox 3-6 in the same way.
Or there is a totally better and more sensible way of doing this with some sort of match function, though I haven't been able to find one.

Any help really appreciated, before I throw my computer out of the window!

Many thanks
Laura

 

[MickG]

Perhaps this in your Userfrom Module:-

Private Sub CommandButton1_Click()
Dim TBox As Control
Dim Rng As Range, Dn As Range
Set Rng = Range(Range("B2"), Range("B" & Rows.Count).End(xlUp))
For Each TBox In Me.Controls
 If TypeName(TBox) = "TextBox" And TBox.Value <> "" Then
    If Application.CountIf(Rng, TBox.Value) > 0 Then
        MsgBox "Match found in " & TBox.Name
    End If
  End If
Next TBox
End Sub

 

[Laurawillow]

Thanks for speedy reply
I've tried this but get Run-time error 438, Object does not support this property or method.
It's objecting to:
If TypeName(TBox) = "TextBox" And TBox.Value <> "" Then

The Microsoft explanation of error 438 makes absolutely no sense to me:
Not all objects support all properties and methods. This error has the following cause and solution:

• You specified a method or property that doesn't exist for this Automation object.
  
  Any ideas?

 

[dmt32]

Hi try this & see if helps:

Private Sub CommandButton1_Click()
    If NoDuplicates(Form:=Me, sh:=Sheets("Sheet1")) Then
        MsgBox "No duplicates found."
    Else
        MsgBox "Duplicate found. Please check and re-enter"
    End If
End Sub




Function NoDuplicates(ByVal Form As Object, ByVal sh As Object) As Boolean
    Dim m As Variant
    Dim Col As Range
    Dim i As Integer
    
    Set Col = sh.Range(sh.Range("B2"), sh.Range("B" & sh.Rows.Count).End(xlUp))
    
    For i = 1 To 5
        m = Application.Match(Form.Controls("TextBox" & i).Text, Col, False)
        If Not IsError(m) Then Form.Controls("TextBox" & i).SetFocus: Exit Function
    Next i
        NoDuplicates = True
End Function

Code assumes that your TexBoxes are named TextBox1 to TextBox5
Change sheet name data resides in where shown in RED

If duplicate found your message should be shown & Focus set to the textbox.

Hope Helpful

Dave

 

[Laurawillow]

This is really helpful. Not only does it work, but I can use the code for looking at multiple textboxes elsewhere (I'm trying to create data entry forms and so have a lot of boxes which I have been naming individually as I didn't know how to do it more neatly. Thanks so much - you have saved my afternoon

(Your guide on what to amend to my code was perfect, really easy even for me!)

Hi try this & see if helps:

Private Sub CommandButton1_Click()
    If NoDuplicates(Form:=Me, sh:=Sheets("Sheet1")) Then
        MsgBox "No duplicates found."
    Else
        MsgBox "Duplicate found. Please check and re-enter"
    End If
End Sub




Function NoDuplicates(ByVal Form As Object, ByVal sh As Object) As Boolean
    Dim m As Variant
    Dim Col As Range
    Dim i As Integer
    
    Set Col = sh.Range(sh.Range("B2"), sh.Range("B" & sh.Rows.Count).End(xlUp))
    
    For i = 1 To 5
        m = Application.Match(Form.Controls("TextBox" & i).Text, Col, False)
        If Not IsError(m) Then Form.Controls("TextBox" & i).SetFocus: Exit Function
    Next i
        NoDuplicates = True
End Function

Code assumes that your TexBoxes are named TextBox1 to TextBox5
Change sheet name data resides in where shown in RED

If duplicate found your message should be shown & Focus set to the textbox.

Hope Helpful

Dave

 

[dmt32]

This is really helpful. Not only does it work, but I can use the code for looking at multiple textboxes elsewhere (I'm trying to create data entry forms and so have a lot of boxes which I have been naming individually as I didn't know how to do it more neatly. Thanks so much - you have saved my afternoon

(Your guide on what to amend to my code was perfect, really easy even for me!)

Hi,
Many thanks for your kind & generous feedback it is very much appreciated - glad solution worked ok for you.

Dave