How to Protect Headers and Footers (Excel 2007-2010 Training)

The only way to do it, is through a macro.
Copy the following code to the clipboard:

Private Sub Workbook_Open()
Application.CommandBars(“Worksheet menu bar”). _
Controls(“View”).Controls(“&Header and Footer…”).Enabled = False
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.CommandBars(“Worksheet menu bar”). _
Controls(“View”).Controls(“&Header and Footer…”).Enabled = True
End Sub

Press ALT + F11

Double click ‘THIS WORKBOOK’ in the Microsoft Excel Objects in the upper left quadrant.

Paste both event handlers into the WorkBook module editing area to the right.

Close the VBE and return to the worksheet.

Save the workbook.

Now, whenever the workbook is opened, the Header and Footer option under the View tab will be disabled (grayed out). When the workbook is closed, the control will be enabled again so it will be accessible in all subsequently opened workbooks.

If you wish to have access to the header and footer for ‘maintenance’, copy the following macro to the clipboard:

Sub HeadnFoot()
If Application.CommandBars(“Worksheet menu bar”). _
Controls(“View”).Controls(“&Header and Footer…”).Enabled = False Then
Application.CommandBars(“Worksheet menu bar”). _
Controls(“View”).Controls(“&Header and Footer…”).Enabled = True
Else
Application.CommandBars(“Worksheet menu bar”). _
Controls(“View”).Controls(“&Header and Footer…”).Enabled = False
End If
End Sub

ALT + F11 to access the VBE.

INSERT > MODULE

Paste the macro into the module editing area to the right.

Close the VBE.

Press ALT + F8

When the Macros window opens, highlight this macro and click ‘Options..’.

Enter a letter to be used as a keyboard shortcut and click ‘OK’.

Close the Macros window.

Save the workbook.

Now, when you open the workbook and need to modify the header or footer, press CTRL + your shortcut letter and the control will be enabled. Press the keyboard shortcut again and the control will again be disabled. It is a ‘toggle’ macro.

Sincerely,
Harry