Macro di eventi per modificare l'intestazione di Excel - Suggerimenti per Excel

Sommario

Donna del Missouri ha chiesto:

Hai idea di come posso ottenere il percorso in cui è archiviato il documento indicato nell'intestazione o nel piè di pagina o in qualsiasi punto del documento. Posso ottenere il nome del file con & f ma non riesco a capire come eseguire il percorso.

Prima di tutto, capisco che Microsoft ha aggiunto questa funzionalità a Excel XP e offro loro i miei complimenti perché è un problema frequente. I lettori che hanno già effettuato l'aggiornamento a Office XP non avranno bisogno delle tecniche nel suggerimento di questa settimana per risolvere questo problema, ma saranno comunque utili per risolvere altri problemi.

La soluzione a questo suggerimento è un tipo speciale di macro chiamato macro del gestore di eventi. Prenderemo brevemente il controllo di Excel ogni volta che sta per stampare la nostra cartella di lavoro e aggiungere il percorso corrente all'intestazione.

Molti utenti di Excel si sono dilettati nella registrazione di semplici macro. Le macro vengono memorizzate in un modulo chiamato Module1 o Module2 e diventano parte del progetto. Oggi parlerò delle macro del gestore di eventi. Queste macro risiedono su un modulo di codice speciale associato a ogni foglio di lavoro o cartella di lavoro.

Suggerimenti passati come la punta Enter Excel Time Without the Colon hanno affrontato l'evento Worksheet_Change. Il suggerimento di oggi richiede che aggiungiamo del codice all'evento BeforePrint della cartella di lavoro.

Il codice aggiunto a un evento verrà eseguito ogni volta che l'evento viene "attivato". In questo caso, ogni volta che viene stampata la cartella di lavoro di Excel, prima che inizi la stampa, Excel passa il controllo al codice VBA e consente che tutto ciò che è possibile specificare nel codice VBA avvenga automaticamente prima della stampa.

Presumo che tu sia nuovo alle macro del gestore di eventi. Spiegherò esattamente come arrivare al posto giusto per entrare in questa macro.

Ho una cartella di lavoro chiamata "Tip055 Sample.xls". Con la cartella di lavoro caricata in Excel, premerò alt = "" + F11 per avviare l'editor di visual basic. L'aspetto predefinito dell'editor è come mostrato a destra. A sinistra, di solito vedi un riquadro Progetto impilato sopra un riquadro Proprietà. La maggior parte del lato destro dello schermo include un riquadro del codice. Se non sono presenti macro nella cartella di lavoro, il riquadro del codice sarà grigio come mostrato a destra.

Ho aggiunto parole in corsivo blu all'immagine per identificare i tre riquadri: non li vedrai nel tuo esempio.

È importante poter vedere il riquadro del progetto nell'editor VB. Se la tua visualizzazione dell'editor VB non include il riquadro del progetto, premi Ctrl + R per visualizzare il riquadro del progetto. In alternativa, fare clic sull'icona della barra degli strumenti mostrata di seguito:

Il riquadro del progetto mostrerà un progetto per ogni cartella di lavoro Excel aperta e ogni componente aggiuntivo installato. Fare clic sul segno più grigio accanto al nome della cartella di lavoro per espandere il progetto per la cartella di lavoro. Quindi fare clic sul segno più grigio accanto alla cartella Oggetti di Microsoft Excel per espandere la cartella dell'oggetto. Ora dovresti vedere una voce per ogni foglio di lavoro e una voce chiamata ThisWorkbook.

Fare clic con il pulsante destro del mouse sulla voce per ThisWorkbook e selezionare Visualizza codice dal menu a comparsa.

Ora probabilmente avrai un grande riquadro del codice bianco vuoto che occupa il lato destro dello schermo. Ci sono due menu a discesa nella parte superiore del riquadro del codice che diranno (Generale) e (Dichiarazioni).

  • Dal menu a discesa a sinistra, seleziona Cartella di lavoro.
  • Il menu a discesa a destra è ora popolato con tutti gli eventi programmabili associati alla cartella di lavoro. Ci sono eventi qui che eseguiranno il codice ogni volta che la cartella di lavoro viene aperta, attivata, disattivata, ecc. Oggi vogliamo scrivere codice nell'evento BeforePrint, quindi seleziona BeforePrint dal menu a discesa a destra.

Nota che ogni volta che selezioni qualcosa dal menu a discesa a destra, l'editor VBA scrive la riga di codice iniziale e finale nel modulo di codice per te. La prima volta che modifichi il menu a discesa a sinistra in Cartella di lavoro, probabilmente hai ricevuto per impostazione predefinita l'inizio di una subroutine Workbook_Open. Se non hai intenzione di scrivere una procedura Workbook_Open, dovresti considerare di eliminare questa procedura vuota.

Ora, scriviamo il codice VBA. Ci sono un paio di variabili utili che puoi usare.

  • ActiveWorkbook.Path restituirà il percorso della cartella di lavoro. Potrebbe apparire come "C: My Documents MrExcel".
  • ActiveWorkbook.FullName restituirà il percorso e il nome del file della cartella di lavoro. Potrebbe essere simile a "C: My Documents MrExcel Tip055 Sample.xls".

È possibile assegnare questa variabile come una in una delle seguenti 6 posizioni:

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

Ecco tre possibili macro di esempio.

Questa macro avrà il percorso e il nome del file aggiunti come piè di pagina a destra del foglio di lavoro attivo:

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

Questa macro avrà il percorso aggiunto come intestazione sinistra di Sheet1 e come piè di pagina centrale di Sheet2:

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

Se tendi a utilizzare l'opzione "Intera cartella di lavoro" durante la stampa, questa versione aggiungerà il nome completo come piè di pagina centrale a tutti i fogli:

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

Segui uno di questi esempi o creane uno tuo. Quando hai finito, chiudi l'editor VBA con File> Chiudi e torna a Microsoft Excel.

Ogni volta che si stampa un foglio di lavoro, il codice verrà eseguito e inserirà il percorso corrente nell'intestazione o nel piè di pagina appropriato che hai indicato nel codice VBA.

Alcune note e avvertenze:

  • Gli utenti inesperti di Excel avranno poca idea che questo codice sia nella cartella di lavoro. Quando aprono la cartella di lavoro, potrebbero ricevere l'avviso di sicurezza che il file contiene macro, ma non ci sarà alcun avviso quando il codice VBA colpisce quello che avevano come piè di pagina centrale e inserisce il nome del percorso lì. Questo può portare a bruciore di stomaco. Immagina che tra 5 anni qualcuno stia usando la tua cartella di lavoro e il nuovo manager voglia che il nome del file si sposti dal piè di pagina centrale al piè di pagina destro. Questa persona potrebbe sapere di modificare manualmente le impostazioni in File> PageSetup, ma se non sanno che il codice è lì, li farà impazzire poiché il codice cambia continuamente i loro piè di pagina.

    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

    Quando si verifica un errore nella macro, gli eventi non vengono mai riattivati. Quando ciò accade, ci sono pochi avvertimenti. Se sospetti che i tuoi gestori di eventi non vengano eseguiti, vai all'editor di visual basic. Premi Ctrl + g per aprire un riquadro immediato. Nel riquadro immediato, digita:

    Print Application.EnableEvents

    e premi invio. Se trovi che questo è impostato su False, digita la seguente riga nel riquadro immediato:

    Application.EnableEvents = True

    e premi invio.

Grazie a Donna per una bella domanda. Nel processo di spiegazione della risposta, è stata una grande opportunità per espandere il concetto di gestori di eventi in VBA.

Articoli interessanti...