Forum Discussion

Taiff's avatar
Taiff
Copper Contributor
Aug 19, 2022

Protected Header and Footer (from Page Layout -> Page Setup) in Excel

Hi everyone,

 

Excel has headers and footers via PageLayout -> PageSetup ->Header/Footer. How do I lock and protect from changes using passwords?

 

Thanks in advance!

excel
  • NikolinoDE's avatar
    NikolinoDE
    Gold Contributor
    Aug 19, 2022

    Taiff 

    As far as I know a solution without VBA is not possible, since the user always has access to the toolbars.

    The only thing I can think of is a small macro (VBA) that starts automatically before printing and fills the header and footer with the desired text.

     

    Here is a small example with VBA

     

    Private Sub Workbook_BeforePrint(Cancel As Boolean)
Me.Unprotect("1234")
With ActiveSheet.PageSetup
.LeftHeader = "YourText1"
.CenterHeader = "YourText2"
.RightHeader="&D"'---date here
.LeftFooter = "YourText3"
.CenterFooter = "Page &P of &N pages" '--- Page number here
.RightFooter = "YourText4"
End With
     Me.Protect("1234")
end sub

     

     

    Hope I was able to help you with this info.

     

    NikolinoDE

    I know I don't know anything (Socrates)

    • Taiff's avatar
      Taiff
      Copper Contributor
      Aug 19, 2022

      Thx NikolinoDE.

      The problem I have is that both header and footer have images. Most solutions I've seen are about text-based content. Any idea how to do it reliably with images? 

      • NikolinoDE's avatar
        NikolinoDE
        Gold Contributor
        Aug 20, 2022

        Taiff 

        this should be adjusted and built into the worksheet, not the workbook.

         

        Option Explicit

Private SubWorksheet_Activate()
Me.Unprotect ("1234")
ActiveSheet.PageSetup.LeftHeaderPicture.Filename = "C:\Documents and Settings\Nikolino\My Documents\My Pictures\MySelfFoto1.jpg"
ActiveSheet.PageSetup.RightHeaderPicture.Filename = "C:\Documents and Settings\Nikolino\My Documents\My Pictures\MySelfFoto2.jpg"
With ActiveSheet.PageSetup
.LeftHeader = "&G"
.CenterHeader = "Here the text for center header"
.RightHeader = "&G"
End With
Me.Protect ("1234")
End Sub

         

         

        NikolinoDE

Resources