liste de validation de données dynamique

[grodep]

bonjour à tous, je vous expose mon problème du jour : dans le classeur ci-joint, vous trouverez une feuille "liste" dans laquelle j'ai rentré deux séries de noms.
Ces deux séries sont définies ensuite dans le gestionnaire de noms en tant que liste par le biais de la formule : "=DECALER(Liste!$B$2;;;NBVAL(liste!$B:$B)-1)"

Dans mon autre feuille "prévi", j'ai plusieurs noms dans la premiere colonne, et pour chacun , la ligne de cellule correspondante ne peut etre remplie qu'avec des données d'une des listes définies dans le gestionnaire de noms. J'utilise pour cela l'option "validation de données".

Ce que je souhaiterais, c'est que pour chaque personne, la liste évolue de manière à ne m'offrir dans le menu déroulant que les données non encore saisies
exemple : pour machepro, j'ai déjà utilisé T1, T2, P1, P2, je voudrais que dans la cellule F5, ma liste de choix ne comprenne plus ces 4 entrées et que dans la cellule G5, ma liste de choix ne comprenne plus ces 4 entrées ni celle de F5, etc, etc ....

Merci à tous de vos avis et suggestions.

 

[BOISGONTIER]

Re : liste de validation de données dynamique

Bonjour,

Exemple:

Données/Validation

JB

 

[pierrejean]

Re : liste de validation de données dynamique

bonjour grodep
Salut JB

Un essai en Vba qui
créee les listes de validation lors de la saisie dans la colonne A et adapte les suivantes en fonction des saisies

 

[grodep]

Re : liste de validation de données dynamique

boisgontier, je connaissais déjà ton site dont je me suis largement inspiré pour mon code, un grand merci à toi pour tous les trésors que tu mets en ligne.

merci pierrejean, ton truc fonctionne du tonnerre, me reste plus qu'à bien l'étudier pour l'adapter à mon code... et là, je sens que ça va être une autre paire de manche. :/

je vous tiens au courant.

 

[BOISGONTIER]

Re : liste de validation de données dynamique

Bonsoir,

Les listes peuvent être > 200 caractères.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  If Not Intersect([champ], Target) Is Nothing Then
    Sheets("liste").[d2:d100].ClearContents
    If [A1] = "TOR" Then a = [TOR].Value Else a = [MOF].Value
    For Each c In a
      If IsError(Application.Match(c, Range(Cells(Target.Row, 2), Cells(Target.Row, 10)), 0)) Then
        Sheets("liste").[d65000].End(xlUp).Offset(1, 0) = c
      End If
    Next c
  End If
End Sub

JB

 

[grodep]

Re : liste de validation de données dynamique

Rebonjour,

bon, pour finir, je n'ai pas réussi à intégrer la solution proposé par pierrejean dans mon code, et me suis donc orienté vers celle de boisgontier : celle ci fonctionne très bien SAUF QUE :
lorsque j'initialise un nouveau classeur, mon code commence par effacer un certain nombre de feuilles, parmi lesquelles la feuille prévi : du coup, je perds la "private sub" inscrite dans la dite feuille, et je ne sais pas comment faire pour qu'à la création de ma feuille prévi, mon code réinscrive la macro propre à cette feuille, ni meme si c'est possible.

De même, le gestionnaire de données "perd" la référence de la liste "champ" puisque celle ci fait référence à la feuille prévi qui vient d'être supprimée avant d'être recréée.

Si vous vous demandez pourquoi je supprime certaines feuilles lors de l'initialisation, c'est parce que c'est la solution la plus efficace que j'ai trouvé pour faire disparaitre tout ce que celles ci contiennent(tableaux, objets) et la maniere dont elles ont été formatées la fois précédente. Les supprimer puis les recréer me simplifie grandement la tâche.

merci d'avance

 

[grodep]

Re : liste de validation de données dynamique

j'aime pas trop faire ça, mais c'est vrai que j'aimerai énormément avoir un nouveau p'tit coup de pouce pour finaliser mon projet, donc je me permets un ptit up en espérant que vous me le pardonnerez.

merci d'avance

 

[pierrejean]

