A common request over the years for Microsoft has been to have the ability to link the print header or footer to a cell. As at the time of writing this functionality doesn’t exist, but we can add a one-line macro to fix that.

In the image below we have a report that we want to print out.

Based on the checkbox above cell A1 we want the words DRAFT or FINAL to display in the centre header. The checkbox above cell A1 is linked to sell A1 – see image below.

If the checkbox is ticked, we want the word FINAL to appear in the centre header.

The cell A2 has the following formula in it.

=IF(A1,”FINAL”,”DRAFT”)

This displays the word FINAL when the checkbox is ticked, and DRAFT when it isn’t.

Cell A2 has been named HdrMessage – see image below.

With this structure it is easy to add the word from cell A2 to the centre header.

A one-line macro needs to be added to ThisWorkbook module in the VBA window.

Follow these steps to make this happen.

1. Press Alt + F11 to open the VBA window.

2. On the left of screen click the ThisWorkbook module for this file – see image below.

3. On the right of screen, click the left dropdown and select Workbook see image below.

4. On the right side dropdown select the BeforePrint option. See image below.

5. Enter the following line of code between the Private Sub and End Sub lines. See image below.
ActiveSheet.PageSetup.CenterHeader = [HdrMessage]

6. You need to save the file as either a macro enabled file or a binary file type. In the Excel window press the F12 function key to open SaveAs and see these options. See image below.
pic 8

That’s it. Whenever you click print or use the Print Preview Full Page icon, the centre header will update based on the cell A2 which is based the checkbox – see images below.

Note: when you use the Print option in the file tab it WON’T update – not sure why.