Validating a unique reference in a textbox

[Blondegirl]

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

 

[Guest]

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

 

[Blondegirl]

Thanks for your assistance Tom, but it now appears to be doing th
opposite! When I input a number that I know already exists it isn'
noticing it.

(Searching above the A5 won't affect anything, the only reason I hav
it like that on my other 'find' code is because the reference number
happen to begin from A5 on the sheets.)

Also, I had forgotten to write something to connect the search to th
relevant textbox eg, Textbox1 = Findstring Should that affect i
here? (It doesn't work when I add it.) If so , what should th
correct code be?

Many thanks

 

[Guest]

Yes, you have to set FindString to the value in the Textbox.

this worked fine for me with Textbox1 being on a userform:

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
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
Findstring = TextBox1.Text
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"
Cancel = True
Exit Sub
End If

Next

End Sub

 

[Blondegirl]

The 'Findstring = TextBox1.Text' did the trick. Thank you very much for
your time, it works a dream now!