Macro d'événement pour changer l'en-tête Excel - Conseils Excel

Table des matières

Donna du Missouri a demandé:

Avez-vous une idée de la façon dont je peux obtenir le chemin où le document est classé indiqué dans l'en-tête ou le pied de page - ou n'importe où dans le document. Je peux obtenir le nom du fichier avec & f mais je ne peux pas comprendre comment faire le chemin.

Tout d'abord, je comprends que Microsoft a ajouté cette fonctionnalité à Excel XP et je leur offre des félicitations car c'est un problème fréquemment posé. Les lecteurs qui ont déjà mis à niveau vers Office XP n'auront pas besoin des techniques de l'astuce de cette semaine pour résoudre ce problème, mais elles seront toujours utiles pour résoudre d'autres problèmes.

La solution à cette astuce est un type spécial de macro appelé macro de gestionnaire d'événements. Nous allons brièvement prendre le contrôle d'Excel chaque fois qu'il est sur le point d'imprimer notre classeur et d'ajouter le chemin actuel à l'en-tête.

De nombreux utilisateurs d'Excel ont essayé d'enregistrer des macros simples. Les macros sont stockées dans un module appelé Module1 ou Module2 et font partie de votre projet. Aujourd'hui, je vais parler des macros de gestionnaire d'événements. Ces macros résident sur un module de code spécial associé à chaque feuille de calcul ou classeur.

Les astuces précédentes telles que la pointe Enter Excel Time Without the Colon ont traité de l'événement Worksheet_Change. L'astuce d'aujourd'hui nécessite que nous ajoutions du code à l'événement BeforePrint du classeur.

Le code ajouté à un événement sera exécuté chaque fois que cet événement est "déclenché". Dans ce cas, à chaque fois que le classeur Excel est imprimé, avant le début de l'impression, Excel transfère le contrôle au code VBA et permet à tout ce que vous pouvez spécifier dans le code VBA de se produire automatiquement avant l'impression.

Je vais supposer que vous êtes nouveau dans les macros de gestionnaire d'événements. Je vais expliquer exactement comment arriver au bon endroit pour entrer cette macro.

J'ai un classeur appelé «Tip055 Sample.xls». Avec le classeur chargé dans Excel, je vais appuyer sur alt = "" + F11 pour démarrer l'éditeur Visual Basic. L'apparence par défaut de l'éditeur est celle illustrée à droite. Sur la gauche, vous voyez généralement un volet Projet empilé au-dessus d'un volet Propriétés. La majeure partie du côté droit de l'écran comprend un volet de code. Si vous n'avez pas de macros dans votre classeur, votre volet de code sera gris comme indiqué à droite.

J'ai ajouté des mots de script en italique bleu à l'image pour identifier les trois volets - vous ne les verrez pas dans votre exemple.

Il est important que vous puissiez voir le volet Projet dans l'éditeur VB. Si votre vue de l'éditeur VB n'inclut pas le volet projet, appuyez sur Ctrl + R pour afficher le volet projet. Ou cliquez sur l'icône de la barre d'outils ci-dessous:

Le volet projet affichera un projet pour chaque classeur Excel ouvert et chaque complément installé. Cliquez sur le signe plus gris à côté du nom de votre classeur pour développer le projet de votre classeur. Ensuite, cliquez sur le gris plus en regard du dossier Objets Microsoft Excel pour développer le dossier d'objets. Vous devriez maintenant voir une entrée pour chaque feuille de calcul et une entrée appelée ThisWorkbook.

Cliquez avec le bouton droit sur l'entrée de ThisWorkbook et sélectionnez Afficher le code dans le menu contextuel.

Vous aurez probablement maintenant un grand volet de code blanc vierge occupant le côté droit de l'écran. Il y a deux listes déroulantes en haut du volet de code qui diront (Général) et (Déclarations).

Dans le menu déroulant de gauche, sélectionnez Classeur.
La liste déroulante de droite contient désormais tous les événements programmables associés au classeur. Il y a des événements ici qui exécuteront du code à chaque fois que le classeur est ouvert, activé, désactivé, etc. Aujourd'hui, nous voulons écrire du code dans l'événement BeforePrint, alors sélectionnez BeforePrint dans le menu déroulant de droite.

Notez que chaque fois que vous sélectionnez quelque chose dans la liste déroulante de droite, l'éditeur VBA écrit la ligne de début et de fin de code dans le module de code pour vous. La première fois que vous changez la liste déroulante de gauche en Workbook, vous avez probablement reçu les débuts d'un sous-programme Workbook_Open par défaut. Si vous n'allez pas écrire une procédure Workbook_Open, vous devez envisager de supprimer cette procédure vide.

