I would see you getting an error. After:=Range("A5")
should be qualified. Also, Find wraps around, so it will search above A5 as
well if that could cause your problem.

Dim Findstring As String
Dim Rng As Excel.Range
Dim Rng1 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
with ObjSheet
set rng1 = .Range(.Cells(5,1),.Cells(rows.count,1).End(xlup))
End With
Set Rng = rng1.Find(What:=Findstring, _
After:=rng1(1), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
MatchCase:=False)
If Not Rng Is Nothing Then
MsgBox "The reference you have entered already exists on
the database.", vbExclamation, "Box reference already exists"
TextBox1.SetFocus
Exit Sub
End If

Next

--
Regards,
Tom Ogilvy

"Blondegirl" wrote:


Hello, I am trying to do a data validation where the reference number
entered on a textbox on a form (which adds a record to the active
sheet) has to be unique and not already in the workbook. I already
have a find facility which can search selected sheets within the
workbook and I am trying to adapt this so that it will search the same
sheets and bring up a message box if the number already exists (ie a
match). I can't get it to work (it is bringing up the message box on
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 on
the database.", vbExclamation, "Box reference already exists"
TextBox1.SetFocus
Exit Sub
End If

Next
'rest of my coding for other things

Thanks.


--
Blondegirl
------------------------------------------------------------------------
Blondegirl's Profile: http://www.excelforum.com/member.php...o&userid=29615
View this thread: http://www.excelforum.com/showthread...hreadid=542849