Créer automatiquement un calendrier à partir de dates d'une autre feuille

Bonjour,

Je vous écris pour obtenir de l'aide et des conseils afin de créer automatiquement un calendrier avec sous chaque mois un ensemble de dates spécifiques disponibles dans une autre feuille (éventuellement dans un autre classeur excel).

Vous trouverez en pièce jointe un fichier excel avec en Feuille 1 un exemple du résultat souhaité et en Feuille 2 les données sources à reporter dans la feuille 1.

J'ai rédigé un peu plus de détail sur le besoin dans la Feuille 1 et ajouter des commentaires sur les cellules utiles afin que vous puissiez mieux comprendre mon besoin.

Evidemment je reste à votre disposition si vous avez besoin d'informations additionnelles.

D'avance merci beaucoup pour le temps que vous prenez à lire mon besoin et j'espère qu'une solution est possible.

Cordialement

Bonsoir,

ci-jointe une proposition avec un calendrier glissant sur 3 ans

Merci beaucoup !

Le résultat est super et répond exactement à ce que je souhaitais

Pourriez-vous m'expliquer ce que vous avez fait : de la programmation ou l'utilisation de formules ? Je vois que le fichier est un .xlsm, cela veut il dire que c'est un fichier avec des macros ?

Si j'ai besoin de l'adapter ou de le modifier comment assurer cette maintenance ?

Enfin c'est un fichier qui sera mis à la disposition d'une autre personne :

• est ce que je peux lui donner le fichier en l'état ?

• quelles sont les règles à respecter pour éviter toutes dégradations du fichier ?

• Dois je poser des protections particulières et si oui lesquelles ?

• Avez-vous d'autres conseils à me suggérer ?

Encore une fois, merci beaucoup pour votre travail et pour avoir répondu aussi rapidement et efficacement à mon besoin. Je suis ravie !

Bien à vous

Bonjour,

Josianne a écrit :

Pourriez-vous m'expliquer ce que vous avez fait : de la programmation ou l'utilisation de formules ? Je vois que le fichier est un .xlsm, cela veut il dire que c'est un fichier avec des macros ?

Il s'agit d'une programmation VBA dont le code est situé dans le module de classe : Feuil7 (Feuille Calendrier).

Ce code utilise les références de nom spécifiques à la feuille calendrier et celles relatives aux classeur : Brand_name, Report_Type et Key_dates (voir menu Formules : Gestionnaire de noms)

Josianne a écrit :

Si j'ai besoin de l'adapter ou de le modifier comment assurer cette maintenance ?

La maintenance peut être assurée par une personne maîtrisant VBA ou via contact sur ce Forum.

Josianne a écrit :

quelles sont les règles à respecter pour éviter toutes dégradations du fichier ?

1- Conserver la feuille Calendrier telle quelle ainsi que les références de nom citées ci-dessus.

2- Dans la feuille "Détail", ne pas supprimer les cellules auxquelles les noms : Brand_name, Report_Type et Key_dates font référence, c'est à dire les celulles B2, D2,F2, G2, H2,I2,J2,L2.

Bonjour,

Merci beaucoup pour vos réponses.

J'ai ré-ouvert ce matin le fichier depuis un PC professionnel et le calendrier ne fonctionne plus correctement, la chronologie de mois n’apparaît ne fonctionne plus et les colonnes de sont plus renseignées. je vous joins dans le word attaché une copie d'écran de ce que j'obtiens et la ligne de code où serait l'erreur (issue du débogage).

Sur ce PC je suis en Office 365 ProPlus et je reçois un message d'alerte de sécurité à l'ouverture (cf. fichier word)

J'ai une question: si je modifie/mets à jour le titre des colonnes de la feuille de détail, est ce que cela se répercutera bien dans la feuille Calendrier ?

Avec mes remerciements

Josianne

Bonjour,

Je pense que vous êtes sur une version d'Excel en Anglais.

1- cliquez sur la barre de défilement associée au mois, ça devrait fonctionner et afficher le mois en anglais

2- vérifiez la formule de la cellule D3 car pour le moment elle affiche #VALEUR avec le libellé du mois en français.

Si vous êtes bien en version anglaise, il faudra dans le code modifier les instructions :

[dates_planning].Cells(j1_min).Offset(-1).FormulaR1C1 = "=UPPER(TEXT(R[1]C,""aaaa""))"

[dates_planning].Cells(j).Offset(-1).FormulaR1C1 = "=UPPER(TEXT(R[1]C,""aaaa""))"

en :

[dates_planning].Cells(j1_min).Offset(-1).FormulaR1C1 = "=YEAR(R[1]C)"

[dates_planning].Cells(j).Offset(-1).FormulaR1C1 = "=YEAR(R[1]C)"

Josianne a écrit :

J'ai une question: si je modifie/mets à jour le titre des colonnes de la feuille de détail, est ce que cela se répercutera bien dans la feuille Calendrier ?

A priori oui.

Bonjour,

