Prevent Header or Footer Change

[zKy]

How do I prevent users from changing the header or footer of a spreadsheet? Neither worksheet nor workbook protection works.

 

[KWMSeattle]

Do your spreadsheets each have different headers/footers?

 

[zKy]

Do your spreadsheets each have different headers/footers?

Yes

 

[KWMSeattle]

I assume your users need to change some things in the workbook? Because otherwise it would be too easy to just password protect the workbook.

 

[zKy]

Certain cells are unlocked.

Protecting the entire workbook doesn't prevent users from changing the headers and footers though.

I assume your users need to change some things in the workbook? Because otherwise it would be too easy to just password protect the workbook.

 

[KWMSeattle]

You could place some VBA code in your workbook's Before_Save event so that even if a user changes the header/footer it will revert to your settings each time they save the file. Something like this might work:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
    With ws.PageSetup
        .LeftHeader = ActiveSheet.Range("A100").Value
        .CenterHeader = ""
        .RightHeader = ws.Name
        .LeftFooter = ActiveWorkbook.Name
        .CenterFooter = ""
        .RightFooter = Date & "  " & Time
    End With
Next ws
End Sub

I'd use this as an example and input your settings as needed. You could reference certain cells (protected, of course!) for your header/footer. See my example above of using cell A100.

 

[zKy]

I'm actually using BeforePrint right now but was wondering whether there's any way to prevent the changes to begin with.

Thanks anyways KMSeattle.

 

[KWMSeattle]

I'm actually using BeforePrint right now but was wondering whether there's any way to prevent the changes to begin with.

Thanks anyways KMSeattle.

Great minds think alike, right?

I'm not sure how to prevent those change then... hopefully somebody else here can help!