XL 2010 Attribuer ou Changer la valeur d'une cellule en validation de données

[Sirberthoult]

Bonjour le forum,

Je souhaiterais dans mon exemple, que dans chaque lignes, les cellules des colonnes B,D et F n'aient jamais 2 fois la même valeur.

- donc en B si je choisi "titi", puis en D si je choisi "tata" alors "toto" s'inscrit logiquement en F.

- si finalement je décide de remplacer en colonne D le "tata" en "toto" alors le "toto" en F se transforme en "tata". pour qu'il n'y ai jamais 2 fois la même valeur...

merci d'avance à toutes personnes pouvant me trouver une solution ou une piste

cordialement.

 

[piga25]

Bonjour,
Un essais comme cela suivant un code de BOISGONTIER

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  Set zsaisie = Range("B2:F7")
  If Not Intersect(zsaisie, Target) Is Nothing And Target.Count = 1 Then
    Ligne = Cells(Target.Row, "a")
    Set d = CreateObject("scripting.dictionary")     ' choisis
    For i = zsaisie.Row To zsaisie.Row + zsaisie.Rows.Count - 1
      If Ligne = Cells(i, "a") Then d(Cells(i, "b").Value) = "": d(Cells(i, "f").Value) = ""
    Next i
    Set d2 = CreateObject("scripting.dictionary")    ' reste
    For Each c In [liste]
      If Not d.exists(c.Value) Then d2(c.Value) = ""
    Next c
    Me.ComboBox1.List = d2.keys
    Me.ComboBox1.Height = Target.Height + 3
    Me.ComboBox1.Width = Target.Width
    Me.ComboBox1.Top = Target.Top
    Me.ComboBox1.Left = Target.Left
    Me.ComboBox1 = Target
    Me.ComboBox1.Visible = True
    Me.ComboBox1.Activate
  Else
    Me.ComboBox1.Visible = False
  End If
End Sub
Private Sub ComboBox1_Click()
  ActiveCell = Me.ComboBox1
End Sub

 

[piga25]

Bonjour,
Avec une validation de donnée qui signale si le nom existe déjà.

 

[Sirberthoult]

Bonjour le forum, Bonjour piga25

merci de t’être penché sur mon problème...

dans ta première solution, c'est intéressant mais je souhaiterais que la liste complète reste disponible quand on ouvre la combobox... ainsi je peux changer "apres coups" ... cela devra faire une inversion de valeurs entre les colonnes concernées.

dans ton post de ce matin je n'ai rien... plus de combo ni de validation de données et je ne trouve pas de code vba non plus...

a bientôt
merci

 

[JHA]

Bonjour à tous,

La version de @piga25 en post #3 ne prenait pas en compte la liste en colonne "K"
Ci joint un essai avec cette condition supplémentaire et une liste nommée dynamique.

=ET(NB.SI($B1:$F1;B1)=1;ESTNUM(EQUIV(B1;Liste;0)))

JHA

 

[Sirberthoult]

Bonjour le forum, piga25, JHA

merci pour vos retours.

je ne cherche pas en priorité à être prévenu ou emperché de pouvoir le faire, mais surtout gagner du temps dans l'attribution des valeurs dans les colonnes...ainsi si "tata" en B et "titi" en D alors s’inscrit tout seul "toto" en F
et si jamais après coups, je décide de changer la valeur dans une colonne alors cela s'inverse automatiquement pour qu'il n'y ai pas de doublons sur la ligne.

 

[Sirberthoult]

voici un code qui fonctionne parfaitement pour 2 colonnes et 2 choix possibles...

si on renseigne 1 valeur dans une colonne l'autre s'inscrit tout seul dans l'autre colonne.
et si finalement on modifie alors les valeurs s'inversent.

pour résumer, j'aimerais la même chose pour 3 colonnes et 3 valeurs possible.

 

[job75]

Bonjour Sirberthoult, le forum,

Ce problème est beaucoup plus compliqué qu'il n'y paraît, voici une solution VBA :

