XL 2010 Liste déroulante en cascade avec condition

[ironhead36t]

Bonjour,

J'essaie de créer un planning pour une équipe.
Le fichier est composé
- d'un onglet Congés où les congés des opérateurs sont indiqués
- d'un onglet Planning, dans lequel, selon les activités demandées, je dois obtenir la liste des opérateurs qui ont les compétences, ET qui ne sont pas en congés
- d'un onglet Ressources - Services avec 2 tableaux indiquant la liste des activités et les compétences des opérateurs.

Dans l'onglet Planning, je peux sélectionner le type d'activité par rapport aux tableaux présents dans l'onglet Ressources - Services.
Je peux également lister les opérateurs qui ont les compétences pour réaliser les activités. Il s'agit d'une liste déroulante en cascade en utilisant la fonction INDIRECT

Ma question est maintenant de savoir, comment je peux intégrer le fait qu'un opérateur est en congés et ne pas l'afficher dans la liste des opérateurs ?

Le fichier est en PJ.

Merci!

 

[Robert]

Bonjour Ironhead et bienvenu, bonjour le forum,

En pièce jointe ton fichier modifié avec l'événementielle Selection Change ci-dessous :

Option Explicit[/COLOR]

Private Sub Worksheet_SelectionChange(ByVal Target As Range) 'au changement de sélection dans l'onglet
Dim RS As Worksheet 'déclare la variable RS (onglet Ressources - Services)
Dim C As Worksheet 'déclare la variable C (onglet Congés)
Dim TV As Variant 'déclare la variable TV (Tableau des Valeurs)
Dim R As Range 'déclare la variable R (Recherche)
Dim COL As Byte 'déclare la variable COL (COLonne)
Dim I As Integer 'déclare la variable I (Incrément)
Dim LI As Integer 'déclare la variable LI (LIgne)
Dim PL As Range 'déclare la variable PL (PLage)
Dim CEL As Range 'déclare la variable CEL (CELlule)
Dim L As String 'déclare la variable L (Liste)

'si la selection se trouve ailleurs que dans les colonnes 5, 8 ou 11 (E, H ou K), sort de la procédure
If Application.Intersect(Target, Application.Union(Columns(5), Columns(8), Columns(11))) Is Nothing Then Exit Sub
If Target.Row = 1 Then Exit Sub 'si la sélection se trouve dans la ligne 1, sort de la procédure
Set C = Worksheets("Congés") 'définit l'onglet C
TV = C.Range("A1").CurrentRegion 'définit le tableau des valeurs TV
Set RS = Worksheets("Ressources - Services") 'définit l'onglet RS
If Target.Offset(0, -1).MergeArea(1).Value = "" Then Exit Sub 'si la valeur de la cellule sélectionnée décalée d'une colonne à gauche est vide, sort de la procédure
'définit la recherche R (recherche dans la ligne 1 de l'onglet C, la valeur exacte de la cellue sélectionnée décalée d'une colonne à gauche)
Set R = RS.Rows(1).Find(Target.Offset(0, -1).MergeArea(1).Value, , xlValues, xlWhole)
If Not R Is Nothing Then COL = R.Column 's'il existe au moins une occurrence trouvée, définit la colonne COL de la premiere occurrence trouvée
For I = 2 To UBound(TV, 1) 'boucle sur toutes les lignes I du tableau des valeurs TV (en partant de la seconde)
    'si la donnée ligne I colonne 2 de TV est égale à la valeur de la cellule ligne = ligne de la cellule sélectionné, colonne B, définit la ligne LI, sort de la boucle
    If TV(I, 2) = Cells(Target.Row, "B").Value Then LI = I: Exit For
Next I 'prochaine ligne de la boucle
If LI = 0 Then Exit Sub 'si LI est égale a zéro, sort de la procédure
Set PL = C.Range(C.Cells(LI, 3), C.Cells(LI, UBound(TV, 2))) 'définit la plage PL (la plage ees cellules des colonnes 3 à 12 de la ligne trouvée LI)
For I = 2 To RS.Cells(Application.Rows.Count, COL).End(xlUp).Row 'boucle 1 : sur toutes les cellules de la colonne COL de l'onglet RS (en partant de la seconde)
        For Each CEL In PL 'Boucle 2 : sur toutes les cellule CEL de la plage PL
            'condition : si la cellule CEL n'est pas vide et si la valeur de la cellule en ligne 1 et colonne COL  de l'onglet C
            'est égale à la valeur de la cellule ligne LI colonne COL de l'ongelt RS, va à l'étiquette "suite"
            If CEL.Value <> "" And C.Cells(1, CEL.Column).Value = RS.Cells(I, COL).Value Then GoTo suite
        Next CEL 'prochaine cellule de la boucle 2
        L = IIf(L = "", RS.Cells(I, COL).Value, L & "," & RS.Cells(I, COL).Value) 'redéfinit la liste L
suite: 'étiquette
Next I 'prochaine cellule de la boucle 1
With Target.Validation 'prend en compte la validation de donnée de la cellule sélectionnée
    .Delete 'efface une eventuelle validation existante
    .Add xlValidateList, Formula1:=L 'définit la liste L comme liste de validation de données
End With 'fin de la prise en compte de la validation de donnée de la cellule sélectionnée
End Sub

Le fichier :

 

[ironhead36t]

Merci Robert!

Ne maîtrisant pas le vba, je vais devoir me retrousser un peu les manches pour comprendre ce que tu as fait

Je voulais aussi rajouter un petit bout de mise en forme pour dire qu'une même ressource ne peut pas être sélectionnée plusieurs fois sur le même jour.

J'ai trouvé ce bout de code:

sub doublon()
    dim ma_cellule as Range
    dim plage as Range
    set plage = selection
    
    for each ma_cellule in Range
        If Application.WorksheetFuntion.CountIf(plage,macellule.Value) > 1 Then
            ma_cellule.Interior.ColorIndex = 46
        End If
    Next ma_cellule
    
End sub

Mais il prend en input une sélection "manuelle". Comment lui dire de ne prendre compte que par ligne, et surtout les colonnes D, G, K ?
Par exemple, regarder si doublon sur D2, G2 et K2
Puis faire la même chose sur la ligne suivante ?

De plus, ce code ne s'exécute que lorsqu'on lui demande. Comment indiquer de le faire à la volée ?

Merci pour l'aide.

 

[Robert]

Re,

Pourquoi tu veux ça en plus puisque tu as déjà une MFC qui te l'indique ?!...

 

[Robert]

Re,

En pièce jointe la version 2. Je me suis rendu compte que les MFC n'étaient pas active sur toutes les lignes et j'ai rajouté une ligne dans le code en cas de sélection de plus d'une seule cellule...

 

[ironhead36t]

Merci Robert, c'est parfait.

Maintenant à moi de le prendre en main, et de le modifier si besoin.