Re : liste de validation de données dynamique

Re

Je suis a ta disposition pour adapter ma proposition a ton code
Pour cela il est indispensable d'avoir un fichier exemple (sans données confidentielles)

 

[grodep]

Re : liste de validation de données dynamique

je te remercie, en fait je mettrais volontiers mon fichier complet en ligne, il ne comporte aucune données confidentielles, mais je suis confronté à deux problemes : le premier est que le classeur est trop lourd, et le second est que j'ai bien trop honte de partager ce code affreusement mal écrit 😛
Et du fait de son écriture calamiteuse, la compréhension par qqun d'extérieur risque d'en être rendue compliquée...

Bref, j'essaie de te donner le bout du code concerné, celui qui "crée" la feuille "prévi" afin que tu te rendes comptes du désastre, et que tu m'aides, si c'est encore possible .

'******************************************************************************************************************************
'******************************************************************************************************************************
'******************************************* établissement feuille prévi  *****************************************************
'******************************************************************************************************************************
'******************************************************************************************************************************

aa = 8 'aa est le numéro de la 1ere colonne des vols donc 8



Sheets("Stage").Select
'mv prend la valeur de la ligne de la 1ere cellule nom moniteur
mv = Application.WorksheetFunction _
        .Match("moniteurs", Worksheets(1).Range("A1:A100"), 0)
        mv = mv + 1
    S = Range("liststa").Rows.Count
j = Range("stage!B4") + 7
S = S * 2
ReDim Tablovol(S, j)
ReDim Tablomon(6, 1)
Tablomon = Range("listmon").Value
ligfnmon = UBound(Tablomon)
ligfnmon = ligfnmon - 1


'remplissage 1ere colonne tablovol avec les noms des stagiaires, et 6 colonnes suivantes avec monits
m = 14
For i = 2 To S Step 2
Tablovol(i, 1) = Range("A" & m).Value
o = mv
For rs = 2 To 7
Tablovol(i, rs) = Range("A" & o).Value
o = o + 1
Next rs
m = m + 1
Next i
Sheets("prévi").Select
  Columns("B:G").Select
    Selection.ColumnWidth = 3.75
      Columns("h:cl").Select
    Selection.ColumnWidth = 7.43
Range("A1:z" & S) = Tablovol
'remplissage liste de validation cellules listmon
For i = aa To j
For o = 2 To S Step 2
Cells(o, i).Select
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=listmon"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
    
    'MFC sur cellule moniteur selon type de vol choisi
    Strcol = IIf((i - 1) \ 26 > 0, Chr((i - 1) \ 26 + 64), "")
    Strcol = Strcol & Chr(i - ((i - 1) \ 26) * 26 + 64)
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=CHERCHE(""s"";" & Strcol & o + 1 & ")"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
       .ThemeColor = xlThemeColorLight2
       .TintAndShade = 0.599963377788629
    End With
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=CHERCHE(""g"";" & Strcol & o + 1 & ")"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 255
        .TintAndShade = 0
    End With
     Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=CHERCHE(""acc"";" & Strcol & o + 1 & ")"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 255
        .TintAndShade = 0
    End With
Next o
Next i

'déverouillage plage de cellules tableau prévi vol

Range(Chr(64 + aa) & 2 & ":" & Strcol & S + 1).Select
 Selection.Locked = False
'remplissage liste de validation saisie du vol
Sheets("Prévi").Select
Range("A1") = "=Stage!B5" 'rappel du stage choisi"
For i = aa To j ' j correspond au nombre total de jour du stage
For o = 3 To S + 1 Step 2  'S correspond au nombre de lignes du tableau ou au nombre de stagiaires(selon l'usage que je dois en faire)
Cells(o, i).Select
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:= _
        "=if($A$1=""moniteur"",moniteur,IF($A$1=""PO"",PO,IF($A$1=""FI"",FI,IF($A$1=""observateur"",observateur,sélection))))"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
Next o
Next i

je crains vraiment que donné comme cela, le code soit incompréhensible. Je joins la feuille concernée "prévi" ainsi que celle contenant les listes de données.

J'espere que l'ensemble sera "utilisable".

Encore merci d'avance.