Liste déroulante avec mise en forme conditionnelle [résolu]

[Mephi]

Bonjour,

Tout d'abord, merci pour ce forum sur lequel j'ai trouvé pas mal de choses utiles ci et là qui m'ont permis de construire mon tableau en version 1.0 (ci-joint) mais voilà, il y a un problème :

- Lorsque je tri une colonne (client, devis ou n° de devis) par ordre alphabétique, mes zones de liste déroulantes suivent bien les lignes par contre, les cases à cocher restent sur leur emplacement d'origine...


--------------------------------

L'idéal en fait, aurait été de pouvoir supprimer les cases à cocher et que la mise en forme conditionnelle soit pilotée depuis les listes déroulantes.

exemple :
En projet : aucune mise en forme
En attente : aucune mise en forme
Validé : police verte
Refusé : police grise + italique
En cours de réalisation : police verte
Travaux non conforme : police verte
Terminé : police rouge
Facturé : police bleu

J'aimerais comprendre le procédé afin de pouvoir au final rajouter des valeurs pour ma liste déroulante et modifier les mise en formes conditionnelles de cette liste...

J'ai vu certains tableaux ou les listes déroulantes étaient directement incorporées dans les cellules et non en tant qu'objets ajoutés. Y a t'il un intérêt particulier à ça ?

Si quelqu'un à la solution à mon petit souci, je l'en remercie par avance.

Mephi

[EDIT] : Je travail sur Excel 2010.

 

[PMO2]

Re : Liste déroulante avec mise en forme conditionnelle

Bonjour,

Une piste avec la démarche suivante.

J'ai supprimé les mises en forme conditionnelle.
Les CheckBox n'ont plus aucune utilité et peuvent être supprimées.
Tout passe par les zones combinées (DropDown) mais il a fallu que j'use de stratagème car elles n'interceptent aucun évènement.
J'ai donc fait une bidouille en liant ces zones combinées à des cellules en colonne M de la feuille VALEURS et en mettant une formule (=M5 etc) dans la colonne N pour pouvoir intercepter l'évènement Workbook_SheetCalculate.

1) Copiez le code suivant dans un module standard

'2 En projet : aucune mise en forme
'3 En attente : aucune mise en forme
'4 Validé:    police verte
'5 Refusé:    police grise + italique
'6 En cours de réalisation : police verte
'7 Travaux non conforme : police verte
'8 Terminé:    police rouge
'9 Facturé:    police bleu

Sub PseudoEventsDropDown(Optional dummy As Byte)
Dim DD As DropDown
Dim S As Worksheet
Dim R As Range
Set S = Sheets("JANVIER")
For Each DD In S.DropDowns
  If DD > 0 Then
    Set R = S.Range(S.Cells(DD.TopLeftCell.Row, 1), S.Cells(DD.TopLeftCell.Row, 7))
    With R.Font
      .Italic = False
      Select Case DD
       Case 4, 6, 7
        .Color = vbGreen
       Case 5
        .Color = RGB(125, 125, 125)
        .Italic = True
      Case 8
        .Color = vbRed
      Case 9
        .Color = vbBlue
      Case Else
        .ColorIndex = 0
      End Select
    End With
  End If
Next DD
End Sub

2) Copiez le code suivant dans la fenêtre de code de ThisWorkbook

Private Sub Workbook_Activate()
Dim DD As DropDown
Dim S As Worksheet
Dim R As Range
Dim Lig&
Set S = Sheets("JANVIER")
Application.EnableEvents = False
For Each DD In S.DropDowns
  DD.ListFillRange = "VALEURS!$A$2:$A$21"
  Lig& = DD.TopLeftCell.Row
  DD.LinkedCell = "VALEURS!$M$" & Lig& & ""
  Sheets("VALEURS").Range("N" & Lig& & "").Formula = "=M" & Lig& & ""
Next DD
Application.EnableEvents = True
End Sub

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
If Sh.Name = "VALEURS" Then Call PseudoEventsDropDown
End Sub

Il faut redémarrer le classeur pour que la procédure Workbook_Activate() puisse s'effectuer.

Cordialement.

PMO
Patrick Morange

 

[Mephi]

Re : Liste déroulante avec mise en forme conditionnelle

Super !

Je vais essayer de comprendre comment rajouter des pages, lignes,...ect...
Et je pense revenir poser quelques questions dans le courant de la semaine

En tout cas, merci pour ce boulot !!!