Passons maintenant à l'écriture du code VBA. Il existe quelques variables utiles que vous pouvez utiliser.

ActiveWorkbook.Path renverra le chemin du classeur. Cela pourrait ressembler à "C: Mes documents MrExcel".
ActiveWorkbook.FullName renverra le chemin et le nom de fichier du classeur. Cela peut ressembler à "C: Mes documents MrExcel Tip055 Sample.xls".

Vous pouvez affecter cette variable à l'une des 6 positions suivantes:

Worksheets("Sheet1").PageSetup.LeftHeader = ActiveWorkbook.FullName Worksheets("Sheet1").PageSetup.CenterHeader = ActiveWorkbook.FullName Worksheets("Sheet1").PageSetup.RightHeader = ActiveWorkbook.FullName Worksheets("Sheet1").PageSetup.LeftFooter = ActiveWorkbook.FullName Worksheets("Sheet1").PageSetup.CenterFooter = ActiveWorkbook.FullName Worksheets("Sheet1").PageSetup.RightFooter = ActiveWorkbook.FullName

Voici trois exemples de macros possibles.

Cette macro aura le chemin et le nom de fichier ajoutés comme pied de page droit de la feuille de calcul active:

Private Sub Workbook_BeforePrint(Cancel As Boolean) ActiveSheet.PageSetup.RightFooter = ActiveWorkbook.FullName End Sub

Cette macro aura le chemin ajouté comme en-tête gauche de Sheet1 et comme pied de page central de Sheet2:

Private Sub Workbook_BeforePrint(Cancel As Boolean) Worksheets("Sheet1").PageSetup.LeftHeader = ActiveWorkbook.Path Worksheets("Sheet2").PageSetup.CenterFooter = ActiveWorkbook.Path End Sub

Si vous avez tendance à utiliser l'option "Tout le classeur" lors de l'impression, cette version ajoutera le nom complet comme pied de page central à toutes les feuilles:

Private Sub Workbook_BeforePrint(Cancel As Boolean) For Each Sh In ActiveWorkbook.Worksheets Sh.PageSetup.CenterFooter = ActiveWorkbook.FullName Next Sh End Sub

Suivez l'un de ces exemples ou créez l'un des vôtres. Lorsque vous avez terminé, fermez l'éditeur VBA avec Fichier> Fermer et retourner à Microsoft Excel.

Chaque fois que vous imprimez une feuille de calcul, le code s'exécute et insère le chemin d'accès actuel dans l'en-tête ou le pied de page approprié que vous avez indiqué dans le code VBA.

Quelques notes et mises en garde:

Les utilisateurs novices d'Excel auront peu d'idée que ce code se trouve dans le classeur. Lorsqu'ils ouvrent le classeur, ils peuvent recevoir l'avertissement de sécurité indiquant que le fichier contient des macros, mais il n'y aura aucun avertissement lorsque le code VBA claque ce qu'ils avaient comme pied de page central et y place le chemin d'accès. Cela peut entraîner des brûlures d'estomac. Imaginez que dans 5 ans, quelqu'un utilise votre classeur et que le nouveau gestionnaire souhaite que le nom de fichier passe du pied de page central au pied de page droit. Cette personne sait peut-être modifier manuellement les paramètres dans Fichier> Configuration de la page, mais si elle ne sait pas que le code est là, cela la rendra difficile car le code change continuellement ses pieds de page.

It is really unlikely that they will be able to find your code module, but just in case they do, you might want to leave a comment in the code module that directs them back to this website for an explanation.

You also might want to add a comment to cell A1 reminding yourself that there is an event handler set up to change the print headers.
There is a setting within VBA which prevents events from running. If your macro suddenly stops working, t is common to find that something within VBA has turned this setting to prevent the events from running. The common scenario is that a programmer starts a macro with:
```
Application.EnableEvents = False '… macro here… Application.EnableEvents = True
```
Lorsqu'une erreur se produit dans la macro, les événements ne sont jamais réactivés. Lorsque cela se produit, il y a peu d'avertissement. Si vous pensez que vos gestionnaires d'événements ne sont pas exécutés, accédez à l'éditeur Visual Basic. Appuyez sur Ctrl + g pour faire apparaître un volet immédiat. Dans le volet immédiat, saisissez:
```
Print Application.EnableEvents
```
et appuyez sur Entrée. Si vous trouvez que cela est défini sur False, tapez la ligne suivante dans le volet immédiat:
```
Application.EnableEvents = True
```
et appuyez sur Entrée.

Merci à Donna pour une excellente question. Dans le processus d'explication de la réponse, c'était une excellente occasion de développer le concept des gestionnaires d'événements dans VBA.