Private Sub Worksheet_Change(ByVal Target As Range)
Dim liste, i&, j%, P As Range, c As Range, k, cc As Range
Set Target = Intersect(Target, Union(Columns("B"), Columns("D"), Columns("F")), UsedRange)
If Target Is Nothing Then Exit Sub
Application.EnableEvents = False 'désactive les évènements
For Each Target In Target 'si entrées multiples
    liste = Range("K1:K3")
    i = Target.Row
    j = Target.Column
    Set P = Union(Cells(i, "B"), Cells(i, "D"), Cells(i, "F"))
    Select Case Application.CountA(P)
        Case 2
            For Each c In P
                For k = 1 To 3
                    If liste(k, 1) = c Then liste(k, 1) = "": Exit For
                Next k
                If c.Column <> j And c = Target Then Target = "": GoTo 1 'si doublon
            Next c
            For Each c In P
                If c = "" Then
                    For k = 1 To 3
                        If liste(k, 1) <> "" Then c = liste(k, 1): GoTo 1 'valeur restante
                    Next k
                End If
            Next c
        Case 3
            For Each cc In P
                If cc.Column <> j And cc = Target Then
                    For Each c In P
                        For k = 1 To 3
                            If liste(k, 1) = c Then liste(k, 1) = "": Exit For
                    Next k, c
                    For k = 1 To 3
                        If liste(k, 1) <> "" Then cc = liste(k, 1): GoTo 1 'valeur restante
                    Next k
                End If
            Next cc
    End Select
1 Next Target
Application.EnableEvents = True 'réactive les évènements
End Sub

A+

 

[Sirberthoult]

Bonjour le forum, Job75

c'est parfait ! c'est exactement ce que je souhaitais ! je vais ce lundi transférer ca dans mon fichier au boulot et je te fais un retour definitif !

merci encore job75

 

[job75]

J'ai ajouté UsedRange dans la 3ème ligne de la macro pour limiter Target.

Pour le cas où l'on copierait une colonne sur elle-même.

Bonne nuit.

 

[Sirberthoult]

Bonsoir le forum, job75,

je reviens seulement maintenant car je n'ai pas pu me pencher sur mon probléme avant avec les ponts et le taf...

bref je pensais que j'arriverai a modifier mon code en ajoutant des conditions pour utiliser soit mon bout de code pour 2 "agents" dans 2 colonnes soit ton code job75 pour 3 "agents" dans 3 colonnes ( qui fonctionne parfaitement)...mais je n'arrive pas à l'ecrire... ( j'ai tenté de determiné le nombre d'occurences présentes dans mes listes triées pour deduire une conditionnalité et appliqué le bon code ... mais je n'y arrive pas )

