protection of headers and footers

[Guest]

At work, we use excell to make controlled forms. We often put form numbers
and revision levels in the footer. I know how to control cells in a
spreadsheet, but how do I protect the header and footer?

 

[Dave Peterson]

I don't think you can.

About the only thing you can do is use code that resets the header and footer to
exactly what you want--maybe in the workbook_beforeprint event.

And this will fail if the user disables macros or disables events.

 

[JE McGimpsey]

First, XL is not a stable platform for controlled forms unless your
users respect the protection you apply (it's a trivial exercise for
anyone who's able to find these groups, to remove cell protection). So
the first way is to tell your staff to leave the headers and footers
alone. And make sure they're generating the forms from a read-only
template rather than working on the original (of course, they're
probably just copying their last saved workbook).

Short of removing the Header and Footer and Page Setup options from
their menus, one way I've implemented before is to include
Workbook_BeforePrint() and Workbook_BeforeSave event macros that set the
headers (usually after validating the form).

 

[Guest]

thanks.

since there's not an easy fix, i think next time i update the forms, i'll
just put the control info in a cell as opposed to the footer... then lock
the cell.

 

[Gord Dibben]

You can place the footer info in a locked cell and use BeforePrint macro to use
that info in the footer.

Private Sub Workbook_BeforePrint(Cancel As Boolean)
With ActiveSheet
.PageSetup.CenterFooter = .Range("A1").Value
End With
End Sub


Gord Dibben MS Excel MVP