MFC sur plage nommée de lignes
Bonjour,
J'espère que tout le monde va bien.
Je rencontre une difficulté et j'aimerais votre aide svp.
Objectif : à partir d'une plage de lignes mémorisée dans le gestionnaire de nom, passer par une MFC pour leur appliquer une couleur.
NB : vous m'excuserez j'espère de ne pas expliquer le contexte lié à cette problématique. J'ai délibérément réduit ma problématique à sa plus simple expression : relever certaines lignes d'un tableau structuré > les mémoriser dans un array du gestionnaire de nom > colorer ces lignes via MFC.
→ Avez-vous une idée s'il vous plait ?
Edit : Avis aux allergiques du VBA, vous pouvez totalement oublier ce bout de code (qui n'a que vocation à créer les éléments spécifiques du gestionnaire de nom et de la MFC) et vous pencher sur les illustrations MFC/gestionnaire de nom, si vous le souhaitez.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Set lsto = ThisWorkbook.Sheets(1).ListObjects(1)
With lsto
If .DataBodyRange Is Nothing Then GoTo FIN
If Not Intersect(Target, .DataBodyRange) Is Nothing _
And Target.CountLarge = 1 Then Call TargetColor(Target)
End With
FIN:
End Sub
Sub TargetColor(Target As Range)
Set dico = CreateObject("Scripting.Dictionary")
dico.Add Target.Row, Nothing
If Not dico.exists(7) Then dico.Add 7, Nothing
If Not dico.exists(9) Then dico.Add 9, Nothing
ActiveWorkbook.Names.Add Name:="TargetColor_Row", RefersTo:=dico.keys
Set dico = Nothing
Set lsto = ThisWorkbook.Sheets(1).ListObjects(1)
With lsto.DataBodyRange.FormatConditions
.Delete
With .Add(xlExpression, , "=OU(CTXT(LIGNE())=CTXT(TargetColor_Row))")
.Interior.Pattern = xlGray25
.Interior.PatternThemeColor = xlThemeColorAccent1
End With
End With
End Sub
Pareil pour moi ca fonctionne mais seulement à l'ouverture de la feuille.
As tu essayé de cliquer sur une ligne du tableau (ca actualisera), et chez moi ca disparait et ne conserve la MFC que sur la ligne active !!!
Bonjour à tous de nouveau !
Je ne constate aucun dysfonctionnement.
Tests effectués : validation d'une cellule, saisie d'une nouvelle valeur
JFL bonjour :)
Il semble que tu utilises une variante de mon problème.
En effet je vois la 9eme ligne 2 eme colonne sélectionnée dans ton image jointe : elle devrait être formatée puisque sélectionnée.
Peux tu utiliser mon fichier joint initialement ou bien le code transmis pour les tests s'il te plait ?
Bonjour à tous de nouveau !
En effet je vois la 9eme ligne 2 eme colonne sélectionnée dans ton image jointe : elle devrait être formatée puisque sélectionnée.
Cette ligne n'étant pas référencée dans le nom TargetColor_Row, il me semble normal que la MFC ne s'applique pas. (Les macros ne sont pas activées dans mon environnement....)
En effet, tu n'utilises ni le fichier joint ni le code transmis.
Mais c'est tout de même intéressant puisque j'ai fais un test en supprimant ma macro et en conservant TargetColor_Row tel qu'enregistré dans le gestionnaire de nom et là cela fonctionne. De plus, une modification manuelle des valeurs de cette variable directement dans le gestionnaire de nom fonctionne aussi et produit les effets attendus.
Ce sujet s'adresse donc exclusivement aux experts VBA puisque lorsque le code est actif la MFC ne produit ses effets QUE sur la ligne active et outrepasse totalement les autres valeurs de la variable TargetColor_Row .... j'hallucine
Je pense qu'il faut forcer le recalcul des MFC du lsto.databodyrange. J'ai tenté un
lsto.Range.Calculateen fin de sub, mais non ...
Rien ne fonctionne et je n'en ai pas l'explication ! C'est incompréhensible, j'ai même osé mon plus beau
EnableFormatConditionsCalculation = TrueEn désespoir de cause mais sans que çà ne me pose de problème (hormis intellectuel et d'égo) dans ma procédure, je me passerais donc de la mémorisation de la variable "tableau de lignes" dans le gestionnaire de nom pour introduire une boucle - for each - qui créera une MFC dédiée à chaque ligne.
J'ai la "chance" de pouvoir supprimer ces MFC et les recréer à chaque selection.change puisqu'il n'y en pas d'autres d'enregistrées (sinon c'eut été une bien sombre histoire).
Aller bref, sujet auto résolu et clos
Bonne soirée à tous, merci JFL
je ne comprends pas pourquoi vous modifiez la MFC, on doit seulement modifier "TargetColo_Row"
Bonsoir,
une proposition :
Sub TargetColor(Target As Range)
Dim Temp As String, NomDeLigne, TabloName(), Tablo, Cpt
' création d'un objet dictionary
Set dico = CreateObject("Scripting.Dictionary")
' récupération de la valeur des lignes mémorisées
On Error GoTo suite
Tablo = Split(Replace(Replace(Replace(Replace(ActiveWorkbook.Names("TargetColor_Row").RefersTo, "}", ""), """", ""), "{", ""), "=", ""), ",")
' vérification sur la dernière valeur existante
If UBound(Tablo) = 0 And Tablo(0) = Target.Row Then GoTo Fin
' création du dictionnaire de numéro unique de ligne
For i = 0 To UBound(Tablo)
dico.Add Tablo(i), Tablo(i)
Next i
suite:
' test d'ajout ou de suppression du numéro de ligne cliquée en la transformant en texte
If Not dico.exists("" & Target.Row) Then
dico.Add Target.Row, Target.Row
Else
dico.Remove "" & Target.Row
End If
' une fois le dico mis à jour on remplie le tableau du nom en valeur numérique par multiplication par 1
ReDim TabloName(1 To dico.Count)
Cpt = 1
For Each NomDeLigne In dico.keys
TabloName(Cpt) = NomDeLigne * 1
Cpt = Cpt + 1
Next NomDeLigne
' on y inscrit les nouvelles valeurs
ActiveWorkbook.Names.Add Name:="TargetColor_Row", RefersTo:=TabloName()
' on détruit le dico pour faire de la place
Set dico = Nothing
Exit Sub
Fin:
ActiveWorkbook.Names.Add Name:="TargetColor_Row", RefersTo:="="
End SubCelle-ci permet de sélectionner et de désélectionner une ligne...
Le fichier :
Pourquoi passer par un dico ?
@ bientôt
LouReeD
Edit : bonsoir BsAlv ! je vais de ce pas voir votre proposition car vu mon niveau je me suis cassé la tête avec ceci !
sans dico
Sub TargetColor(Target As Range)
Dim sNom, Nom As Name, s, s1, i
sNom = "targetColor_Row" 'le nom du "name"
On Error Resume Next
Set Nom = ThisWorkbook.Names(sNom) 'il existe déjà ?
On Error GoTo 0
If Nom Is Nothing Then Set Nom = ThisWorkbook.Names.Add(sNom, "|") 'sinon, on l'ajoute
s = Mid(Replace(Nom.Value, Chr(34), ""), 2) 'la valeur actuelle sans le "=" et les gillemets
s1 = "|" & Target.Row & "|" 'numero de la ligne entre 2 pipes
If InStr(1, s, s1) > 0 Then 'ligne déjà présent
s = Replace(s, s1, "|") '=supprimer
Else 'sinon
s = s & IIf(Right(s, 1) = "|", "", "|") & Mid(s1, 2) 'ajouter
End If
Nom.RefersTo = s
End SubBonjour,
en reprenant des idées de BsAlv, en gardant la possibilité de supprimer des lignes sélectionnées, en n'utilisant pas de Dictionnary et en permettant de cliquer "à la suite" sur la même cellule, voici ma nouvelle proposition :
Sub TargetColor(Target As Range)
Dim TabloName(), Tablo, Cpt, S, Nom
' récupération de la valeur des lignes mémorisées
On Error Resume Next
' on récupère le nom du gestionnaire
Set Nom = ActiveWorkbook.Names("TargetColor_Row")
' s'il n'existe pas alors S vaut le numéro de ligne cliquée
If Nom Is Nothing Then
' on attribue la première valeur
S = Target.Row
Else
' s'il existe :
' on récupère la valeur de TargetColor_Row en supprimant les "{","}" et "=" (on enlève par précaution les ")
' et en ajoutant une "," avant et après
S = "," & Replace(Replace(Replace(Replace(ActiveWorkbook.Names("TargetColor_Row").RefersTo, "}", ""), """", ""), "{", ""), "=", "") & ","
' on test si la ligne est déjà présente en prenant le Target.Row entouré de ","
' comme cela en cliquant sur 7 on ne joue pas avec le 17 : ,7, <> ,17, (merci BsAlv !)
If InStr(1, S, "," & Target.Row & ",") > 0 Then
' ligne déjà présente on l'enlève de la variable S
S = Replace(S, "," & Target.Row & ",", ",")
' si la variable devient vide
If S = "," Then ActiveWorkbook.Names.Add Name:="TargetColor_Row", RefersTo:="=": Exit Sub
Else
' sinon on l'ajoute à la suite
S = S & Target.Row ' on ajoute la ligne à la variable S
End If
End If
On Error GoTo 0
' nettoyage de la variable S en supprimant les données vides
' on split la variable S en tablo
Tablo = Split(S, ",")
' vérification sur la dernière valeur existante
If UBound(Tablo) = 0 And Tablo(0) = Target.Row Then
' le gestionnaire n'aura qu'une valeur celle de Target.Row
ActiveWorkbook.Names.Add Name:="TargetColor_Row", RefersTo:=Target.Row: Exit Sub
Else
' sinon on boucle sur Tablo afin de retirer les valeurs vides
Cpt = 0
' on remplie le tableau Noms avec les variables existantes
For i = 0 To UBound(Tablo)
If IsNumeric(Tablo(i)) Then
Cpt = Cpt + 1
ReDim Preserve TabloName(1 To Cpt)
TabloName(Cpt) = Tablo(i) * 1
End If
Next i
End If
' on y inscrit les nouvelles valeurs
ActiveWorkbook.Names.Add Name:="TargetColor_Row", RefersTo:=TabloName()
End SubLe fichier :
@ bientôt
LouReeD
BsAlv,
en l'état sur mon fichier votre code ne fonctionne pas, je pense que c'est du aux "|" qui reste dans le RefereTo :
C'est pourquoi j'ai repris votre idée mais avec les virgules qui sont utilisées (sous VBA, car une fois le tableau injecté dans le nom du gestionnaire elles se transforment en point...) de base. Enfin sur ma version d'Excel
@ bientôt
LouReeD
salut LouReeD, la formule de la MFC était aussi modifiée, c'est pourquoi cela ne fonctionne pas chez vous ...
Bonjour à BsAlv, LouReed,
Wow les amis, calmez vous
Pour mon objectif, la première réponse de Bart est satisfaisante, il suffisait après avoir modifié le TargetColor_Row de ne pas supprimer/recréer la MFC en place. Même si je ne comprends absolument pas pourquoi cela pose un problème à Excel.......
Après vous semblez être parti à la recherche d'une réponse éloignée de mon but premier, mais c'est complètement ma faute puisque ca manquait d'explication.
Le but est que : à chaque selectionchange dans le tableau, la ligne sélectée est mise en subrillance. Cette ligne est liée (par une clé dans une colonne qui n'apparait pas dans l'exemple) à d'autres lignes du même tableau (qui font référence à cette clé), l'ensemble des lignes (celle sélectée et celles liées à la clé) doivent alors subir la surbrillance. La collecte de l'ensemble des lignes en question est disponible au préalable dans un dictionnaire...
... et c'est pour cela que je suis partie d'un exemple simplifié avec un target.row et des valeurs arbitraires.
Ma difficulté était l'incompréhension face à ce phénomène.
Je vous remercie, en espérant que vous n'y avez pas passé trop de temps ... mea culpa
Bonjour,
oui mais la solution de désélection dans ce cas peut être intéressante, non ? Sinon en cas de clic par erreur comment revenir en arrière ?
Après j'avais cru comprendre qu'à chaque clic les lignes devaient "s'ajoutaient"... Mais à priori ce sont les ligne 7 et 9 plus la dernière cliquée qu'il vous fallait...
@ bientôt
LouReeD
re,
il ne faut pas changer la MFC si elle s'applique à un tableau structuré. Si on ajoute ou supprime des lignes du tableau, la MFC s'adapte, donc vraiment facile !
Il y a une problème, si on ajoute des lignes quelque part au milieu du TS, la MFC se double en 2 plages séparées, vraiment ambétant de point de vue visuel mais après un certain temps, cela peut aussi ralentir le fichier. Solution, si on a plusieurs lignes dans ce tableau, on sélectionne la 2eme jusqu'à la dernière ligne du TS et on supprime toutes les MFCs de cette plage. Puis on sélectionne la première ligne du TS et on copie ses MFC vers le reste du TS. (On peut le faire aussi en VBA). Cela n'est pas toujours possible, si les formules des MFC sont compliquées ... . Donc, on fait cette manipulation dépendant de l'urgence, une fois par jour, semaine, mois, année, ...