XL 2013 via VBA : liste de validation avec conditions

[Sebast]

Bonjour à toutes et à tous,

je cherche à mettre en place via VBA une liste de validation sélective.
Jusqu'ici, je ne sais le faire qu'à la mano avec le classique :

Données/Validation de données/Autoriser/Liste/Source, laquelle source contient la formule suivante :
(au passage, merci le forum pour son aide précieuse)

'

=SI(OU(Saisie!$B9="Divers";Saisie!$B9="Autre"); Frais_généraux!$A$2:$A$4;SI(Saisie!$B9="Produit"; Atelier!$A$2:$A$5; Agricole!$A$2:$A$14))

Comme on peut le voir, cette formule ne gère que 3 cas de figure.
La règle qui prévaut en feuille "Saisie", colonne B :
si on a 'divers' ou 'autre' alors la liste de validation doit piocher les infos en feuille "Frais_généraux"
si on a 'produit' alors la liste de validation va piocher les infos en feuille "Atelier"
autres valeurs alors la liste de validation va piocher les infos en feuille "Agricole"

Ayant prochainement à gérer bien davantage de cas, je crains d'être dépassé par les imbrications multiples, c'est pourquoi j'aimerais savoir si on peut mettre tout ça en VBA.
je me dis qu'avec un SELECT CASE, ce sera beaucoup plus lisible, même avec 10 cas de figure …

J'avoue avoir fait des recherches sur le net et utlisé l'enregistreur de macros, mais sans succès : au mieux, j'enregistre une unique condition mais ne vois pas trop comment rendre variable le nombre de conditions

Quelqu'un peut-il me dire comment mettre ça en macro ?

Merci d'avance

 

[job75]

Re : via VBA : liste de validation avec conditions

Bonjour Sebast,

Placez dans le code de la feuille "Saisie" :

Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Range, f$
Set r = Intersect(Target, Range("B2:B" & Rows.Count), Me.UsedRange)
If r Is Nothing Then Exit Sub
For Each r In r 'en cas d'entrées/effacements multiples
  Select Case r
    Case "Divers": f = "=Frais_généraux!$A$2:$A$4"
    Case "Autre": f = "=Frais_généraux!$A$2:$A$4"
    Case "Produit": f = "=Atelier!$A$2:$A$5"
    Case Else: f = "=Agricole!$A$2:$A$14"
  End Select
  With r(1, 2).Validation
    .Delete
    .Add xlValidateList, Formula1:=f
  End With
Next
End Sub

Pour obtenir les nouvelles formules de validation, revalidez une par une les cellules de la plage B2:B10.

Ou mieux, faites un copier-coller de cette plage sur elle-même.

Edit : j'avais oublié Target dans Intersect.

A+

 

[job75]

Re : via VBA : liste de validation avec conditions

Re,

Une autre solution plus légère en mémoire, seule la cellule active en colonne C est dotée d'une liste de validation :

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim f$
[C:C].Validation.Delete 'RAZ
If Target.Row = 1 Or Target.Column <> 3 Or Target.Count > 1 Then Exit Sub
Select Case Target(1, 0)
  Case "Divers": f = "=Frais_généraux!$A$2:$A$4"
  Case "Autre": f = "=Frais_généraux!$A$2:$A$4"
  Case "Produit": f = "=Atelier!$A$2:$A$5"
  Case Else: f = "=Agricole!$A$2:$A$14"
End Select
Target.Validation.Add xlValidateList, Formula1:=f
End Sub

A+

 

[Sebast]

Re : via VBA : liste de validation avec conditions

Bonsoir job75,

merci beaucoup pour ton aide, qui répond parfaitement à mon attente ;
en plus, les solutions sont astucieuses et limpides, exactement ce qu'il me faut.

Cependant quelques questions subsistent (même si je dois encore digérer le tout) :

1er code

Set r = Intersect(Range("B2:B" & Rows.Count), Me.UsedRange)

À quoi correspond le Me ? on ne le déclare nulle part

2e code
On passe de Worksheet_Change (1er) à Worksheet_SelectionChange : pourquoi ?

[Select] Case Target(1, 0)

Target(1,0) fonctionne ici comme un offset() ?

Encore merci pour le temps que tu as consacré à mon besoin, j’en apprends beaucoup tous les jours

 

[job75]

Re : via VBA : liste de validation avec conditions

Re,

À quoi correspond le Me ? on ne le déclare nulle part

Le mot clé Me représente la feuille VBA où se trouve le code.

Edit : j'ai complété le Intersect de la 1ère macro.

[Select] Case Target(1, 0)

Target(1,0) fonctionne ici comme un offset() ?

Oui, c'est le même repérage que pour Cells(x, y).

Bonne fin de soirée.

 

[Sebast]

Re : via VBA : liste de validation avec conditions

Bonjour job75,


Merci pour ces précisions, je comprends mieux.

En revanche

Concernant ton message

Edit : j'ai complété le Intersect de la 1ère macro

Je ne comprends pas, dans ton post de 17 h 34 hier, tu écrivais déjà

Edit : j'avais oublié Target dans Intersect.

mais je n’ai eu qu’un seul code avec Intersect/target.

Comment le comprendre : tu dois apporter une correction ? le premier message avec l’oubli du target dans intersect n’est pas passé ?


Encore merci