XL 2013 Comment avoir plus d'une liste de validation sélective dans la même feuille ?

Sebast

XLDnaute Impliqué
Bonjour à toutes et à tous,


je m'arrache les cheveux à vouloir introduire dans la même feuille plusieurs listes de validation qui, selon les cas, vont pointer dans des tables distinctes.

En feuille "Saisie", je sélectionne un élément en colonne B, puis en colonne C, en fonction de ce qui figure en colonne B, la liste de validation affiche les choix possibles
Jusqu'ici ça fonctionne

Mais en colonne F, en fonction de ce qui figure en colonne B devrait - théoriquement - apparaître là aussi une autre liste de validation sélective
Malheureusement, ça plante et je ne sais pas si c'est dû à mon code (syntaxe foireuse ?) ou si ça n'est tout simplement pas possible …
Je me demande en effet si l'erreur ne tient pas au fait que j'ai deux fois If Not Intersect(Target …

Quelqu'un peut-il me dire si :
1) on peut avoir plusieurs fois If not Intersect(Target … dans la même Private Sub ?
2) il y a une alternative : autre code ? Mon exemple ici est simplifié (avec select case comportant 4 branches) mais la réalité sera supérieure donc difficile de mettre une formule alambiquée en Données/Validation de données


D'avance, merci pour vos lumières


Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim f As String
Dim Colonne As Range
Dim dernierelign As Long
Dim Lastline As Long

' --- Traitement première partie : remplissage colonne C via liste de validation

dernierelign = Sheets("Saisie").Range("B" & Rows.Count).End(xlUp).Row
Set Colonne = Sheets("Saisie").Range("B2:B" & dernierelign)

If Not Intersect(Target, Colonne) Is Nothing Then

For Each Target In Colonne
    Select Case Target
         Case "Parcelle"
                Lastline = Sheets("Parcelles").Range("A" & Rows.Count).End(xlUp).Row
                f = "=Parcelles!$B$2:$B$" & Lastline
         Case "Matière"
                Lastline = Sheets("Matières").Range("A" & Rows.Count).End(xlUp).Row
                f = "=Matières!$B$2:$B$" & Lastline
         Case "Fourniture"
                Lastline = Sheets("Fournitures").Range("A" & Rows.Count).End(xlUp).Row
                f = "=Fournitures!$B$2:$B$" & Lastline
         Case "Prestation"
                Lastline = Sheets("Prestations").Range("A" & Rows.Count).End(xlUp).Row
                f = "=Prestations!$B$2:$B$" & Lastline
    End Select

With Target(1, 2).Validation
     .Delete
     .Add xlValidateList, Formula1:=f
End With

Next Target

End If


' Traitement deuxième partie : remplissage colonne F via liste de validation

Dim MyF As String
Dim MaPlage As Range
Dim dernlig As Long
dernlig = Sheets("Saisie").Range("B" & Rows.Count).End(xlUp).Row
Set MaPlage = Sheets("Saisie").Range("B2:B" & dernlig)

If Not Intersect(Target, MaPlage) Is Nothing Then

 For Each Target In MaPlage

 Select Case Target
         Case "dernlig"
                Lastline = Sheets("Activités_parcelles").Range("A" & Rows.Count).End(xlUp).Row
                MyF = "=Activités_parcelles!$A$2:$A$" & dernlig
         Case "Matière"
                dernlig = Sheets("Activités_matières").Range("A" & Rows.Count).End(xlUp).Row
                MyF = "=Activités_matières!$A$2:$A$" & dernlig
         Case "Fourniture"
                dernlig = Sheets("Activités_fournitures").Range("A" & Rows.Count).End(xlUp).Row
                MyF = "=Activités_fournitures!$A$2:$A$" & dernlig
         Case "Prestation"
                dernlig = Sheets("Activités_prestations").Range("A" & Rows.Count).End(xlUp).Row
                MyF = "=Activités_prestations!$A$2:$A$" & dernlig
    End Select

With Target(1, 5).Validation  ' car la liste est en 5e position
     .Delete
     .Add xlValidateList, Formula1:=MyF
End With

