Thank you very much!
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.
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:
You are guest
or post as a guest, but your post won't be published automatically.
- To post as a guest, your comment is unpublished.· 11 days agoAfter shutting down and reopening the excel file, the header/footer can still be deleted. So how to fix this?
Thank you very much!
- To post as a guest, your comment is unpublished.· 2 years agonotas: Puede insertar el contenido del encabezado o pie de página en la ubicación correspondiente en el código anterior.
No entiendo ese paso , por favor si pueden explicarme, lo agradecería.
- To post as a guest, your comment is unpublished.· 2 years agoHi used this with auto sheet name, file location, and page number and when saving it requires you to click in each field to print correctly.
- To post as a guest, your comment is unpublished.· 4 years agoHi,
Love the code to protect the header and footer, is there a way to change the font size, style, and color in the code so it is always replaced with a larger font?