How to protect header and footer in Excel?
In Excel, we can protect the cells quickly and easily, but, have you ever tried to protect the header and footer to prevent others modifying them? Normally, there is no direct way to solve this task in worksheet, but, the following VBA code may do you a favor.
Protect header and footer with VBA code
Protect header and footer with VBA code
Header and footer are useful for us when printing worksheet, to prevent them being changed by others, please do with following steps:
1. Hold down the ALT + F11 keys to open the Microsoft Visual Basic for Applications window.
2. Then double click ThisWorkbook under the VBAProject section to open a blank module, and then copy and paste the following code into the module:
VBA code: Protect header and footer
Option Explicit
Private Sub Workbook_BeforePrint(Cancel As Boolean)
Call ProtectHF
End Sub
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Call ProtectHF
End Sub
Private Sub Workbook_Open()
Call ProtectHF
End Sub
Private Sub ProtectHF()
'Updateby Extendoffice
Application.ScreenUpdating = False
ThisWorkbook.ActiveSheet.PageSetup.RightHeader = ""
ThisWorkbook.ActiveSheet.PageSetup.RightFooter = ""
ThisWorkbook.ActiveSheet.PageSetup.CenterFooter = "KTE-Sale-Report"
ThisWorkbook.ActiveSheet.PageSetup.CenterHeader = "Created by Skyyang"
ThisWorkbook.ActiveSheet.PageSetup.LeftHeader = ""
ThisWorkbook.ActiveSheet.PageSetup.LeftFooter = ""
Application.ScreenUpdating = True
End Sub
Note: You can insert the header or footer content into the corresponding location in the above code.
3. And then save and close this code, now, while clicking Save button to save this workbook, the modified header or footer content will be return to the original ones automatically if you have tried to change the header or footer , see screenshot:
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