Mephi

 

[Mephi]

Re : Liste déroulante avec mise en forme conditionnelle

Bon, après différents tests, voici mes difficultés :

1) Création de la feuille Février :
Puis-je utiliser "ThisWorlbook" pour rajouter les valeurs de "Fevrier" ?
Et si oui, comment dois-je faire ?

J'ai essayer les modifications suivantes :

Private Sub Workbook_Activate()
Dim DD As DropDown
Dim S As Worksheet
Dim R As Range
Dim Lig&
Set S = Sheets("JANVIER")
Set S = Sheets("FEVRIER")
Application.EnableEvents = False
For Each DD In S.DropDowns
DD.ListFillRange = "VALEURS!$A$2:$A$21"
Lig& = DD.TopLeftCell.Row
DD.LinkedCell = "VALEURS!$F$" & Lig& & ""
Sheets("VALEURS").Range("G" & Lig& & "").Formula = "=F" & Lig& & ""
Next DD
Application.EnableEvents = True
End Sub

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
If Sh.Name = "VALEURS" Then Call PseudoEventsDropDown
End Sub

La page février fonctionne en liant à la main les cellules des zones de défilement mais la page Janvier ne fonctionne plus

J'ai ensuite essayé plusieurs formats :

Set S = Sheets("JANVIER" + "FEVRIER")

Set S = Sheets("JANVIER") + ("FEVRIER")

... ect...

Mais rien ne passe...


Puis

Private Sub Workbook_Activate()
Dim DD As DropDown
Dim S As Worksheet
Dim R As Range
Dim Lig&
Set S = Sheets("JANVIER")
Application.EnableEvents = False
For Each DD In S.DropDowns
DD.ListFillRange = "VALEURS!$A$2:$A$21"
Lig& = DD.TopLeftCell.Row
DD.LinkedCell = "VALEURS!$F$" & Lig& & ""
Sheets("VALEURS").Range("G" & Lig& & "").Formula = "=F" & Lig& & ""
Next DD
Application.EnableEvents = True
End Sub

Private Sub Workbook_Activate()
Dim DD As DropDown
Dim S As Worksheet
Dim R As Range
Dim Lig&
Set S = Sheets("FEVRIER")
Application.EnableEvents = False
For Each DD In S.DropDowns
DD.ListFillRange = "VALEURS!$A$2:$A$21"
Lig& = DD.TopLeftCell.Row
DD.LinkedCell = "VALEURS!$H$" & Lig& & ""
Sheets("VALEURS").Range("I" & Lig& & "").Formula = "=H" & Lig& & ""
Next DD
Application.EnableEvents = True
End Sub

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
If Sh.Name = "VALEURS" Then Call PseudoEventsDropDown
End Sub

C'est la cata...

J'ai essayé de créer un second simulateur d'évènement "ThisWorkbook" mais ça foire aussi !

Du coup, je sais plus trop quoi tenter...

Je joins la dernière version du projet (v1.1)

 

[PMO2]

Re : Liste déroulante avec mise en forme conditionnelle

Bonjour,

Voici une nouvelle version qui peut traiter les mois de janvier à décembre
à la condition expresse de nommer vos feuilles comme suit
JANVIER FEVRIER MARS AVRIL MAI JUIN JUILLET AOUT SEPTEMBRE OCTOBRE NOVEMBRE DECEMBRE
en respectant l'orthographe et la casse. Ou alors changer le Array dans le code.

Cordialement.

PMO
Patrick Morange

 

[Mephi]

Re : Liste déroulante avec mise en forme conditionnelle

Bonjour Patrick,

Tout d'abord, merci, une fois de plus pour cet excellant travail et pour le temps passé sur mon tableau.

Il y a cependant quelque chose qui cloche dans mon : Le classement par ordre alphabétique.
En effet, lorsque je trie mes données par ordre croissant, tout fonctionne. Je sauvegarde, puis dès que je rouvre le classeur, un message m'informe que les macros sont désactivées et me propose de les réactiver.
Et, dès qu'elles sont réactivées, au lieu de remplacer les valeurs dans la feuille "valeurs", il modifie les zones déroulantes en se servant des valeurs qui ne sont plus à la bonne place.

Bon, c'est pas très clair alors voici le tableau en version 2.01

Note : J'ai rajouté des colonnes cachées (N, O et P) sur chaque page pour calculer uniquement les montants des devis qui étaient acceptés. Mais le problème ne semble pas venir de là.

