Hello, I am trying to do a data validation where the reference numbe
entered on a textbox on a form (which adds a record to the activ
sheet) has to be unique and not already in the workbook. I alread
have a find facility which can search selected sheets within th
workbook and I am trying to adapt this so that it will search the sam
sheets and bring up a message box if the number already exists (ie
match). I can't get it to work (it is bringing up the message box o
any number I input) & would appreciate if someone could help me on it!
Here is the relevant part of my coding:

Dim Findstring As String
Dim Rng As Excel.Range
Dim mysheets As Excel.Sheets
Dim objsheet As Excel.Worksheet

Set mysheets = Worksheets(Array("Sheet 2", "Sheet 3", "Sheet 4", _
"Sheet 5", "Sheet 10", "Sheet 11"))

For Each objsheet In mysheets
Set Rng = objsheet.Columns("A").Find(What:=Findstring, _
After:=Range("A5"), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
MatchCase:=False)
If Not Rng Is Nothing Then
MsgBox "The reference you have entered already exists o
the database.", vbExclamation, "Box reference already exists"
TextBox1.SetFocus
Exit Sub
End If

Next
'rest of my coding for other things

Thanks

--
Blondegir
-----------------------------------------------------------------------
Blondegirl's Profile: http://www.excelforum.com/member.php...fo&userid=2961
View this thread: http://www.excelforum.com/showthread.php?threadid=54284