CODEWITHSUNDEEP
excelvba
amein
amein

Reputation: 115

Check duplicate on multiple userform textbox value

Im still working to improve my library booklist. This is previous question to check duplicate value while user entering the details (https://stackoverflow.com/a/60014470/6409413).

While Im testing this, I thinking its better and faster to create a "templist" on sheet2, then click a button on the userform to get all the details instead of typing or copy/pasting into the userform textbox. So,

Private Sub GetButton_Click()
    With Me
    .TitleTextBox.Value = Cells(ActiveCell.Row, "B").Value
    .AuthorTextBox.Value = Cells(ActiveCell.Row, "C").Value
    .CopyTextBox.Value = Cells(ActiveCell.Row, "D").Value
    .ISBNTextBox.Value = Cells(ActiveCell.Row, "E").Value
    .CallNoTextBox.Value = Cells(ActiveCell.Row, "F").Value
    .PublicationTextBox.Value = Cells(ActiveCell.Row, "G").Value
 End With
 ''Selection.EntireRow.Font.Strikethrough = True
End Sub

Then, once all the textbox filled, I want to check for the duplicate data on the main list. So, I try something like below:

Private Sub CheckButton_Click()
Dim FoundCell As Range
Dim Search As String
Dim ws As Worksheet
Set ws = Worksheets("booklist")
Search = TitleTextBox.Text
Set FoundCell = ws.Columns(2).Find(Search, LookIn:=xlValues, Lookat:=xlWhole)
On Error GoTo ErrMsg
If FoundCell Is Nothing Then
    Title_checker.Caption = ChrW(&H2713)
Else
Title_checker.Caption = "Duplicate" & " " & FoundCell.Address

End If
FoundCell.Select
Exit Sub
ErrMsg:
MsgBox "Select booklist sheet!"
End Sub

Im learned from previous question, Im only can "find" once each time. But, I want to check on three data for the duplicate value which is title on column B, ISBN on column E and CallNo at column F. Im not sure how to implement this answer (https://stackoverflow.com/a/60014470/6409413) on my previous question into my new "CheckButton". COuld someone help?

Upvotes: 0

Views: 616

Answers (1)

Tim Williams
Tim Williams

Reputation: 166341

Using the same Sub from the previous question:

Private Sub CheckButton_Click()

    DupCheck TitleTextBox.Text, 2, Title_checker 

    DupCheck ISBNTextBox.Text, 5, ISBN_checker

    DupCheck CallNoTextBox.Text, 6, CallNo_checker

End Sub

Sub DupCheck(txt, ColNo As Long, theLabel As Object)
    Dim m
    With Worksheets("booklist")
        m = Application.Match(txt, .Columns(ColNo), 0)
        If Not IsError(m) Then 
            theLabel.Caption = "Duplicate" & " " & .Cells(m, ColNo).Address
            .Activate         '<< added: select the sheet
            .Rows(m).Select
        Else
            theLabel.Caption = ChrW(&H2713)
        End If
    End With
End Sub

Upvotes: 1

Related Questions