Is it possible to lock/protect header and footers in Microsoft Excel 2007?
I have a protected macro-enabled worksheet in Excel 2007 and prefer to use headers/footers with it. Unfortunately I cannot figure out how to protect the header/footer when I lock the sheet. They don’t count as cells apparently. I do not want the header/footer data changed at all or even selectable and I prefer the way headers are handled (visually) in Excel rather than reducing the margins and adding more cells in place of the header/footer (just doesn’t fit as well with the "fit everything on one page when printing" option). Any suggestions?
Editor’s comment
If you need to recover a lost or forgotten password for a Microsoft Office document, database or spreadsheet…Click Here to download your password recovery software
Tagged with: cells • footers • header footer • margins
Filed under: Microsoft Excel
Like this post? Subscribe to my RSS feed and get loads more!
Here is one way to ‘lock’ the header and footer control, by disabling it in the WorkBook_Open event.
You indicate that the workbook is ‘macro enabled’, so if you are well versed in VBA please forgive the following step by step ‘how to’.
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.