Créer des MFC d'un type prédéfini pour chaque ligne d'une feuille

Bonjour le Forum !

Je gère une feuille Excel qui détaille, semaine par semaine et pour plusieurs projets, le planning et le temps attribué aux personnes affectées à chaque projet (cf fichier joint).

Pour chaque projet, plusieurs lignes détaillent le planning hebdomadaire à l'aide d'une MFC, et les lignes suivantes détaillent la charge de travail également à l'aide de MFC.

Jusque-là tout va bien.

Là où ça se complique, c'est que ce planning est très évolutif.

J'ajoute et je retire constamment des projets et des lignes à l'intérieur des projets et, de plus, mon fichier fait des aller-retours entre Excel 2003 sur PC et Excel 365 sur Mac.

Du coup, les MFC beuguent et je passe un temps fou à les recréer.

A force, je me suis dis que le plus simple serait de créer une macro qui efface toutes les MFC de la feuille et les recrée toutes propres.

Il me semble que le plus simple serait d'attribuer un code à chaque ligne en fonction de sa nature (planning ou décomposition), que la macro "lise" ce code puis recrée la MFC correspondante.

Problème : je ne sais pas m'y prendre pour faire ça en VBA (je sais créer des macros avec l'enregistreur de macros puis les bricoler à la marge, mais là c'est trop complexe pour procéder comme ça).

Quelqu'un pourrait-il m'aider siouplé ?

Merci !!!

30planning.zip (3.20 Ko)

Bonjour,

tant qu'à faire ça autant mettre les couleurs en dur et supprimer tes MFC.

Plus simple et ça évitera de ralentir ton fichier (les MFC sont volatiles, recalculées entièrement à chaque saisie).

eric

Merci pour ta réponse, mais ta proposition qui a le mérite de la simplicité n'est pas approprié à l'usage que j'ai de ce fichier.

Parce que je retravaille ce planning en permanence : j'ajoute un jour de travail ici, j'en retranche 2 là, je vérifie que le total par collaborateur ne dépasse pas 5j par semaine, ou je rallonge une phase, etc...

Du coup, si je ne passe pas par des MFC, je vais devoir relancer la macro à chaque changement, avec des risques d'oublis, etc...

Pour l'instant je préférerais persévérer avec des MFC.

tu peux très bien lancer la macro sur l'événement Change, en limitant aux cellules impactant tes couleurs.

Si ton tableau doit s'étendre ça restera plus léger puisque seules les lignes impactées seront mises à jour.

Si ton tableau s'agrandit les MFC deviennent très gourmandes.

Enfin c'est toi qui voit

Hem hem... "lancer la macro sur l'événement Change" ça ne me parle pas trop, faute de connaissances structurées en VBA.

Mais je pense comprendre la logique.

Ce qu'il y a, c'est que, pour les besoins de la cause (et la confidentialité de données professionnelles), j'ai simplifié ma feuille Excel.

Dans le fichier original, tous les jours de travail sont totalisés par phase et par personne.

Ça veut dire que chaque changement d'une valeur peut affecter des dizaines de cellules, peut-être des centaines de cellules.

Par ailleurs, mon classeur original pèse un peu moins de 6Mo en .xls et 2,5 en .xlsm et encore, c'est pq il intègre une copie des versions de chaque semaine précédente. Quand j'en extrais la feiulle de la semaine en cours, ça ne pèse que 580ko en .xls, donc ça n'est pas spécialement lourd.

Mon vrai problème n'est pas la lourdeur du fichier mais le fait que les MFC se dérèglent, sans doute du fait des échanges PC v2003 <--> Mac v365.

Bonjour,

Quand je parlais de plus léger, il s'agit plus du nombre de cellules avec MFC que du poids du fichier.

Si à terme c'est plusieurs centaines ou milliers ça rend le fichier peu réactif et ça devient vite insupportable.

Comme tu y tiens je suis donc parti sur suppression/reconstruction des MFC, en plus c'est plus simple.

Elles sont toutes supprimées à partir de F6.

Modifier les constantes :

Const lig1 As Long = 6, col1 As Long = 6 

si ça évolue.

Par contre je ne le fais pas sur chaque changement de cellule, pas raisonnable. Si tu le veux, tu l'ajouteras...

Elles sont reconstruites d'office à chaque activation de la feuille.

Et par un bouton MFC si tu veux le faire au coup par coup.

eric

27planning.zip (16.78 Ko)

Wow !

Merci beaucoup pour tout ce boulot.

Je vais regarder ça à la loupe pour mieux comprendre et adapter à mon "vrai" planning.

Cela dit, quand je clique sur le bouton "MFC" ça coince sur la ligne

plage.FormatConditions.Add Type:=xlExpression, Formula1:="=ET($D" & plage.Row & "<=F$2;$E" & plage.Row & ">=F$3)"

de la macro MFC1...

Y a-t-il un préalable à connaître ?

En tous cas, 1000 mercis de m'aider comme ça.