Next Target

End If

End Sub
 

Pièces jointes

  • Question_forum_24032016.xlsm
    42.5 KB · Affichages: 43

cathodique

XLDnaute Barbatruc
Re : Comment avoir plus d'une liste de validation sélective dans la même feuille ?

Bonsoir,

Soit patient. Au fait, sur la feuille "saisies" en colonne B, il n'y a pas de validation. et tu nous dis que ça fonctionne.

Personnellement, je n'ai pas bien compris.

Cordialement,
 
Dernière édition:

Sebast

XLDnaute Impliqué
Re : Comment avoir plus d'une liste de validation sélective dans la même feuille ?

Bonsoir,

merci d'avoir regardé.
Sur la feuille "Saisie" en colonne B il y a bel et bien une validation, mais contrairement aux deux autres que j'ai essayé de mettre en place, il n'y a pas d'ambiguité : tout provient d'une unique feuille. Alors que pour les autres colonnes, comme essaie de le faire le select case, la source n'est pas la même ...

si tu essaies de valider une des trois colonnes (en jaune dans le fichier joint), tu constateras le plantage - que je ne sais pas à quoi attrubuer.

Cordialement
 

Chris401

XLDnaute Accro
Re : Comment avoir plus d'une liste de validation sélective dans la même feuille ?

Bonjour

Tu peux le faire en un seul traitement :
Code:
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim f As String, MyF As String
Dim Colonne As Range
Dim dernierelign As Long, Lastline As Long, dernlig As Long

' --- Traitement : remplissage colonne C et F via liste de validation

dernierelign = Sheets("Saisie").Range("B" & Rows.Count).End(xlUp).Row
Set Colonne = Sheets("Saisie").Range("B2:B" & dernierelign)

If Not Intersect(Target, Colonne) Is Nothing Then

For Each Target In Colonne
    Select Case Target
         Case "Parcelle"
                Lastline = Sheets("Parcelles").Range("A" & Rows.Count).End(xlUp).Row
                f = "=Parcelles!$B$2:$B$" & Lastline
                dernlig = Sheets("Activités_parcelles").Range("A" & Rows.Count).End(xlUp).Row
                MyF = "=Activités_parcelles!$A$2:$A$" & dernlig
         Case "Matière"
                Lastline = Sheets("Matières").Range("A" & Rows.Count).End(xlUp).Row
                f = "=Matières!$B$2:$B$" & Lastline
                dernlig = Sheets("Activités_matières").Range("A" & Rows.Count).End(xlUp).Row
                MyF = "=Activités_matières!$A$2:$A$" & dernlig
         Case "Fourniture"
                Lastline = Sheets("Fournitures").Range("A" & Rows.Count).End(xlUp).Row
                f = "=Fournitures!$B$2:$B$" & Lastline
                dernlig = Sheets("Activités_fournitures").Range("A" & Rows.Count).End(xlUp).Row
                MyF = "=Activités_fournitures!$A$2:$A$" & dernlig
         Case "Prestation"
                Lastline = Sheets("Prestations").Range("A" & Rows.Count).End(xlUp).Row
                f = "=Prestations!$B$2:$B$" & Lastline
                dernlig = Sheets("Activités_prestations").Range("A" & Rows.Count).End(xlUp).Row
                MyF = "=Activités_prestations!$A$2:$A$" & dernlig
    End Select

With Target(1, 2).Validation
     .Delete
     .Add xlValidateList, Formula1:=f
End With
With Target(1, 5).Validation
     .Delete
     .Add xlValidateList, Formula1:=MyF
End With

Next Target

End If

End Sub
Cordialement
Chris
 

cathodique

XLDnaute Barbatruc
Re : Comment avoir plus d'une liste de validation sélective dans la même feuille ?

Bonjour,
Sur la feuille "Saisie" en colonne B il y a bel et bien une validation
Je n'ai peut-être pas un bon niveau en vba, mais je t'assure que j'ai ouvert ton fichier pour essayer de donner un coup de main, en colonne C il y a bel et bien une liste de validation mais pas en colonne B. Pas vraiment compris.

