Check entry in table from userform for duplicate

[Kappes105]

Hello to all ,
I am posting here for the first time. i am from Germany, please forgive my bad English. I have the following problem:

I have a userform. The data is then inserted into the table via Public Function Data_Write (Code below). This works also perfectly.

How can I prevent duplicate data records from being entered? I want to check for duplicates before writing to the table. I want to check ALL columns.
I want to use "check if duplicate" also for other forms, so write it in a function.

If the data is already available, a message should appear "New Data are written". If data has been changed, only the change should be saved an a message should appear "data were changed".

Does anyone have a suggestion for me how I could realize this?
Many thanks for the support.

See my Function Code Data_Write

'Call from the respective form via button OK
'Transfer table name and form name
'In the form the tab Userform Tag must be set so that the columns are written continuously
'--------------------------------------------------------------------------------------------------------------------
'--------------------------------------------------------------------------------------------------------------------
Sub Write_Data(wkstab As Worksheet, UFName As Object)

Dim objControl As Control 'all controls from the userform
Dim FirstEmtyRow As Long 'For first empty row in descriptive table

Dim i As Long 'loop counter for column entry

FirstEmtyRow = wkstab.UsedRange.Rows.Count + 1 'Set first free line in the used area

For Each objControl In UFName.Controls
If IsNumeric(objControl.Tag) Then 'If the ControlTag is numeric then entry in olumn
wkstab.Cells(FirstEmtyRow, objControl.TabIndex + 1) = objControl.value
End If
Next objControl

End Sub

 

[Anthony47]

1) I think that using UsedRange to determine which is the last used range is weak, as UsedRange is badly managed in Excel.
It would be better use a "pilot column" (a column that is for sure filled in any row) and thus use

FirstEmtyRow = wkstab.Cells(Rows.Count, PilotColumnNumber).End(xlp).Row + 1

If you don't have a pilot column then you might use something like

FirstEmtyRow = 0
On Error Resume Next
FirstEmtyRow = wkstab.Range("B:J").Find(What:="*", After:=Range("B1"), _
          SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
On Error GoTo 0
FirstEmtyRow = FirstEmtyRow + 1

This will get the information looking in the specified columns; B:J in this example.

2) I seem that your question is "How do I prevent user to input the same value in the range", but I missed what to do after.
In general I suggest to work with something like:

For Each objControl In UFName.Controls
    If IsNumeric(objControl.Tag) Then 'If the ControlTag is numeric then entry in olumn
    If Application.WorksheetFunction.CountIf(wkstab.Range("B1:J1").Resize(FirstEmtyRow), dd) = 0 Then     'I used B1:J1 because the same columns were used for 1st free line search, above
        'What to do if that entry is not yet in the table??
        wkstab.Cells(FirstEmtyRow, objControl.TabIndex + 1) = objControl.Value  '??
    Else
        'What to do if the value is already in the table??
        MsgBox ("The value is already in the table")        '??
    End If
    End If
Next objControl

Bye

 

[Kappes105]

Hello Anthony

thank you very much for your answer.

If the complete line is already exist there it can only be an update.

I get an error message in the row

Application.WorksheetFunction.CountIf(wkstab.Range("B1:J1").Resize(FirstEmtyRow), dd) = 0 Then

What does dd mean

Greetings from Germany

 

[Anthony47]

Ooopps...
I should have written =objControl.value instead of =dd (in the failing instruction)

If it still fails, when you get the error and are in debugmode, open the "Immediate Window" (typing Contr-g should do the job), then type in the window these two command (one after the other)

?FirstEmtyRow

?wkstab.Range("B1:J1").Resize(FirstEmtyRow).Address

Copy which are the responses and paste the info in your next message

Bye

 

[Kappes105]

Hello Anthony

unfortunately this does not work. I will therefore make the check afterwards and delete the line again and output a message that the record was entered twice or the data are already available.
Thank you very very much for your support.