Event Macro To Change Excel Header

January 29, 2002 - by Bill Jelen

Donna from Missouri asked:

Do you have any idea how I can get the path where the document is filed indicated in the header or footer - or anywhere in the document. I can get the file name in with &f but I am unable to figure out how to do the path.

First of all, I understand that Microsoft has added this functionality to Excel XP and I offer kudos to them as it is a frequently asked problem. Any readers who have already upgraded to Office XP will not need the techniques in this week's tip to solve this problem, but they will still be useful to solve other problems.

The solution to this tip is a special type of macro called an event handler macro. We are going to briefly take control of Excel every time that it is about to print our workbook and add the current path to the header.

Many Excel users have dabbled in recording simple macros. The macros are stored in a module called Module1 or Module2 and become part of your project. Today, I am going to discuss event handler macros. These macros reside on a special code module that is associated with each worksheet or workbook.

Past tips such as the Enter Excel Time Without the Colon tip have dealt with the Worksheet_Change event. Today's tip requires that we add some code to the Workbook's BeforePrint event.

Code added to an event will be run whenever that event is "fired". In this case, anytime that the Excel workbook is printed, before the printing starts, Excel turns control over to the VBA code and allows anything you can specify in VBA code to automatically happen before printing.

I am going to assume you are new to event handler macros. I am going to walk through exactly how to get to the right place in order to enter this macro.

I have a workbook called 'Tip055 Sample.xls'. With the workbook loaded in Excel, I am going to hit Alt + F11 to start to visual basic editor. The default look of the editor is as shown at right. On the left, you usually see a Project pane stacked on top of a Properties pane. Most of the right side of the screen includes a Code Pane. If you have no macros in your workbook, your code pane will be gray as shown at the right.

I've added blue italic script words to the image to identify the three panes - you will not see these in your example.

It is important that you be able to see the Project Pane in the VB editor. If your view of the VB editor does not include the project pane, then hit Ctrl+R to view the Project Pane. Or, click the toolbar icon shown below:

Project Pane Toolbar Icon

VBAProject Treeview

The project pane will show a project for every open Excel workbook and every installed Add-In. Click the grey plus next to the name of your workbook to expand the project for your workbook. Then, click the grey plus next to the Microsoft Excel Objects folder to expand the object folder. You should now see one entry for every worksheet and one entry called ThisWorkbook.

Right-click the entry for ThisWorkbook and select View Code from the pop-up menu.

You will now probably have a large blank white code pane taking up the right side of the screen. There are two dropdowns at the top of the code pane that will say (General) and (Declarations).

From the left dropdown, select Workbook.
The right dropdown is now populated with all of the programmable events associated with the workbook. There are events here that will run code any time the workbook is opened, activated, deactivated, etc. Today, we want to write code in the BeforePrint event, so select BeforePrint from the right dropdown.

Note that every time you select something from the right dropdown, the VBA editor writes the beginning and ending line of code into the code module for you. The first time that you change the left dropdown to Workbook, you probably received the beginnings of a Workbook_Open subroutine by default. If you are not going to write a Workbook_Open procedure, you should consider deleting this empty procedure.

Now, on to writing the VBA code. There are a couple of useful variables that you can use.

ActiveWorkbook.Path will return the path of the workbook. It might look like "C:\My Documents\MrExcel".
ActiveWorkbook.FullName will return the path and filename of the workbook. It might look like "C:\My Documents\MrExcel\Tip055 Sample.xls".

You can assign this variable to be one in one of the following 6 positions:

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

Here are three possible sample macros.

This macro will have the path and filename added as the right footer of the active worksheet:

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

This macro will have the path added as the left header of Sheet1 and as the center footer of Sheet2:

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

If you tend to use the "Entire Workbook" option when printing, this version will add the full name as the center footer to all sheets:

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

Follow one of these examples or create one of your own. When you are finished, close the VBA editor with File > Close and Return to Microsoft Excel.

Anytime that you print a worksheet, the code will run and insert the current path into the appropriate header or footer that you indicated in the VBA code.

Some notes and cautions:

Novice Excel users will have little idea that this code is in the workbook. When they open the workbook, they may get the security warning that the file contains macros, but there will be no warning when the VBA code whacks whatever they had as the center footer and puts the pathname there. This can lead to heartburn. Imagine 5 years from now someone is using your workbook and the new manager wants the filename to move from the center footer to the right footer. This person may know to manually change the settings in File > PageSetup, but if they do not know the code is there, it will drive them batty as the code continually changes their footers back.

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
```
When an error occurs in the macro, the events never get turned back on. When this happens, there is little warning. If you suspect that your event handlers are not being executed, go to the visual basic editor. Hit Ctrl+g to bring up an immediate pane. In the immediate pane, type:
```
Print Application.EnableEvents
```
and hit enter. If you find that this is set to False, then type the following line in the immediate pane:
```
Application.EnableEvents = True
```
and hit enter.

Thanks to Donna for a great question. In the process of explaining the answer, it was a great opportunity to expand on the concept of Event Handlers in VBA.