Check row for multiple values from Excel

I am making a data entry form through VBA excel. I am very new at this and was unable to get any meaningful search results as I was unsure of the name of what I was trying to do.

Anyway, the form I am making is for users to enter data collected from running audits and when "Save" button is clicked, the data from the text boxes and combo boxes is written to a new row in an excel workbook. In my form there are four fields that identify each data entry (Year, Quarter, Day, Ward) followed by the rest of the data. I am trying to get some code to first check if there is a row with the same values already in the workbook to prevent duplicate entries.

e.g. The spreadsheet contains the following data - YEAR -- QUARTER---- DAY--- WARD


2013----------1--------- Monday -- ICU 2013----------2--------- Monday -- ICU 2013----------3--------- Monday -- ICU 2013----------4--------- Monday -- ICU 2014----------1--------- Monday -- ICU

And a user enters a new entry with the following values

Year - 2014 Quarter - 1 Day - Monday Ward - ICU

I need some code that would give a result (Msgbox saying duplicate entry etc)

but if they entered one that was

Year - 2014 Quarter - 2 Day - Monday Ward - ICU

All is ok and they are able to click "save"

I appreciate your help (sorry for the dumb question!)

Answers


Here's a code fragment (Function IsDuplicate()) which loops through a range until the first blank row (to be precise to the first row where the fist column is empty) and compares 4 key fields with 4 parameters you are providing.

You can call this from your Save button and, depending on the outcome, proceed with writing and closing the form or refuse the writing and display a MsgBox.

Sub Test()
Dim DTab As Range

    Set DTab = ActiveSheet.[B3]  ' define start of data table

    If IsDuplicate(DTab, 1, 1, 1, 1) Then     ' your form input here instead of 1,1,1,1
        MsgBox ("duplicate")
        ' stay in form
    Else
        MsgBox ("unique")
        ' write record and close form
    End If
End Sub

Private Function IsDuplicate(DRange As Range, P1 As Variant, P2 As Variant, P3 As Variant, P4 As Variant) As Boolean
Dim Idx As Long

    IsDuplicate = False
    Idx = 2                         ' 1st row is header

    Do While DRange(Idx, 1) <> ""   ' break at 1st blank row
        If DRange(Idx, 1) = P1 And _
           DRange(Idx, 2) = P2 And _
           DRange(Idx, 3) = P3 And _
           DRange(Idx, 4) = P4 Then
            IsDuplicate = True
            Exit Do
        End If
        Idx = Idx + 1
    Loop

End Function

You will need this function quite often .... sometimes you want to return Idx instead of just a True/False to return the number of the record - if you must decide whether to Insert/Append or Update a record.


Need Your Help

Delphi LoadLibrary Failing to find DLL other directory - any good options?

delphi dll path loadlibrary

Two Delphi programs need to load foo.dll, which contains some code that injects a client-auth certificate into a SOAP request. foo.dll resides in c:\fooapp\foo.dll and is normally loaded by c:\foo...

Links between org files

emacs org-mode

Say I have two org files, file1.org and file2.org on disk (let's assume they are in the same directory). How can create a link on file1.org to file2.org?