Avoid duplicates when entering in a form
balou11
-
michel_m Posted messages 18903 Registration date Status Contributor Last intervention -
michel_m Posted messages 18903 Registration date Status Contributor Last intervention -
Hello everyone,
I’m presenting my problem (I tried to find a solution on the net, but in vain).
Here is my situation: I have a database in Excel. In column B is the last name, in column C the first name, and in column A I have a formula that concatenates the two.
Example: I enter in B: DUPONT
in C: ALAIN
In A: DUPONT ALAIN (this populates automatically)
But here’s the issue: I fill this database via a form (which works very well). I’d like, via VBA, to prevent duplicates; it should be impossible to re-enter a DUPONT ALAIN.
The check code should run after ALAIN is entered, because the user must enter the last name first.
Thank you in advance.
I’m presenting my problem (I tried to find a solution on the net, but in vain).
Here is my situation: I have a database in Excel. In column B is the last name, in column C the first name, and in column A I have a formula that concatenates the two.
Example: I enter in B: DUPONT
in C: ALAIN
In A: DUPONT ALAIN (this populates automatically)
But here’s the issue: I fill this database via a form (which works very well). I’d like, via VBA, to prevent duplicates; it should be impossible to re-enter a DUPONT ALAIN.
The check code should run after ALAIN is entered, because the user must enter the last name first.
Thank you in advance.
3 answers
-
Hello,
to be added in the module of the sheet:
Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) If Application.WorksheetFunction.CountIf(Range("B:B"), Target.Value) > 1 Then MsgBox "Nom déja employé" Target.Value = "" Target.Select End If End Sub
--
@+ Le Pivert -
First of all, thank you for the response Mr. "the woodpecker"!
But it doesn't work, I can easily create DUPONT PASCALE, the code should be able to look in column A which concatenates the last name and first name (DUPONT ALAIN).
The user should be able to enter "DUPONT" in the "name" field.
Then they should be able to enter the first name, but only if in column A, which concatenates both (last name and first name), a message should appear!
Thanks again to everyone for taking the time.-
Rectification for column A containing first name and last name Put in the userForm: Option Explicit Private Sub CommandButton1_Click() Dim DerniereLigneUtilisee As Long DerniereLigneUtilisee = Range("A" & Rows.Count).End(xlUp).Row + 1 Range("C" & DerniereLigneUtilisee).Value = TextBox1 'first name Range("B" & DerniereLigneUtilisee).Value = TextBox2 'last name Range("A" & DerniereLigneUtilisee).Value = Range("C" & DerniereLigneUtilisee).Value & " " & Range("B" & DerniereLigneUtilisee).Value End Sub to put in the concerned sheet: Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) If Application.WorksheetFunction.CountIf(Range("A:A"), Target.Value) > 1 Then MsgBox "Nom déjà employé" Target.Value = "" Range("B" & Target.Row) = "" Range("C" & Target.Row) = "" Target.Select End If End Sub Voilà
- Post your anonymized workbook on this site. Then paste the link in this post:
https://www.cjoint.com/
@+ Le Pivert
-
-
Hello
To enforce an input order (first name then last name or vice versa! ..)
use the textbox property: enabled
in the VBA help
as long as the "first name" is not filled in, you cannot type in the "last name"
-
If you’re not used to handling forms (userforms), you have this very detailed overview from SilKyroad, former Microsoft MVP
https://excel.developpez.com/cours/?page=prog#userform
-