Cordialement,

Anthony

 

[PMO2]

Re : Liste déroulante avec mise en forme conditionnelle

Bonjour,

Effectivement, dès qu'on utilise le tri le résultat obtenu est complètement erroné.
On jette ce code et on le remplace par le code suivant qui utilise un ComboBox dynamique plutôt que des DropDown.

J'ai effacé tous les DropDown (listes déroulantes).
Dans la feuille VALEURS en colonne C j'ai ajouté la correspondance en Long de la couleur de la police (voir la Sub LongCouleurPolice)

FONCTIONNEMENT
Si on clique en colonne I (SUIVI) d'une feuille valide, à la condition que la cellule A de la ligne soit renseignée, on obtient un ComboBox dans lequel apparaît la liste de la feuille VALEURS. Il n'y a plus qu'à faire son choix.

IMPERATIF
La référence à la librairie Microsoft Forms 2.0 Object Library doit être activée (dans le VBE menu Outils/Références…)

Copiez le code suivant dans un module standard

'//////////////////////////////////////////
'/// Nécessite la librairie suivante    ///
'/// (faire menu Outils/Références...)  ///
'/// Library MSForms                    ///
'/// C:\WINDOWS\system32\FM20.DLL       ///
'/// Microsoft Forms 2.0 Object Library ///
'//////////////////////////////////////////

Sub CreeComboBox(Optional dummy As Byte)
Dim OL As OLEObject
Dim CB As ComboBox
Dim S As Worksheet
Dim R As Range
Dim var
Set R = ActiveCell
Set OL = ActiveSheet.OLEObjects.Add(ClassType:="Forms.ComboBox.1", Link:=False, _
        DisplayAsIcon:=False, Left:=R.Left, Top:=R.Top, Width:=R.Width, Height:=R.Height)
Set CB = OL.Object
Set S = Sheets("VALEURS")
var = S.Range("a2:a" & S.[a3].End(xlDown).Row & "")
CB.List = var
CB.LinkedCell = R.Address
With CB.Font
  .Name = "Arial"
  .Size = 9
End With
Set OL = Nothing
Set CB = Nothing
End Sub

Copiez le code suivant dans la fenêtre de code de ThisWorkbook

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Dim S As Worksheet
Dim OL As OLEObject
Dim R As Range
Dim PLAGE As Range
Dim Mois
Dim var
Dim var2
Dim i&
Dim j&
Dim bool As Boolean
Mois = Array(, "2010", "JANVIER", "FEVRIER", "MARS", "AVRIL", "MAI", "JUIN", "JUILLET", "AOUT", "SEPTEMBRE", "OCTOBRE", "NOVEMBRE", "DECEMBRE")
For i& = 1 To UBound(Mois)
  If Sh.Name = Mois(i&) Then
    bool = True
    Exit For
  End If
Next i&
If bool Then
  For Each OL In Sh.OLEObjects
    If OL.progID = "Forms.ComboBox.1" Then
      OL.Cut
      Set OL = Nothing
    End If
  Next OL
  If Sh.[a4] = "" Then Exit Sub
  Application.EnableEvents = False
  Set S = Sheets("VALEURS")
  var = S.Range("a2:c" & S.[a3].End(xlDown).Row & "")
  Set PLAGE = Sh.Range("a4:i" & Sh.[a3].End(xlDown).Row & "")
  var2 = PLAGE
  For i& = 1 To UBound(var2, 1)
    Set R = Sh.Range(Sh.Cells(i& + 3, 1), Sh.Cells(i& + 3, 9))
    With R.Font
      .Color = 0
      .Italic = False
    End With
    If var2(i&, 9) <> "" Then
      For j& = 1 To UBound(var, 1)
        If var2(i&, 9) = var(j&, 1) Then
          With R.Font
            .Color = var(j&, 3)
            If var2(i&, 9) = "Refusé" Then
              .Italic = True
            End If
          End With
          Exit For
        End If
      Next j&
    End If
  Next i&
  Application.EnableEvents = True
  '-----------
  If Target.Column = 9 Then
    If Not Application.Intersect(Target, PLAGE) Is Nothing Then
      Call CreeComboBox
    End If
  End If
End If
End Sub

Cordialement.

PMO
Patrick Morange

 

[Mephi]

Re : Liste déroulante avec mise en forme conditionnelle

Encore une fois merci. Je me débrouille avec ça.
Bonne continuation.

Anthony