Mise à jour liste validation depuis liste comportant des formules

Bonjour à tous,

J'ai trouvé sur le forum comment mettre à jour la cellule A3 contenant la liste de validation lorsqu'on modifiait les données sources D7:H7.

Mon problème va un peu plus loin dans le sens où mes données sources D7:H7 dépendent d'une cellule A1 et d'une cellule A2.

Lorsque je modifie directement les cellule D7:H7, la cellule A3 se modifie bien.

Par contre lorsque je modifie ma cellule A1 et/ou ma cellule A2, mes données sources D7:H7 se mettent à jour et A3 n'est pas modifié.

Quelqu'un pourrait m'aider ?

Merci par avance.

19classeur1.xlsm (15.36 Ko)

Bonjour,

Tu es un peu fâché avec les déclarations de variables et cibler la zone de changement ne t'a guère préoccupé...

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim zone As Range, zonetest As Range, t, ab, i%
    If Intersect(Target, Me.Range("A1:B1")) Is Nothing Then Exit Sub
    Set zone = Me.Range("D7:H7")
    Set zonetest = Me.Range("A3")
    ab = Me.Range("A1:B1").Value
    On Error GoTo Fin
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.Undo
    t = zonetest.Value
    If zonetest.Cells.Count > 1 Then
        For i = 1 To UBound(t)
            If t(i, 1) <> "" Then
                t(i, 1) = WorksheetFunction.Match(t(i, 1), zone, 0)
            End If
        Next i
    Else
        If t <> "" Then t = WorksheetFunction.Match(t, zone, 0)
    End If
    Me.Range("A1:B1").Value = ab
    zone.Calculate
    If zonetest.Cells.Count > 1 Then
        For i = 1 To UBound(t)
            If t(i, 1) > 0 Then t(i, 1) = zone.Cells(1, t(i, 1))
        Next i
    Else
        If t > 0 Then t = zone.Cells(1, t)
    End If
    zonetest.Value = t
Fin:
    Application.EnableEvents = True
End Sub

Seuls les changements en A1 et B1 sont pris en compte, comme il se doit...

La zonetest étant limitée à une seule cellule, condition nécessaire pour distinguer le cas du cas standard (plusieurs cellules, en colonne). Si on est systématiquement à plusieurs cellules cette condition pourra disparaître...

Cordialement.

Waouh, merci milles fois, je suis incapable de faire ça !

c'est exactement ce que je cherchais.

Dans le fichier original que je ne peux pas déposer sur le forum, la cellule A1 et B1 sont en fait B7 et T10. Si je les séparer par un ";" dans la macro, celle-ci beug, aurais-tu une solution.

Enfin, j'ai plusieurs cellules contenant une liste de validations de données avec plusieurs sources différentes mais toujours alimenter par les mêmes B7 et T10.

J'ai reproduit mon fichier ici mais je n'arrive pas à appliquer la macro.

Le séparateur en VBA est la virgule, non le point-virgule. Cependant cela ne fonctionne que pour certaines opérations, pour d'autres Excel renâcle car tu définis ainsi une plage multizones, dont le comportement se distingue dans certains cas d'une plage d'un seul tenant...

On va donc traiter les deux cellules en tableau mais séparément...

Pour intégrer les autres listes, il va falloir revoir l'ensemble, car en fait tu n'as que des listes isolées référant chacune à sa propre plage-liste et avec des écarts irréguliers entre les listes.

Je vois ça dès que j'ai un moment...

Merci infiniment !

Bonjour,

Version réadaptée :

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim zone() As Range, t(), ab(1), z, zt, i%
    If Intersect(Target, Union(Me.[B7], Me.[T10])) Is Nothing Then Exit Sub
    zt = Array(14, 18, 22, 26, 32)
    z = Array(16, 20, 24, 28, 35)
    ReDim zone(UBound(z)): ReDim t(UBound(zt))
    For i = 0 To UBound(z)
        Set zone(i) = Me.Range("X" & z(i) & ":AB" & z(i))
    Next i
    ab(0) = Me.Range("B7"): ab(1) = Me.Range("T10")
    On Error GoTo Fin
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.Undo
    For i = 0 To UBound(zt)
        If Me.Range("T" & zt(i)) <> "" Then
            t(i) = Me.Range("T" & zt(i))
            t(i) = WorksheetFunction.Match(t(i), zone(i), 0)
        End If
    Next i
    Me.Range("B7") = ab(0): Me.Range("T10") = ab(1)
    Me.Calculate
    For i = 0 To UBound(zt)
        If t(i) > 0 Then
            Me.Range("T" & zt(i)) = zone(i).Cells(1, t(i))
        End If
    Next i
Fin:
    Application.EnableEvents = True
End Sub