J'espère que le code de Chris fera ton affaire.

Bonne journée.
 

Chris401

XLDnaute Accro
Re : Comment avoir plus d'une liste de validation sélective dans la même feuille ?

Re
Salut cathodique

Il y a également la solution sans macro

Cordialement
Chris
 

Pièces jointes

  • Copie de Question_forum_24032016-2.xlsx
    35.4 KB · Affichages: 35

Sebast

XLDnaute Impliqué
Re : Comment avoir plus d'une liste de validation sélective dans la même feuille ?

Salut Chris, salut cathodique,


Chris
Comment n’y ai-je pas pensé avant ?
Encore une fois, j’avais le nez dans le guidon et c’est un œil neuf qui me donne la solution !

Effectivement, c’est le plus simple et le plus efficace de placer les deux formules dans le select case …

Quant à la solution sans VBA, j’en prends également note (mais je préfère via le code)

Cependant, afin de ne pas mourir idiot, sais-tu si mon erreur venait de l’emploi multiple de If Not Intersect(Target, Colonne)
J’ai cherché un peu partout et ne trouve pas de réponse quant à l’éventuelle interdiction de plusieurs If Not Intersect(Target …
dans la même procédure …


Cathodique
Si tu te places en colonne B (couleur jaune), tu ne vois pas une petite flèche orientée vers le bas et le choix parmi 4 items ?
Cette colonne B (support) présente une liste de validation non gérée par VBA – certes – mais la liste y est bien.


Encore merci pour votre aide
 

Bebere

XLDnaute Barbatruc
Re : Comment avoir plus d'une liste de validation sélective dans la même feuille ?

bonjour
voilà un autre code
Option Explicit


Private Sub Worksheet_Change(ByVal Target As Range)
Dim f As String, nomf As String
Dim Plg As Range, Cel As Range
Dim derl As Long
Dim Ws As Worksheet
' --- Traitement première partie liste de validation c
Application.EnableEvents = False
derl = Range("B" & Rows.Count).End(xlUp).Row
Set Plg = Range("B2:B" & derl)
nomf = Range("B" & Target.Row) & "s"
Set Ws = Worksheets(nomf)


If Not Intersect(Target, Plg) Is Nothing Then
derl = Ws.Range("A" & Rows.Count).End(xlUp).Row
f = "=" & nomf & "!$B$2:$B$" & derl

Target.Offset(, 1).ClearContents

With Target.Offset(, 1).Validation
.Delete
.Add xlValidateList, Formula1:=f
End With

End If


' Traitement deuxième partie : remplissage colonne F via liste de validation
'
If Target.Column = 3 Then
derl = Ws.Range("A" & Rows.Count).End(xlUp).Row
Set Plg = Ws.Range("B2:B" & derl)
For Each Cel In Plg
If Cel = Target Then
Target.Offset(, 1) = Cel.Offset(, 1)
Target.Offset(, 2) = Cel.Offset(, 2)
Exit For
End If

Next Cel
derl = Sheets("Activités_" & nomf).Range("A" & Rows.Count).End(xlUp).Row
f = "=Activités_" & nomf & "!$A$2:$A$" & derl

With Cells(Target.Row, "F").Validation 'liste de validation F,mon avis pas besoin
.Delete
.Add xlValidateList, Formula1:=f
End With

End If

Application.EnableEvents = True

End Sub
 

Sebast

XLDnaute Impliqué
Re : Comment avoir plus d'une liste de validation sélective dans la même feuille ?

Bonjour Bebere,

Merci beaucoup pour ta solution complète, qui fonctionne parfaitement.

Cependant, quelques questions :

Offset(, 1)
On peut omettre le premier membre de la parenthèse ?
Est-ce équivalent à offset(0,1) ?


nomf = Range("B" & Target.Row) & "s"
Set Ws = Worksheets(nomf)

Je comprends la démarche, qui d’ailleurs colle parfaitement au cas d’espèce fourni.
Mais si demain pour la colonne B je devais étendre les choix avec des noms sans rapport entre l’item et le nom de la feuille respective, je me retrouve coincé. C’est mon erreur d’avoir laissé penser qu’il y avait une relation entre l’item et le nom de la feuille (c’est-à-dire juste la lettre ‘s’).

Exemple : si à l’avenir en feuille Support j’introduis l’item « Matériel » mais que les infos respectives sont à piocher dans une feuille intitulée « Equipement », alors le lien est coupé. Idem si pour diverses raisons, la feuille Parcelles devait s’appeler « Foncier », la feuille « Matières » devait s’intituler «Produit », la feuille « Fournitures » devait s’appeler « Pièces_détachées » ou la feuille « Prestations » renommée en « Frais_généraux ».

Du coup, je me retrouve coincé car je vais certainement faire évoluer le fichier (ici version simplifiée).
Tu m’enlèverais une sacrée épine si tu me montrais comment faire la recherche entre l’item et les feuilles respectives nommées autrement. Pas besoin de le faire pour la colonne F, qui (et c’est encore ma faute) suit la même logique : je saurai m’inspirer de ce qui concernera la colonne C.


Encore merci pour l’aide et les explications apportées
 

Bebere

XLDnaute Barbatruc
Re : Comment avoir plus d'une liste de validation sélective dans la même feuille ?

Sebast pour offset l'explication est bonne
avec un select case, tu initialises nomf avec le nom de la feuille
un exemple
Application.EnableEvents = False
derl = Range("B" & Rows.Count).End(xlUp).Row
Set Plg = Range("B2:B" & derl)

Select Case Range("B" & Target.Row)
Case "Matière"
nomf = Range("B" & Target.Row) & "s"

Case "Fourniture"
nomf = Range("B" & Target.Row) & "s"


End Select
 

Sebast

XLDnaute Impliqué
Re : Comment avoir plus d'une liste de validation sélective dans la même feuille ?

Re,

Merci pour tes explications.

Je ne m’en sors pas avec cet exemple.


Imagine que les fournitures se trouvent désormais en feuille nommée « Pièces_détachées »,
Il n’y a alors plus de lien « syntaxique » entre Fourniture (colonne B) et la feuille qui reprendrait le nom de Fourniture auquel on ajoute la lettre s (voir le nouveau fichier joint)

Case "Fourniture"
nomf = Range("B" & Target.Row) & "s"
… ne s’appliquerait donc plus

Et c’est justement ce qui me chagrine car mon fichier est appelé à évoluer, avec des noms de feuille qu’on ne retrouvera pas en item colonne B (par exemple, dans la réalité, la feuille qui contient les parcelles portera le nom du compte comptable, donc un code sans aucun lien avec « Parcelles »)

Mais je reconnais que dans ma demande initiale et dans mon fichier initial, ce lien existait (simplifié à tort par moi)
Mon souci est qu’à l’avenir, je vais reprendre des noms de feuille sans lien direct avec les items de la colonne B et je vais me retrouver coincé

J’ai essayé en définissant une nouvelle plage variable (MyCompleteF) mais sans succès (voir fichier joint)

As-tu une idée ?
 

Pièces jointes

  • Question_25032016_mise_a_jour.xlsm
    40.3 KB · Affichages: 22
  • Question_25032016_mise_a_jour.xlsm
    40.3 KB · Affichages: 27

Bebere

XLDnaute Barbatruc
Re : Comment avoir plus d'une liste de validation sélective dans la même feuille ?

bonjour Sebast
un début de solution,commentaires dans feuille saisie et dans code
 

Pièces jointes

  • Question_25032016_mise_a_jour.xlsm
    45.1 KB · Affichages: 27
  • Question_25032016_mise_a_jour.xlsm
    45.1 KB · Affichages: 31

Sebast

XLDnaute Impliqué
Re : Comment avoir plus d'une liste de validation sélective dans la même feuille ?

Bonjour Bebere,

merci pour ton aide. Je reprends à peine suite à une weekend de Pâques prolongé.
Je regarde ça et te revient au besoin.

à bientôt
 

Discussions similaires

Membres actuellement en ligne

Statistiques des forums

Discussions
311 725
Messages
2 081 943
Membres
101 849
dernier inscrit
florentMIG