Verrouiller le contenu d'une cellule après calcul

Bonjour tout le monde,

Je mets en place un classeur permettant de répertorier des fournisseurs. Pour cela, je dispose d'une formule qui attribue un code unique à chaque fournisseur, sous la forme XXX-N.

XXX représente les 3 premières lettres du nom du fournisseur, et le N est un chiffre (1 par défaut pour le premier fournisseur qui commence par ces 3 lettres), qui augmente si on veut enregistrer un nouveau fournisseur dont le nom commence par les 3 mêmes lettres.

Voici donc la formule que j'utilise en A2 :

=MAJUSCULE(GAUCHE(B2;3))&"-"&SOMMEPROD(N(GAUCHE($A$1:A1;3)=GAUCHE(B2;3)))+1

Cette formule permet d'augmenter le dernier chiffre du code si le début du code est déjà utilisé pour un autre fournisseur

Quand j'ajoute un fournisseur dont les 3 premières lettres du nom sont identiques à celles d'un fournisseur déjà enregistré.

Ici, tout se passe comme je le souhaite. Mais lorsque je trie par ordre alphabétique sur le nom des fournisseurs, comme le nouveau fournisseur se trouve avant les autres, le chiffre final des codes fournisseurs se trouve modifié (comparez sur les 3 "TES-N").

J'aimerais donc savoir si quelqu'un aurait une solution me permettant de bloquer définitivement le code attribué à chaque fournisseur, pour qu'il ne change pas quand j'en ajoute d'autres et que je trie.

Merci

Bonjour,

2 débuts de réponse avec 'tableaux' et 'plages nommées' (propositions identiques).

A tester dans ton environnement de travail. Voir les avantages et inconvénients.