je joins un fichier "anonymé"... cela ce passe dans l'onglet "sectorisation" chaque jour le nombre d'agent dispo peut etre différent donc : si 2 agent code pour 2 colonnes (B et D pour les matins) ou (C et E pour les aprems)
si 3 agents code pour 3 colonnes ( B, D et F pour les matins ou (C, E et G eventuellement pour les aprems mais normalement il n'y en a toujours seulement 2)

merci d'avance pour l'aide...

 

[job75]

Bonsoir Sirberthoult, le forum,

Ce que vous voulez faire maintenant semble sans rapport avec le problème précédent.

Et je n'y comprends rien.

D'ailleurs vous n'utilisez pas le code de mon post #8.

A+

 

[Sirberthoult]

Bonsoir le forum, job 75

Si justement, je n'ai juste pas recopier votre code dans la feuille "sectorisation" pour ne pas "effacer" celui qui fonctionne quand il y 2 agents en poste.

Si je remplace par votre code dans le cas de figure ou il y a 3 agents à placer dans 3 secteurs (bleu matin, rouge matin et violet matin ) cela fonctionne nickel ! mais si je passe à l'apres midi du même jour ( bleu aprem et rouge aprem) alors votre code "n'agit" pas "correctement" c'est a dire comme le fait actuellemnt le mien : je place le premier agent en bleu et l'agent restant va automatiquement en rouge.

pour résumé parfois j'ai 3 agents pour 3 secteurs et parfois j'ai 2 agents pour 2 secteurs et je souhaite que le code fonctionne de facon similaire: c'est a dire que le dernier agents non placé va automatiquement dans le secteur non occupé et si finalement je change alors il s'inverse entre eux (exactement ce que j'ai demandé dans mon poste et que vous avez reussi à faire dans votre code à 3 colonnes ) Moi j'avais un code qui agissait ainsi pour 2 agents (2colonnes , 2 secteurs ). et maintenant j'aimerai que le bon code s'applique dans le bon cas de figure...j'ai tenté de determiné le nombre d'occurences présentes dans mes listes triées pour deduire une conditionnalité et appliqué le bon code, mais surement a cause de la matrice cela me donne le nombre de lignes dans mes liste et non pas le nombre "d'agents"...

peut etre que la solution est autre...en tout cas j'espere que je suis claire dans mes explications...
j'ai mis des annotations dans le code en page "sectorisation" pour essayé d'etre compris...

merci de me lire...

 

[Sirberthoult]

Bonjour le forum, job75

Je n'arrive pas de moi même à apporter des modifications à votre code job75 qui fonctionne très bien avec 3 agents.(titi, toto, tata comme dans mon post initial) car parfois je n'ai que 2 agents ( j'ai laissé mon code en vert qui fonctionne bien aussi, mais QUE pour 2 agents) .
je pensais pouvoir fusionner les 2 codes ou faire agir le votre quand il a 3 agents possible et le mien quand je n'en ai que 2 mais je n'y arrive pas.

dans mon onglet "sectorisation" apparaît 2 listes d'agents qui travaillent du matin colonne "K" et d’après midi colonne "L" et je cherche à remplir le tableau "A1:G33" et j'aimerai toujours le même fonctionnement le dernier agent non renseigné s'ajoute automatiquement dans la colonne vide et si je fais un changement alors les agents s'inversent.

merci de jeter un œil...

 

[piga25]

Bonjour,
Avec le code de JOB75 et uniquement pour le matin suivant deux ou trois personnes:

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("K4") = "" Then GoTo deux:
Trois:
Dim liste, i&, j%, P As Range, c As Range, k, cc As Range
Set Target = Intersect(Target, Union(Columns("B"), Columns("D"), Columns("F")), UsedRange)
If Target Is Nothing Then Exit Sub
Application.EnableEvents = False 'désactive les évènements
For Each Target In Target 'si entrées multiples
    liste = Range("K2:K4")
    i = Target.Row
    j = Target.Column
    Set P = Union(Cells(i, "B"), Cells(i, "D"), Cells(i, "F"))
    Select Case Application.CountA(P)
        Case 2
            For Each c In P
                For k = 1 To 3
                    If liste(k, 1) = c Then liste(k, 1) = "": Exit For
                Next k
                If c.Column <> j And c = Target Then Target = "": GoTo 1 'si doublon
            Next c
            For Each c In P
                If c = "" Then
                    For k = 1 To 3
                        If liste(k, 1) <> "" Then c = liste(k, 1): GoTo 1 'valeur restante
                    Next k
                End If
            Next c
        Case 3
            For Each cc In P
                If cc.Column <> j And cc = Target Then
                    For Each c In P
                        For k = 1 To 3
                            If liste(k, 1) = c Then liste(k, 1) = "": Exit For
                    Next k, c
                    For k = 1 To 3
                        If liste(k, 1) <> "" Then cc = liste(k, 1): GoTo 1 'valeur restante
                    Next k
                End If
            Next cc
    End Select
1 Next Target
Application.EnableEvents = True 'réactive les évènements

deux:
Dim un As Range, deux As Range, form1, xrg As Range, x, n&, s(0 To 1), v
   On Error GoTo FIN:
   If Target.Count > 1 Then Exit Sub
   If Target = "" Then Exit Sub
   Set un = Target
  
   If un.Column = Range("b2").Column Then
      Set deux = Cells(Target.Row, "d")

   ElseIf un.Column = Range("d2").Column Then
      Set deux = Cells(Target.Row, "b")
   ElseIf un.Column = Range("c2").Column Then
      Set deux = Cells(Target.Row, "e")
   ElseIf un.Column = Range("e2").Column Then
      Set deux = Cells(Target.Row, "c")
   End If
   form1 = Target.Validation.Formula1
   If Left(form1, 1) = "=" Then
      Set xrg = Range(Mid(form1, 2))
      If xrg.Count <> 2 Then Exit Sub
      For Each x In xrg.Value: s(n) = x: n = n + 1: Next
   Else
      v = Split(form1, Application.International(xlListSeparator))
      If UBound(v) - LBound(v) + 1 <> 2 Then Exit Sub
      s(0) = v(0): s(1) = v(1)
   End If
   Application.EnableEvents = False: deux.Value = IIf(un.Value = s(0), s(1), s(0))
FIN:
   Application.EnableEvents = True


End Sub