Le principe est le même, mais nous avons affaire à 5 zonetest d'une seule cellule et 5 zone, chacune dédiée à une zonetest, on traite donc les 5 en boucle... Pour pouvoir le faire : un tableau zt liste les lignes des zonetest (colonne fixe = T) et un tableau z liste les lignes des zone (colonnes fixes = X et AB).

La variable Range zone est remplacée par un tableau de type Range, dimensionné sur le nombre de zone, on y affecte les différentes zone...

La variable Range zonetest n'est pas remplacée, chaque zonetest étant limitée à une cellule, on traite les cellules directement...

La variable t qui recueillait les valeurs de zonetest pour récupérer l'adresse source dans zone, afin de pouvoir affecter la nouvelle valeur à zonetest, devient un tableau correspondant aux cellules zonetest et utilisé de la même façon pour chacune.

Enfin, ab devient un tableau à 2 éléments pour recueillir les valeurs des 2 cellules maintenant disjointes.

Pour suivre le processus de A à Z et le mettre en place quel que soit le nombre de zone et zonetest, si la configuration reste semblable, avec colonnes fixes...

1) On teste si le changement concerne l'une ou l'autre (ou les deux) des deux cellules à la source de changements dans les zone, la suite ne se déroulant que si c'est le cas.

2) On établit un tableau zt des lignes de zonetest, et z des lignes de zone ; ces tableaux devront être de même dimension puisque autant de zone que de zonetest...

3) On dimensionne les tableaux zone et t aux dimensions de z et zt. On affecte à chaque élément de zone la plage correspondante (objet Range).

4) On affecte à ab les valeurs des deux cellules B7 et T10 modifiées...

5) Ces éléments recueillis on suspend les évènements, ainsi que la mise à jour de l'affichage, sous gestion d'erreur, de façon qu'en cas d'erreur on puisse sortir en rétablissant les évènements, et on annule la modification pour recueillir les valeurs antérieures.

6) On recueille dans t les valeurs de chaque zonetest (qui n'ont pas changé...) que l'on tranforme en rang de la plage zone correspondante, en utilisant la fonction Match (EQUIV) [valeurs de zone rétablies à leurs anciennes valeurs].

7) On réapplique le changement introduit à B7 et T10. On force le recalcul par mesure de précaution, de façon que les plages zone prennent leurs nouvelles valeurs.

8) On affecte à chaque zonetest sa nouvelle valeur prélevée dans zone, connaissant son rang (lequel correspond à la colonne...)

Voilà ! Cordialement.

C'est exactement ce que je cherchais. Je crois qu'il va falloir que je me mette sérieusement à VBA...

Merci beaucoup pour le travail et les explications !

Bonjour,

J'ai appliqué le code VBA de M.FERRAND pour un autre fichier et j'ai encore besoin de l'aide de la communauté.

Concrètement quand je change les valeurs T12, T21, T30, T39 ou T51, les cellules U12, U21, U30, U39, U51 se mettent automatiquement à jour. Les cellules U12, U21, U30, U39, U51 sont elles-même des listes.

Mon souhait est le suivant :

Admettons que je sélectionne le 3ème choix de la liste U12 (15 produit 1)

si je change la valeur de T12 (Admettons que je mette la valeur 4 )

la valeur U12 va mettre à jour le 3ème choix de la liste U12 (60 produit 1)

Or je voudrais que lorsque je change la valeur de T12, la cellule U12 affiche le 1er choix de la liste (ici 20 produit 1)

Idem pour les cellules U12, U21, U30, U39, U51.

Espérant avoir été clair.

Merci par avance de votre aide précieuse.

11classeur1.xlsm (18.67 Ko)

Bonjour,

Désolé pour le délai, j'étais absent du Forum depuis mi-février... Je rattrape !

Cette dernière configuration est très simplifiée par rapport aux précédentes ! Une seule cellule est source du changement pour chaque liste... En outre, le retour à la première valeur de la liste en cas de changement simplifie encore...

Si j'ai bien compris, on peut donc simplifier d'autant la procédure !

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim z, zt, i%, a%
    If Target.Count > 1 Then Exit Sub
    If Target.Column = 20 Then
        zt = Array(12, 21, 30, 39, 51)
        For i = 0 To UBound(zt)
            If Target.Row = zt(i) Then a = i: Exit For
        Next i
        If i > UBound(zt) Then Exit Sub
        z = Array(16, 25, 34, 43, 55)
        Me.Range("U" & zt(a)) = Me.Range("W" & z(a))
    End If
End Sub

A noter que certains choix en T introduisent des vides dans la liste correspondante ce qui me paraît être une anomalie à examiner de plus près... Je n'ai pas regardé les formules...

Cordialement.

Merci beaucoup. C'est tout à fait ce que je cherchais.

En effet il y a certains vide dans les cellules car j'ai essayé d'éliminer les doublons mais la méthode est perfectible.

Rechercher des sujets similaires à "mise jour liste validation comportant formules"