Is there a way to restrict header and footer of an excel form?

I prepared an excel form for my sales team to fill. I used data validations, protecting cells etc.. to make sure the form was foolproof. (I couldn't succeed in the end.) I put a "notes" section for them to write whatever they have to say that I couldn't foresee. Some of them started to write their notes to header and footer section instead of using "notes" box. How can I restrict them to edit header and footer?

2 Answers

Here's an option that will revert the header and footer to values you determine when a user hits "Save". Essentially, the VBA code tells Excel to set the header and footer contents to certain criteria before the document saves.

First you need to open the VBA editor. With your workbook open, press Alt+F11.

Double click on "ThisWorkbook" on the left to open a new window. Then just copy/paste the info below.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets With ws.PageSetup 'Replace the information after the equals sign with what you want. .LeftHeader = ActiveSheet.Range("A100").Value '.CenterHeader is the middle of the header. Put any text you want in quotes "" .CenterHeader = "Header Text" .RightHeader = ws.Name .LeftFooter = ActiveWorkbook.Name 'To leave a section blank, just use two quotes like in .CenterFooter .CenterFooter = "" .RightFooter = Date & " " & Time End With
Next ws
'No need to change any of the comments beginning with a '. These will not affect your code
End Sub

In this example, .CenterHeader = "Header Text" will revert the center header section to whatever you type between the "".

ws.Name puts the name of the worksheet into the left footer. ActiveWorkbook.Name puts the File name into the left footer Date & " " & Time fills in the date and time into the right footer. This will update with the date and time the user saves the document.

You could put in a sentence informing the user that anything entered into the header/footer will be lost when saving and to please use the notes section.

I unfortunately could not find anything to "lock" the header/footer from being edited in the first place.

Protecting the workbook will prevent changes to the header and footer. You need to format cells to unprotect the cells to only allow changes to the cells which the users are allowed to change, and protect the cells which you don't want the users to change. Then, protect the workbook, and you can optionally password protect the workbook to prevent the users from unprotecting it.