Interdire dans la saisie dans une cellule non vide d'une colonne (clé primaire)

[rcan7412]

Bonjour et bonne année,

Le sujet a déjà été traité sur ce post :
https://www.excel-downloads.com/threads/interdire-la-saisie-dans-une-cellule-non-vide.50110/

Toutefois, je n'arrive pas à mes fins pour adapter mon code avec le Worksheet_Change.

Dans le cadre d'une saisie de clé primaire alphanumérique, je voudrais verrouiller (sans utiliser le verrouillage de la feuille) les cellules de la colonne A qui ont déjà des données encodées (N'autoriser l'encodage que dans les cellules vides).
L'idéal aurait d'avoir quand même la possibilité de déverrouiller et de reverrouiller en cas de faute de frappe avec un bouton par exemple (ou case à cocher).

J'ai déjà le code suivant qui détecte et élimine automatiquement les doublons (indispensable pour une clé primaire).

Private Sub Worksheet_Change(ByVal Target As Excel.Range)    Dim Colonne As Integer
    Dim Adresse As String
    
    Application.EnableEvents = False
    
    'On sort si plus d'une cellule a été modifiée
    If Target.Count > 1 Then Exit Sub
    'On sort si la cellule modifiée est vide
    If Target.Value = "" Then Exit Sub
    
    'Définit la colonne à vérifier (1=Colonne A, 2=colonne B ...etc...)
    Colonne = 1
    
    'Vérifie si c'est la colonne cible a été modifiée
    If Target.Column = Colonne Then
    
        'Recherche si la nouvelle donnée existe déjà dans la colonne.
        Adresse = Columns(Colonne).Find(What:=Target.Value, After:=Target.Offset(1, 0), LookAt:=xlWhole, _
            SearchDirection:=xlNext).Address
            
        'Si l'adresse de cellule trouvée ne correspond pas à la cellule modifiée, cela
        'signifie qu'il y a un doublon dans la colonne.
        If Adresse <> Target.Address Then
        
            MsgBox "La donnée '" & Target & "' existe déjà dans la cellule " & Adresse
            'Suppression de la donnée
            Target.Value = ""
            Target.Select
            
            ElseIf Target.Value <> "" Then MsgBox "ne pas modifier un clé existante"
        
        End If
    End If
   Application.EnableEvents = True
   
End Sub

A noter qu'en outre j'utilise la validation de données avec cette formule dans la colonne A : =ET(NBCAR(A2)<11;B2<>""). Car je veux limiter le nombre de caractères à 10 et n'accepter une valeur que si une donnée est déjà présente dans la cellule contiguë (B2)

Comment s'y prendre ?

Merci pour toute idée....

 

[rcan7412]

Re : Interdire dans la saisie dans une cellule non vide d'une colonne (clé primaire)

J'ai trouvé une alternative sans code vba pour empêcher d'écraser une clé primaire encodée en colonne A en conditionnant ce qui se trouve en colonne B.

En validation de données dans la colonne A, j'indique :

=ET(NBCAR(A2)<11;B2="";NB.SI($A:$A;A2)=1)

Ce qui implique :
- Un clé de 10 caractères maximum
- Je ne peux encoder une clé en colonne A que si la cellule contiguë en colonne B est vide (donc je ne sais modifier la clé en colonne A que si j'efface ce qui ce trouve en colonne B)
- le 3ème argument "NB.SI($A:$A;A2)=1", m'empêche de saisir un doublon dans la colonne A (indispensable pour l'usage d'une clé primaire).

Il me reste toutefois un problème pour lequel, je fait appel à l'équipe, pour sécuriser en plus la clé primaire, je souhaiterais empêcher d'effacer la clé en colonne A si la colonne B est "non-vide". Là je sèche. Sans faut-il passer par le vba ?

En annexe le fichier test.

Bonne journée

 

[rcan7412]

Re : Interdire dans la saisie dans une cellule non vide d'une colonne (clé primaire)

Bonjour,

A défaut de réponses, j'ai cherché, cherché, cherché... et enfin trouvé
Et comme j'aime trouver l'info quand je cherche, j'en profite pour partager à qui en aurait besoin, tout l'esprit de cet excellent forum.

Je me suis inspiré en définitive du code très simple de Hervé
https://www.excel-downloads.com/threads/interdire-la-saisie-dans-une-cellule-non-vide.50110/


Voici les bases :
2 colonnes

La première (A) contient la clé primaire
La seconde (B) le nom "libre"

Dans la colonne A, j'indique dans les cellules la validation de données personnalisée suivante :
=ET(NBCAR(A2)<11;B2="";NB.SI($A:$A;A2)=1)


(bis) Ce qui implique :
- Un clé de 10 caractères maximum
- Je ne peux encoder une clé en colonne A que si la cellule contiguë en colonne B est vide. Ce n'était pas mon option en premier lieu mais avec la protection en vba, j'ai changé d'optique.
- le 3ème argument "NB.SI($A:$A;A2)=1", m'empêche de saisir un doublon dans la colonne A (indispensable pour l'usage d'une clé primaire).

Enfin la protection de la clé primaire en colonne A, à mettre en vba dans la feuille concernée :

Option Explicit


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If CheckBox1 Then Exit Sub
If Target.Column = 1 And Target <> "" Then
    MsgBox "Et coco! ta cellule est pas vide alors va voir ailleurs si j'y suis!"
    Cells(Target.Row, 2).Select
    End If
End Sub

C'est somme toute assez simple mais bigrement efficace.

Pour mon application je l'ai bien sûr agrémentée avec d'autres messages de saisie et d'erreurs.

Cela ne remplace pas les protections des véritables clés primaires des bases de données comme ACCESS mais là n'est pas le but. C'est surtout un outil mono-utilisateur pour éviter les erreurs de saisie à l'usage.

Je reste toujours intéressé par d'autres solutions le cas échéant.

Par exemple, quand on procède à la saisie d'une nouvelle clé en colonne A, il indique le message d'erreur (vba), alors qu'il n'a lieu d'être pour une première saisie, mais bon là j'abandonne.

Le modèle est en annexe.

Bonne journée !

 

[rcan7412]

Re : Interdire dans la saisie dans une cellule non vide d'une colonne (clé primaire)

Encore un détail pour éviter l'erreur : 'Erreur d’exécution 13 Incompatibilité de type' si on sélectionne plusieurs cases (évoqué dans le post d'origine) :

Option Explicit


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If CheckBox1 Then Exit Sub


    'On sort si plus d'une cellule a été modifiée
    If Target.Count > 1 Then Exit Sub
    'On sort si la cellule modifiée est vide
    If Target.Value = "" Then Exit Sub


If Target.Column = 1 And Target <> "" Then
    MsgBox "Et coco! ta cellule est pas vide alors va voir ailleurs si j'y suis!"
    Cells(Target.Row, 2).Select
    End If
End Sub

Et voilà