1er. code (plage de données) :

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim nb As Variant

    If Not Application.Intersect(Target, Range("Tableau1[fnr]")) Is Nothing Then
        If Target.Count > 1 Or Target = "" Then Exit Sub

        ActiveWorkbook.Names("nb_fnr1").RefersToR1C1 = _
            "=SUMPRODUCT(--(LEFT(d.code_fnr,3)=LEFT(""" & Target & """,3)))"

        nb = Format(Evaluate("nb_fnr1") + 1, "00")

        Target.Offset(0, -1) = UCase(Left(Target, 3)) & "-" & nb
    End If

End Sub

Bonjour Jean-Eric et merci beaucoup pour ton aide

J'ai regardé ta solution, et après avoir supprimé quelques lignes j'ai tenté d'en entrer de nouvelles.

Le numéro attribué à Try n'est pas bon (2 au lieu de 1), et pour les entrées suivantes non plus.

Je joins mon classeur d'origine à ce message, si ça peut aider (feuille "Fournisseurs").

Re,

je n'ai pas envisagé la suppression de données dans la mesure ou ces éléments pouvaient être utiliser dans des formules (de recherche d'adresse, par exemple).

Donc j'ajoute, et jai aussi un historique des fournisseurs. Je ne supprime pas

Cdlt.

OK pour l'historique, ça peut être intéressant.

Mais le problème reste gênant : j'ai changé l'Alerte d'erreur dans la Validation des données. J'ai mis Arrêt à la place d'Informations, pour vraiment interdire les doublons (avec Informations, Excel affiche bien le message qui précise que l'entrée existe déjà dans la base, mais le contenu de la cellule reste et un nouveau code est quand même affecté).

Maintenant, quand j'essaye d'ajouter un fournisseur existant, Excel m'en empêche et me propose de taper autre chose. Mais quand je corrige mon entrée, le numéro affecté n'est pas bon. Dans l'exemple suivant, j'ai essayé d'ajouter un autre OBST et Excel m'a prévenu qu'il existait déjà. J'ai donc changé et ajouté "OBSTT". Excel accepte et affecte un code à ce fournisseur, mais il finit par 6 au lieu de 5. J'en déduis donc qu'Excel a quand même pris en compte la saisie abandonnée du nouveau "OBST", en lui affectant le code OBS-05 (bien qu'il ne l'affiche pas).

De plus, je remarque qu'on trouve 3 fois l'entrée "OBS" dans la liste déjà existante, alors que les doublons devraient être interdits. Enfin, ces doublons devraient au moins avoir le même code (OBS-01), alors qu'ils ont tous un code différent.

Merci pour le temps que tu me consacres

Je me permets un petit up, n'ayant toujours pas trouvé de solution à mon problème.

Si quelqu'un a une idée, je suis preneur

Bonne journée à tous !

Bonjour,

Désolé du retard pour intervenir.

Je regarde ce jour.

Re,

A tester.

Bonjour Jean-Eric,

Aucun problème pour le temps que tu as pris, tu as aussi une vie et tu fais ça bénévolement, et en plus le résultat est parfait cette fois !

J'espère qu'un jour je pourrai à mon tour aider les débutants, mais il va falloir encore du temps pour ça.

Merci beaucoup Jean-Eric pour ton coup de main et bonne journée

En fait j'ai un dernier petit problème (mais pas avec ton code) : quand j'adapte ton code sur ma feuille (j'utilise la méthode avec le tableau), je n'arrive pas à obtenir nb_fnr1 dans le gestionnaire des noms. J'imagine que ce morceau de code joue :

ActiveWorkbook.Names("nb_fnr1").RefersToR1C1 = _
"=SUMPRODUCT(--(LEFT(d.code_fnr,3)=LEFT(""" & Target & """,3)))"

Promis après ça j'ai fini

Re,

Envoie ton fichier.

Je regarderai

Lol je pensais pas avoir une réponse si rapidement ! Alors je veux bien que tu fasses la manip' sur la feuille "Test", et si possible que tu m'expliques comment tu fais stp.

Re,

Voir fichier.

Il suffisait de créer un nom 'nb_fnr' dans le classeur.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim nb As Variant
Dim nonDoublon As Boolean

    If Not Application.Intersect(Target, Range("Tableau6[Dénomination sociale]")) Is Nothing Then
        If Target.Count > 1 Or Target = "" Then Exit Sub

        nonDoublon = Application.CountIf(Range("Tableau6[Dénomination sociale]"), Target) = 1
        If Not nonDoublon Then
            MsgBox "Ce fournisseur existe déja.", 64
            Application.Undo
            Target.Clear
            Exit Sub
        End If
        ' Créer au préalable, un nom "nb_fnr" dans le classeur.
        ActiveWorkbook.Names("nb_fnr").RefersToR1C1 = _
        "=SUMPRODUCT(--(LEFT(d.code_fnr,3)=LEFT(""" & Target & """,3)))"

        nb = Format(Evaluate("nb_fnr") + 1, "00")

        Target.Offset(0, -1) = UCase(Left(Target, 3)) & "-" & nb
    End If

End Sub

Bonjour Jean-Eric,

J'avais bien essayé ça, mais quand j'essaye d'ajouter le nom "nb_fnr" depuis le gestionnaire des noms, si je ne mets rien dans le champ "Fait référence à", le nom ne se créée pas... Que faut-il écrire ici ?

Bonjour à tous,

J'ai pas envie d'embêter Jean-Eric pour un truc aussi simple, il m'a déjà bien aidé . Un de vous saurait-il comment je peux résoudre mon dernier petit problème ? J'imagine que c'est rien de méchant, mais je ne trouve pas la solution...

Bonne journée !

Bonjour,

Je up encore mon sujet, n'ayant toujours aucune réponse pour mon petit problème.

Ce n'est pas bloquant car Jean-Eric a déjà fait la manip' dans mon fichier, mais j'aimerais connaître la méthode pour pouvoir le refaire plus tard si besoin.

Je rappelle mon problème : comment ajouter un nom dans le Gestionnaire des noms sans renseigner le champ "Fait référence à" ? Le but étant de faire remplir ce champ de manière relative par un code VBA qui fait référence à ce nom.

Peut être qu'il faut créer le nom ailleurs que dans le Gestionnaire des noms (directement en VBA par exemple ?)...

Bonne journée à tous

Bonjour,

pour intialiser le nom, tu fais ="test"

A te relire.

Cdlt.

Bonjour Jean-Eric,

Un énorme merci pour ton aide, tu m'as bien dépanné.

Je viens enfin de réussir à faire fonctionner le nom.

En fait, plutôt que d'écrire ="test", il suffit de ne pas laisser vide le champ "Fait référence à" (un simple espace suffit). Tu peux me confirmer que c'est bien ça ?

Bonne journée tout le monde,

tg29