Mettre à jour les valeurs d'une Data Validation si la liste source est modifiée

[bouclesdor]

Bonjour à tous,

Je vais appel à vous ce matin car j'ai fouillé sur le net pour trouver un code VBA qui me permettra de mettre à jour automatiquement les valeurs d'une data validation car ma liste source sera appelé à être modifié régulièrement et je veux que les changements ce reflete dans ma feuille "projet" qui est ma feuille contenant les données. Ma liste est dans la feuille "regroupement".

Voici le code que j'ai trouvé qui semblait bien fonctionner jusqu'à ce que je fasse des tests et me rendre compte d'un bug.

Option Explicit
' Developed by Contextures Inc.
' Contextures Excel Tips, Excel Tutorials and Excel Examples
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo errHandler

Dim rng As Range
Dim strOld As String
Dim strNew As String
Dim wsData As Worksheet
Dim wsLists As Worksheet

Set wsLists = Sheets("Regroupement") ' feuille qui contient la liste
Set wsData = Sheets("Projet") 'feuille nommé projet celle qui contient les datavalidations
Set rng = wsLists.Range("liste") 'nom de la plage de liste

If Target.Count > 1 Then GoTo exitHandler

If Intersect(Target, rng) Is Nothing Then
'do nothing
Else
Application.EnableEvents = False
strNew = Target.Value
Application.Undo
strOld = Target.Value
Target.Value = strNew

If strOld <> "" Then
' G est la colonne avec la datavalidation sur la feiulle projet
wsData.Columns("g:g").Replace What:=strOld, _
Replacement:=strNew, LookAt:=xlPart, _
SearchOrder:=xlByRows
End If
Application.EnableEvents = True
End If

exitHandler:
Application.EnableEvents = True
Exit Sub

errHandler:
MsgBox "Change could not be completed"
GoTo exitHandler
End Sub


Le bug est que lorsque je remplace le mot en B4 (qui était fraise) par la lettre "E" tout simplement et lorsque j'ai remodifié la case en remplacant le "E" par "coucou" ça changé tous les "E" dans tout les mots. Je voulais que ça change seulement les cellules qui contenait le E pas les E de tous les mots... Pouvez-vous me dire quoi changer dans mon code...

PS je vais joindre la pièce jointe dans 2 minutes... j'arrive pas à la joindre pour le moment et je ne veux pas perdre mon message...

Merci à l'avance!

Bouclesdor

 

[bouclesdor]

Re : Mettre à jour les valeurs d'une Data Validation si la liste source est modifiée

voilà la pièce jointe. elle était trop grosse j'ai donc fait un p'tit ménage...

Merci beaucoup...

 

[BOISGONTIER]

Re : Mettre à jour les valeurs d'une Data Validation si la liste source est modifiée

Bonjour,

http://boisgontierjacques.free.fr/fichiers/DonneesValidation/DVMaj.xls

JB

 

[Misange]

Re : Mettre à jour les valeurs d'une Data Validation si la liste source est modifiée

Bonjour

tu n'as aucun besoin de macro pour faire cela.
Il te suffit de définir un nom (onglet formule) basé sur un tableau :
sur ta feuille regroupement, j'ai mis un tableau (onglet accueil/style/mettre sous forme de tbleau.
Tout ce que tu écris dessous s'y incorpore automatiquement (et se retrouvera dans ta liste de validation).
Tu sélectionnes la colonne famille en positionnant le curseur en haut de la colonne
onglet formule/insérer un nom tu mets par exemple famille_produits
ensuite dans l'autre feuille : données/validation /liste et comme nom tu entre = famille_produits
Essaie !

 

[bouclesdor]

Re : Mettre à jour les valeurs d'une Data Validation si la liste source est modifiée

Merci pour vos réponses je vais testé ça tantôt!!

Bonne journée,

Bouclesdor

 

[BOISGONTIER]

Re : Mettre à jour les valeurs d'une Data Validation si la liste source est modifiée

Bonjour,

Voir PJ

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect([liste], Target) Is Nothing And Target.Count = 1 Then
    Application.EnableEvents = False
    ValSaisie = Target.Value
    Application.Undo
    AncVal = Target
    For i = 1 To [listeChoix].Count
      If Sheets("projet").Range("listeChoix")(i) = AncVal Then Sheets("projet").Range("listeChoix")(i) = ValSaisie
    Next i
    Target = ValSaisie
   Application.EnableEvents = True
  End If
End Sub

liste =DECALER(Regroupement!$B$2;;;NBVAL(Regroupement!$B:$B)-1)
ListeChoix =DECALER(Projet!$G$2;;;NBVAL(Projet!$G:$G)-1)


JB

 

[bouclesdor]

Re : Mettre à jour les valeurs d'une Data Validation si la liste source est modifiée

Misange: je viens de tester votre fichier et voici ce que ça m'a donné après avoir fait des tests... j'ai le même bug qui change des mots dans des lignes qu'il ne devrait pas. le mot "truc" est partout...

 

