Notify User of Duplicate Entry

[pahy96]

I am creating a personal finance tracker where a user enters their purchase information in order to track their daily/weekly/yearly spending. Upon submitting the User Form, the information simply populates a raw data source that feeds into many pivot tables and charts. For my own personal benefit, I would like a warning message to appear if the form submission produces the exact same row as the most current (or last) row in the raw data table. Here is the code I have for the Submission Button in the User Form:

Private Sub SubmitCommandButton_Click()

Dim emptyRow As Long


'Make Raw Data Sheet Active
Sheet4.Activate


'Determine emptyRow
emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1


'Transfer information
Cells(emptyRow, 3).Value = MonthComboBox.Value
Cells(emptyRow, 4).Value = DayTextBox.Value
Cells(emptyRow, 5).Value = YearTextBox.Value
'Cells(emptyRow, 5).Value = MonthComboBox.Value & " " & DayTextBox & ", " & YearTextBox
Cells(emptyRow, 6).Value = CategoryComboBox.Value
Cells(emptyRow, 7).Value = SubcategoryComboBox.Value
Cells(emptyRow, 8).Value = NotesTextBox.Value
Cells(emptyRow, 9).Value = PriceTextBox.Value
Cells(emptyRow, 10).Value = ConsumerComboBox.Value
Cells(emptyRow, 11).Value = WithdrawalTextBox.Value
Cells(emptyRow, 12).Value = DepositTextBox.Value


MsgBox "Submission Successful!"


End Sub

If a duplicate is submitted, I would like the user to be asked whether or not they would like to continue with the submission. If they click "no" a message box will appear saying "Submission cancelled." If they click "yes," a message box will appear saying "Submission Successful!"

 

[NoSparks]

If you don't mind writing the data to the sheet prior to the check for duplication and then deleting it if necessary you could add something like this ahead of the success message.

For i = 3 To 12
    If Cells(emptyRow, i).Value <> Cells(lr, i).Value Then
        Exit For
    Else
        Response = MsgBox("This submission is a duplicate" & vbCrLf & _
                          "of the previous submission." & vbCrLf & _
                          "Do you want to keep it?", 36, "DUPLICATE SUBMISSION")
        If Response = vbNo Then
            Rows(emptyRow).Delete
            MsgBox "Last Submission Removed"
            Exit Sub
        End If
    End If
Next i