Validating a unique reference in a textbox

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.

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
>
>

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

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

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

Many thanks.

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

--
Regards,
Tom Ogilvy



"Blondegirl" wrote:

>
> Thanks for your assistance Tom, but it now appears to be doing the
> opposite! When I input a number that I know already exists it isn't
> noticing it.
>
> (Searching above the A5 won't affect anything, the only reason I have
> it like that on my other 'find' code is because the reference numbers
> happen to begin from A5 on the sheets.)
>
> Also, I had forgotten to write something to connect the search to the
> relevant textbox eg, Textbox1 = Findstring Should that affect it
> here? (It doesn't work when I add it.) If so , what should the
> correct code be?
>
> Many thanks.
>
>
> --
> Blondegirl
> ------------------------------------------------------------------------
> Blondegirl's Profile: http://www.excelforum.com/member.php...o&userid=29615
> View this thread: http://www.excelforum.com/showthread...hreadid=542849
>
>

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