Merci pour votre réponse et désolée pour le délai à vous faire mon retour.

Effectivement il s'agit bien d'une version anglaise d'excel. J'ai donc fait les modifications que vous me proposez et l'erreur a disparu. Par contre le comportement du calendrier n'est pas optimal:

le changement de mois depuis l'onglet gauche ne se répercute pas correctement sur la ligne des mois

les dates ne sont pas correctement mise à jour lorsque je change de mois et que je reviens au mois précédent

je suis parfois obligé de revenir sur la feuille de détail et ensuite de retourner à la feuille Calendrier pour que la mise à jour soit correcte

Est-il possible d'améliorer ce comportement ?

Est-il aussi possible de faire en sorte que le programme détecte la version d'excel et sélectionne le type d'affichage correct et fonction de la langue ? Les personnes qui vont utiliser ce fichier auront soit une version française, soit une version anglaise d'excel.

De plus est-il possible d'automatiser la mise en forme du calendrier avec le choix d'une police et la grille pour toutes les cellules ? Actuellement jusqu'en ligne 76 j'ai bien la grille et une police Century Gothic. Au-delà la grille disparuait et la police devient Times New Roman.

En pièce jointe la version du fichier que j'utilise avec la mise à jour pour la comptabilité avec la version Anglaise

Bien cordialement

Josianne a écrit :

le changement de mois depuis l'onglet gauche ne se répercute pas correctement sur la ligne des mois

J'ai effectué quelques petites modifications. Voir si ça va résoudre le problème.

Josianne a écrit :

les dates ne sont pas correctement mise à jour lorsque je change de mois et que je reviens au mois précédent

de quelles dates s'agit-il ?

Bonjour à tous

Une solution PowerQuery (intégré à 2016+) :

Un liste déroulante permet de choisir la période(année mois) de début de la requête PowerQuery

Cela devrait fonctionner sur la version US

Bonjour Thev et Chris,

Merci pour vos retours. Ci dessous mes réponses

1. Aux questions de Thev

Le problème 1 est résolu. Merci beaucoup.

Pour le problème 2, vous trouverez en pièce jointe un fichier qui décrit comment dupliquer l'anomalie avec des copies d'écran.

2. A la proposition Power Query de Chris

Ce que j'aime beaucoup dans cette solution c'est que lorsque je passe d'1 mois à l'autre le calendrier s'ajuste et n'affiche que les lignes ayant des dates sur le mois sélectionné. C'est vraiment bien car cela évite de descendre dans liste pour voir apparaître les dates.

Par contre les dates du mois sélectionné s'affiche correctement (format dd/MM/yyyy) mais les dates des mois suivants restent en format numérique

Mes questions ensuite sont:

Peut-on faire une mise en forme du calendrier à l'image de la proposition de Thev et qui correspond à ma demande initiale

Est ce qu'on peut aussi afficher le calendrier sur 3 ans glissant

Cordialement

Josianne

Re

Josianne a écrit :

Par contre les dates du mois sélectionné s'affiche correctement (format dd/MM/yyyy) mais les dates des mois suivants restent en format numérique

il suffit de paramétrer tout l'onglet en format date puis de modifier le format des 4 premières colonnes du tableau de résultats (Calendrier) et les cellules A1 et B1 en Standard

Josianne a écrit :

Peut-on faire une mise en forme du calendrier à l'image de la proposition de Thev et qui correspond à ma demande initiale

Peux-tu préciser ?

Si c'est une ligne pour les années et une pour les mois, non.

C'est un tableau structuré qui respecte la norme à savoir une et une seule ligne de titres

Si c'est les couleurs : faisable par MFC

Josianne a écrit :

Est ce qu'on peut aussi afficher le calendrier sur 3 ans glissant

Le requête prend automatiquement les Années et mois existants

Si tu as plus de 3 ans on peut borner

Sinon construire des colonnes vides est faisable mais va alourdir...

Merci pour vos réponses.

Ci dessous mes réponse et mes questions additionnelles

Ok pour la mise en forme des dates. J'ai suivi vos conseils, cela fonctionne. Merci.

Pour la mise en forme: oui j'aimerai que les dates s'affichent avec des couleurs différentes comme présenté dans l'onglet Feuille 1 - Calendrier. Que veux dire MFC ?

Pour la prise en compte des mois et année: j'ai testé en ajoutant une ligne additionnelle à l'onglet détail avec des dates en nov2021 et janv 2022. Pourtant dans l'onglet PowerQuery la liste de choix dans le menu déroulant s'arrête à 2021janvier. Les mois de novembre et décembre 2021 et Janvier 2022 ne sont pas proposés. Je vois que vous avez créé un onglet Tech avec une liste d'annéemois et une liste de titre de dates. Est ce que c'est cet onglet qui donne les bornes ?

Cordialement

RE

MFC : mise ne forme conditionnelle

Quand on active l'onglet PowerQuery ou que l'on choisit un autre période en B1, la requête se réactualise par VBA