(et oui : une recréation des MFC à l'ouverture + à la demande, ça devrait suffire).

Ca coince ne veut rien dire, il faut le message d'erreur.

Tu as mis un xls parce que tu es vraiment sur excel 2003 ?

Si oui, les MFC ont changé depuis, ça risque d'être incompatible... Peux-tu aller sur un >=2007 ?

Merci pour ta réponse rapide.

Oui, désolé, "ça coince" c'est insuffisant.

Donc voilà le msg que je reçois : "Erreur d'exécution '5': Argument ou appel de procédure incorrect".

J'essaierai demain au boulot sur Office365.

Mais chez moi, je reste sur Office 2003 (je préfère, et j'ai pas trop le choix).

Si tu veux que je rende compatible 2003 il faudrait que tu enregistres une macro et que tu m'envoies le résultat.

Tu te mets en F9, tu fais Alt puis (sans relacher Alt) v puis r, et tu valides la fenêtre.

Tu vas dans 'Mise en forme conditionnelles / Gérer les règles'

tu double-cliques sur la 1ère, dans la formule tu ajoutes un espace que tu supprimes, et tu valides tout.

Tu refais Alt+v Alt+r

Alt+F11, double-clic sur Module1 (à gauche)

Tu copie tout le texte que tu colles ici dans un post. Garde sa mise en forme avec l'icone </>

eric

Quand je fais "Alt puis (sans relacher Alt) v" ça insère une date (le 30/11/2018) dans la cellule active :/

Et si je maintiens la touche Alt enfoncée et que je tape "r", ça déroule la barre d'outils "Dessin" mais ça n'ouvre pas de fenêtre.

J'ai recommencé après avoir fermé la barre d'outils Dessin, et là il ne se passe rien de spécial, mais aucune fenêtre ne s'ouvre...

Y a-t-il un autre moyen de faire ce que tu me demandes de faire ?

Je pensais que les raccourcis clavier n'avaient pas changé.

Fouille dans les menus pour trouver 'Enregistrer une maco' ou qq chose d'approchant

Bon, je ne suis pas sûr d'avoir fait ce que tu attends vu que ma version d'Excel ne se comporte pas comme dans ton souvenir.

Si c'est une macro pour créer un bout de MFC que tu voulais, voilà ce que ça donne :

Sub Macro6()
    Calculate
    Selection.FormatConditions.Delete
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=LC="""""
    Selection.FormatConditions(1).Interior.ColorIndex = 43
End Sub

Ça ira ?

C'est exactement ça.

Remplacer les 2 macros par :

Sub MFC1(plage As Range) ' MFC dates
    Application.ScreenUpdating = False
    plage.FormatConditions.Add Type:=xlExpression, Formula1:="=ET($D" & plage.Row & "<=F$2;$E" & plage.Row & ">=F$3)"
    plage.FormatConditions(plage.FormatConditions.Count).Interior.ColorIndex = 37
End Sub

Sub MFC2_4(plage As Range) ' MFC contenu
    Application.ScreenUpdating = False
    plage.FormatConditions.Add Type:=xlExpression, Formula1:="=F" & plage.Row & ">5"
    plage.FormatConditions(plage.FormatConditions.Count).Interior.ColorIndex = 45
    plage.FormatConditions.Add Type:=xlExpression, Formula1:="=ET(F" & plage.Row & ">0;F" & plage.Row & "<5)"
    plage.FormatConditions(plage.FormatConditions.Count).Interior.ColorIndex = 6
    plage.FormatConditions.Add Type:=xlExpression, Formula1:="=F" & plage.Row & "=5"
    plage.FormatConditions(plage.FormatConditions.Count).Interior.ColorIndex = 15
End Sub

Et dit moi ce que ça donne. Je ne suis pas sûr de comment il les ordonne sur 2003

eric

suite...

Si ça ne fonctionne pas essaie pour MFC1

Sub MFC1(plage As Range) ' MFC dates
    Application.ScreenUpdating = False
    plage.FormatConditions.Add Type:=xlExpression, Formula1:="=AND(RC4<=R2C,RC5>=R3C)"
    plage.FormatConditions(plage.FormatConditions.Count).Interior.ColorIndex = 37
End Sub

L'autre plantera toujours, je l'adapterais quand on aura trouvé la bonne syntaxe pour 2003.

Si ça ne va toujours pas tu remplaces AND par ET et tu retestes

Merci !!!

Ça marche...presque.

La MFC pour les barres de planning n'est pas exacte, du coup les barres de planning sont incorrectes.

En gros, là où la formule de la MFC devrait être : =ET(LC4<=L2C;LC5>=L3C),

la macro crée ça : =ET(L(65514)C4<=L2C(-2);L(65514)C5>=L3C(-2)) ...comme si 2 lignes avaient été supprimées à un moment.

(comme je suis un archéo, je travaille en style L1C1, je n'y comprends rien en style A1)

Même chose pour la MFC qui doit passer le fond d'une cellule de type "décomposition" en ornge.

Là où la formule de la MFC devrait être =LC>5

la macro crée ça : =L(65517)C(-2)>5

Par ailleurs, comme tu le subodorais, l'ordre de création est inversé entre v2003 et les versions suivantes.

EDIT : Nos msg se sont croisés. Je teste ce que tu viens de m'envoyer.

Super !

Ta nouvelle version pour la macro MF1 marche en style L1C1 si je la modifie comme ça :

Sub MFC1(plage As Range) ' MFC dates
    Application.ScreenUpdating = False
    plage.FormatConditions.Add Type:=xlExpression, Formula1:="=ET(LC4<=L2C;LC5>=L3C)"
    plage.FormatConditions(plage.FormatConditions.Count).Interior.ColorIndex = 37
End Sub

Ah, tu es en L1C1 ? Je commence à comprendre.

Il faut filer à la MFC la formule dans le type de notation activé.

Il faut donc tester et mettre la syntaxe attendue. On verra ça demain

eric

Oui.

Bonne nuit.

Et merci 2000 fois !

Bonjour,

voici la version compatible A1 et L1C1.

Plutôt que de doubler le nombre de formules je préfère changer le paramétrage de la notation choisie.

Ca fera moins de modif si tu ajoutes/modifies des MFC.

Donc si ça plante le temps du débogage, il faudra rétablir manuellement ton choix dans les options.

Si pas de plantage c'est restauré à la fin.

eric

13planning.zip (15.18 Ko)
Rechercher des sujets similaires à "creer mfc type predefini chaque ligne feuille"