[Misange]

Re : Mettre à jour les valeurs d'une Data Validation si la liste source est modifiée

Qu'est ce que tu as fait exactement ?
Si tu changes orange par truc sur la feuille regroupement ou si tu ajoutes machin, dans l'autre feuille, dans le menu déroulant les choix qui te sont proposés changent. Mais bien sur les valeurs déjà entrées ne vont pas changer. Tu as fait un rechercher remplacer ??

 

[bouclesdor]

Re : Mettre à jour les valeurs d'une Data Validation si la liste source est modifiée

Bonjour Misange,

Merci de votre aide très précieuse. ce que j'ai fait en test c'est changer le mot au complet dans ma liste "regroupement" et j'ai aussi fait un test de modifié le texte car mon but est que ma liste ne fera pas seulement s'agrandir mais je veux aussi qu'elle modifie mes valeurs déjà entrés dans "projet". Bref je veux que si j'ajoute un complément d'information à coté d'un mot (ex: orange délicieuse) que partout dans ma feuille projet où il y avait seulement le mot orange qu'il soit maintenant inscrit "orange délicieuse"...

Suis-je plus claire?

Merci encore de votre aide! c'est grandement apprécié!

PS BoisGonthier je viens de faire un test extra rapide et ça semble bien fonctionner! Je vais diner et revient pour retester tout ça! Merci à toi aussi de ton aide!

Bouclesdor

 

[Misange]

Re : Mettre à jour les valeurs d'une Data Validation si la liste source est modifiée

Alors là c'est sur qu'en aucune façon la modification de la source des données utilisées pour une liste de validation ne peut modifier les valeurs déjà entrées avec cette liste. Ca ne peut se faire que par macro
Si tu modifies Orange par Orange délicieuse, ça ne modifiera que les valeurs proposées pour les prochaines saisies. C'est en général le comportement qui est souhaité. Si la solution de jacques te convient, c'est parfait alors. Bon appétit

 

[bouclesdor]

Re : Mettre à jour les valeurs d'une Data Validation si la liste source est modifiée

Merci Misange pour ton aide! Ça serait pratique qu'il y ait un bouton dans excel à cocher si on veut appliquer les modifications à tous les données déjà inscrites ou bien seulement au nouvelles données mais bon d'ici là on va travailler avec les codes vba!

Boisgontier: Donc me revoilà avec mon "retest" tout semble fonctionner à merveille!!
Un immense merci Boisgontier! Tu es le pros des macros!

Bonne journée et au plaisir...

Bouclesdor

 

[thombzh]

Re : Mettre à jour les valeurs d'une Data Validation si la liste source est modifiée

Bonjour,

Je rencontre presque le même problème et j'ai opté pour la solution de Boisgontier

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect([liste], Target) Is Nothing And Target.Count = 1 Then
    Application.EnableEvents = False
    ValSaisie = Target.Value
    Application.Undo
    AncVal = Target
    For i = 1 To[listeChoix].Count
      If Sheets("projet").Range("listeChoix")(i) = AncVal Then Sheets("projet").Range("listeChoix")(i) = ValSaisie
    Next i
    Target = ValSaisie
   Application.EnableEvents = True
  End If
End Sub

Mon soucis est que dans l'onglet liste, je ne saisie rien dans A2, A3, etc... il s'agit de formules
Je reprend pour exemple le fichier
adapté à mon besoin dans l'onglet liste A2 = B2&" "&C2&" "&D2" "&E2 (idem sur X lignes)

Or la si je saisie dans A2 directement ça fonctionne, mais moi je vais intervenir uniquement sur les données des colonnes B,C,D et E et ensuite ma liste de validation est bien en A:A.

Comment puis je procéder pour obtenir le résultat voulu ?

 

[BOISGONTIER]

Re : Mettre à jour les valeurs d'une Data Validation si la liste source est modifiée

Bonjour,

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Column >= 2 And Target.Column <= 5 And Target.Count = 1 Then
    Application.EnableEvents = False
    ValSaisie = Target.Value
    Application.Undo
    AncVal = Cells(Target.Row, 1)
    Target = ValSaisie
    For i = 1 To [listeChoix].Count
      If Sheets("choix").Range("listeChoix")(i) = AncVal Then
        Sheets("choix").Range("listeChoix")(i) = Cells(Target.Row, 1)
      End If
    Next i
   Application.EnableEvents = True
  End If
End Sub

voir pj


JB

 

[thombzh]

Re : Mettre à jour les valeurs d'une Data Validation si la liste source est modifiée

Merci Boisgontier, c'est exactement ce que je cherchais, j'ai voulu modifier la formule =b2&c2&d2&e2 par =b2&" "&c2&" "&d2&" "&e2 mais la macro ne fonctionne plus. J'ai besoin des espaces entre les données car il s'agit de données client type nomclient adresse codepostal ville.

 

[BOISGONTIER]

Re : Mettre à jour les valeurs d'une Data Validation si la liste source est modifiée

voir pj

jb