Mutually exclusive values

[scantor145]

Excel 2003
Visual Basic 6.3

I have a form with 24 text boxes. The names of the text boxes all
start with “txt”, e.g. txtCHOL, txtALB, etc. The values that are
entered into the text boxes can range from 1 to 24. Is there an easy
way (VB code) to check that each box has a different number, that is,
mutually exclusive.

Thanks

 

[Bob Phillips]

Dim tb As Control
Dim collNums As Collection
Dim cTBs As Long

Set collNums = New Collection
For Each tb In Me.Controls
If TypeName(tb) = "TextBox" Then
cTBs = cTBs + 1
On Error Resume Next
collNums.Add tb.Text, tb.Text
On Error GoTo 0
End If
Next tb
If collNums.Count < cTBs Then
MsgBox "All values must be unique"
End If


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)