restricting duplicate entries

[mrmarc]

I have all these textboxes in my userform:

Txt3.Value
Txt4.Value
Txt5.Value
Txt6.Value
Txt7.Value
Txt8.Value
Txt9.Value
Txt10.Value
Txt11.Value
Txt12.Value
Txt13.Value
Txt14.Value
Txt15.Value
Txt16.Value
Txt17.Value
Txt18.Value
Txt19.Value
Txt20.Value
Txt21.Value
Txt22.Value
Txt23.Value
Txt24.Value
Txt25.Value
Txt26.Value
Txt27.Value
Txt28.Value
Txt29.Value
Txt30.Value
Txt31.Value
Txt32.Value
I have an idea on how to restrict any of these from being the same, but my way seems super inefficient and it will take me a long time.

This code will be put in a command button click.

Any ideas on a simple bit of code?

 

[mikerickson]

Perhaps,

Dim txtArray(3 To 32) As String
Dim  I As Long, flag as boolean

Dim txtArray(1 To 3) As String
Dim i As Long, flag As Boolean

flag = False

For i = 3 to 32
    With Me.Controls("TextBox" & i)
        flag = IsNumeric(Application.Match(Chr(5) & .Text, txtArray, 0)) Or flag
        txtArray(i) = Chr(5) & Me.Controls("Txt" & i).Text
    End With
Next I

If flag Then
    MsgBox "There is a duplicate"
Else
    MsgBox "no dup"
End If

 

[mrmarc]

Thanks for the reply.

Im getting a duplicate declaration error.

Also, should the "With Me.Controls("TextBox" & i)" be "With Me.Controls("Txt" & i)?

Thanks!

 

[mikerickson]

Yes, it should be "Txt". I think this version has the syntax errors removed.

Dim txtArray(3 To 32) As String
Dim  I As Long, flag as boolean

flag = False

For i = 3 to 32
    With Me.Controls("Txt" & i)
        flag = IsNumeric(Application.Match(Chr(5) & .Text, txtArray, 0)) Or flag
        txtArray(i) = Chr(5) & .Text
    End With
Next I

If flag Then
    MsgBox "There is a duplicate"
Else
    MsgBox "no dup"
End If

 

[mrmarc]

Alright seems to be working. We just need to deal with text boxes that have no value. So if two or more textboxes are left empty the code says "there are duplicates." I need to let this pass, because the userform doesn't always have to have all text boxes filled.

Any ideas? Thanks for the help already. I REALLy appreciate it.

Yes, it should be "Txt". I think this version has the syntax errors removed.

Dim txtArray(3 To 32) As String
Dim  I As Long, flag as boolean

flag = False

For i = 3 to 32
    With Me.Controls("Txt" & i)
        flag = IsNumeric(Application.Match(Chr(5) & .Text, txtArray, 0)) Or flag
        txtArray(i) = Chr(5) & .Text
    End With
Next I

If flag Then
    MsgBox "There is a duplicate"
Else
    MsgBox "no dup"
End If

 

[mikerickson]

Change this line

flag = ((0 < Len(.Text)) And IsNumeric(Application.Match(Chr(5) & .Text, txtArray, 0))) Or flag