La liste des périodes est aussi actualisée à l'activation de l'onglet pour proposer toutes les périodes existantes

On peut actualiser manuellement par Données, Actualiser Tout si le VBA est désactivé

Josianne a écrit :

Pour le problème 2, vous trouverez en pièce jointe un fichier qui décrit comment dupliquer l'anomalie avec des copies d'écran.

Je n'ai pas cette anomalie sur ma version d'Excel. J'ai néanmoins procédé à une petite modif qui résoudra peut être le problème.

Bonjour Chris et Thev,

Je peux enfin revenir vers vous pour vous donner des nouvelles de mon calendrier. Au final mon client a préféré la version de Chris, et l'a testé depuis début septembre.

Nous rencontrons un problème gênant: lorsqu'on fait des allers-retours sur des choix de dates (cellule B1), nous avons constaté que les colonnes de dates se désorganisent et les mois ne sont plus dans l'ordre. J'ai regardé les requêtes et ne vois pas comment corriger ce problème.

Ensuite j'ai suivi vos conseils pour que le format de dates soient différents et je constate que dès que je change la sélection de dates à un mois antérieur à celui précédemment affiché, je perds le format de date sur les nouvelles colonnes affichées.

Encore un très grand merci pour votre aide.

Josianne

Bonjour

Clic droit depuis une cellule du tableau de résultat dans Excel : Outils de tableau, Création, Propriétés : conserver les dispositions, filtre/tri des colonnes doit être décoché,

Cela évité qu'il tente de conserver l'ordre initial des colonnes et ajoute à droite

Je n'ai pas testé sur 2016. Cela fonctionne sur l'add on (2010-2013), mais sur 365 j'ai déjà eu un bug un décochant (pas revérifié récemment).

Pour le format, comme je l'avais dit le 15/06/2020 à 18:49, je pense qu'il faut formater toutes les colonnes Excel en date. et juste modifier les quatre 1ères

On ne sait pas dans la requête quels sera le nombre de colonnes ni leur titre pour typer

Je regarderai pour typer toutes les colonnes à partir de la cinquième

Bonjour,

La case 'Conserver les dispositions, filtres...' est bien décochée et pourtant l'ordre est perturbé dès que j' sélectionne une date antérieure puis reviens au mois courant ou plus. La colonne du mois précédemment sélectionnée est ajoutée en bout de tableau à droite.

Et pour la mise en forme des formats de cellules j'avais bien suivi vos instructions en date 15 Juin et pourtant cela ne fonctionne pas sur mon poste (version 2016) ni sur celui de mon client (version 365).

Cordialement

Bonjour,

Avez-vous une solution à me proposer pour conserver le format de date. Je vous confirme que dès que je sélectionne une période antérieure à la période affichée, les dates sont affichées avec le format classique JJ/MM/AAAA et plus avec celui que je souhaite JJ-MMM. Il semble donc que dès qu'une nouvelle colonne est affichée, elle prend un format par défaut.

Pour mon problème d'ordonnancement des colonnes, je pense l'avoir résolu. En rechargeant des nouvelles données j'ai constaté que le problème vient du texte contenu dans la colonne A. Le comportement du calendrier est perturbé lorsque dans ces cellules j'ai du texte qui commence par Activated avec un A majuscule ou par BLI avec un B majuscule ou Aluminium avec un A majuscule. Dès que je passe ces mots en minuscule, le problème disparait. Avez-vous une explication ?

Enfin, dernière question pour aujourd'hui (:-)): il m'est demandé de poster ce fichier dans un drive avec ouverture du fichier avec Excel 365 Online. Or je constate que dans ce cas les macros ne sont pas prises en compte et donc le calendrier ne fonctionne plus comme souhaité. Existe t-il une solution ou confirmez-vous cette limitation d'excel online ?

Avec mes remerciements pour votre aide. Cordialement

Bonjour

Pour la colonne A j'ai ajouté une conversion en majuscules : PowerQuery, comme VBA, est sensible à la casse ce qui perturbe le classement, cette colonne étant prise en 1er critère

Pour le format des dates, effectivement le tableau semble vivre sa propre vie car même en appliquant un style de cellule à tout l'onglet, il garde son format

(A propos de styles, le fichier que tu avais fourni étant pollué par 806 styles personnalisés, je les ai supprimé)

J'ai appliqué au tableau une MFC qui a l'air de fonctionner (peut-être à adapter si vos paramètres régionaux sont US)

Sinon on pourrait convertir en texte dans PowerQuery mais je ne suis pas fan de ce type de solution...

Le VBA ne fonctionne pas sur 365 online et, je viens de tester, PowerQuery non plus.
C'est une version gratuite donc limitée...

Merci Chris, je vais tester cette nouvelle version du fichier dès aujourd'hui et vous tiens eu courant du résultat. J'avais aussi fait cette annulation des styles personnalisés, ils proviennent du couper/coller que je fais du fichier de données source (:-)).