Search duplicate before insert data using userform

[amein]

hi, Im creating a excel booklist for our library and im using userform to insert new entry of books details. could someone help me to fix my code to search duplicate for ISBN, title and call number. If this 3 entry no duplicate found, then only create/insert new booklist entry. So far im only managed to get this work for ISBN only.

Option Explicit

Private Sub CancelButton_Click()

Unload Me

End Sub


Private Sub ClearButton_Click()

Call UserForm_Initialize

End Sub

Private Sub OKButton_Click()

Dim FoundCell As Range
Dim Search As String
Dim emptyRow As Long
Dim i As Long
Dim tmpStr As String
Dim ws As Worksheet

''remove text for ISBN column
tmpStr = ISBNTextBox.Value
    
    For i = 1 To Len(tmpStr)
        Select Case Mid$(tmpStr, i, 1) '//examine current char
            Case "0" To "9" '//permitted chars
               '//ok
            Case Else
               Mid$(tmpStr, i, 1) = "!"
        End Select
    Next i
    tmpStr = Replace$(tmpStr, "!", "") '//strip invalids & return

''check duplicate for ISBN column
Set ws = Worksheets("booklist")
emptyRow = ws.Cells(Rows.Count, 5).End(xlUp).Offset(1, 0).Row
Search = ISBNTextBox.Text
Set FoundCell = ws.Columns(5).Find(Search, LookIn:=xlValues, Lookat:=xlWhole)
If FoundCell Is Nothing Then
MsgBox "No existing ISBN found, inserted new list!"

''create new entry
Cells(emptyRow, 1).Value = NoTextBox.Value
Cells(emptyRow, 2).Value = TitleTextBox.Value
Cells(emptyRow, 3).Value = AuthorTextBox.Value
Cells(emptyRow, 4).Value = CopyTextBox.Value
Cells(emptyRow, 5).Value = tmpStr
Cells(emptyRow, 6).Value = CallNoTextBox.Value
Cells(emptyRow, 7).Value = PublicationTextBox.Value

Else
MsgBox "ISBN exists!" & " data found at cell address " & FoundCell.Address
End If

If DeptCheckBox1.Value = True Then Cells(emptyRow, 8).Value = DeptCheckBox1.Caption

If DeptCheckBox2.Value = True Then Cells(emptyRow, 8).Value = Cells(emptyRow, 8).Value & " " & DeptCheckBox2.Caption

If DeptCheckBox3.Value = True Then Cells(emptyRow, 8).Value = Cells(emptyRow, 8).Value & " " & DeptCheckBox3.Caption


End Sub

Private Sub UserForm_Initialize()
''No. coulumn auto numbering
Dim LstRw As Long
LstRw = Cells(Rows.Count, "A").End(xlUp).Row
Me.NoTextBox.Value = Cells(LstRw, "A").Value + 1
NoTextBox.Value = Me.NoTextBox.Value
''Empty TextBox
TitleTextBox.Value = ""
AuthorTextBox.Value = ""
CopyTextBox.Value = ""
ISBNTextBox.Value = ""
CallNoTextBox.Value = ""
PublicationTextBox.Value = ""

DeptCheckBox1.Value = False
DeptCheckBox2.Value = False
DeptCheckBox3.Value = False

'Set Focus on NameTextBox
NoTextBox.SetFocus

End Sub

https://www.excelforum.com/attachmen...1&d=1580278127

[davesexcel]

If you work with the textbox_exit event for each textbox, then you will know if the value exists right away.

Private Sub TitleTextBox_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    Dim x
    x = Application.WorksheetFunction.CountIf(Range("B:B"), Me.TitleTextBox)
    If x > 0 Then
        MsgBox "Title exists, please enter a new title"
    End If

End Sub

[amein]

If you work with the textbox_exit event for each textbox, then you will know if the value exists right away.

Private Sub TitleTextBox_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    Dim x
    x = Application.WorksheetFunction.CountIf(Range("B:B"), Me.TitleTextBox)
    If x > 0 Then
        MsgBox "Title exists, please enter a new title"
    End If

End Sub

Thank you for your reply, I modified little bit and come out with this.

Private Sub TitleTextBox_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    Dim title
    title = Application.WorksheetFunction.CountIf(Range("B:B"), Me.TitleTextBox)
    If title > 0 Then
        Title_checker.Caption = "Duplicate"
    Else
        Title_checker.Caption = ChrW(&H2713)
    End If

End Sub

Attachment 660266

Then, now, I want to add a button so that once we found the duplicate, then we can click on that button and directly go to the cell of the duplicate data.

I try something like this but only works with 1 value which is Title. how to combine this code so that can work with ISBN and Call number too?

Or can we add into the first code for this purpose?

Private Sub Gotobutton_Click()
    Dim Finddupe, Rng As Range
    
    Set Rng = Worksheets("booklist").Range("B:B")
    Set Finddupe = Rng.Find(TitleTextBox.Text, LookIn:=xlValues, Lookat:=xlWhole)
    Application.GoTo Worksheets("booklist").Range(Finddupe.Address)

End Sub