none
Lock Excel 2010 Headers and Footers in Protected Worksheet

    Question

  • Hi,

    Is there any way to lock Excel 2010 Headers and Footers in a Protected Worksheet?

    Thank You!

    Friday, June 24, 2011 12:52 AM

Answers

  • Hi,
    Here is one way to 'lock' the header and footer control, by disabling it in the WorkBook_Open event.

    You indicate that the workbook is 'macro enabled', so if you are well versed in VBA please forgive the following step by step 'how to'.

    Copy the following code to the clipboard:

    Private Sub Workbook_Open()
    Application.CommandBars("Worksheet menu bar"). _
    Controls("View").Controls("&Header and Footer...").Enabled = False
    End Sub

    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Application.CommandBars("Worksheet menu bar"). _
    Controls("View").Controls("&Header and Footer...").Enabled = True
    End Sub

    Press ALT + F11

    Double click 'THIS WORKBOOK' in the Microsoft Excel Objects in the upper left quadrant.

    Paste both event handlers into the WorkBook module editing area to the right.

    Close the VBE and return to the worksheet.

    Save the workbook.

    Now, whenever the workbook is opened, the Header and Footer option under the View tab will be disabled (grayed out). When the workbook is closed, the control will be enabled again so it will be accessible in all subsequently opened workbooks.

    If you wish to have access to the header and footer for 'maintenance', copy the following macro to the clipboard:

    Sub HeadnFoot()
    If Application.CommandBars("Worksheet menu bar"). _
    Controls("View").Controls("&Header and Footer...").Enabled = False Then
    Application.CommandBars("Worksheet menu bar"). _
    Controls("View").Controls("&Header and Footer...").Enabled = True
    Else
    Application.CommandBars("Worksheet menu bar"). _
    Controls("View").Controls("&Header and Footer...").Enabled = False
    End If
    End Sub

    ALT + F11 to access the VBE.

    INSERT > MODULE

    Paste the macro into the module editing area to the right.

    Close the VBE.

    Press ALT + F8

    When the Macros window opens, highlight this macro and click 'Options..'.

    Enter a letter to be used as a keyboard shortcut and click 'OK'.

    Close the Macros window.

    Save the workbook.

    Now, when you open the workbook and need to modify the header or footer, press CTRL + your shortcut letter and the control will be enabled. Press the keyboard shortcut again and the control will again be disabled. It is a 'toggle' macro.
    Sincerely,
    Harry
    • Marked as answer by Harry Yuan Thursday, June 30, 2011 1:33 AM
    Tuesday, June 28, 2011 7:01 AM

All replies

  • Hi,
    Here is one way to 'lock' the header and footer control, by disabling it in the WorkBook_Open event.

    You indicate that the workbook is 'macro enabled', so if you are well versed in VBA please forgive the following step by step 'how to'.

    Copy the following code to the clipboard:

    Private Sub Workbook_Open()
    Application.CommandBars("Worksheet menu bar"). _
    Controls("View").Controls("&Header and Footer...").Enabled = False
    End Sub

    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Application.CommandBars("Worksheet menu bar"). _
    Controls("View").Controls("&Header and Footer...").Enabled = True
    End Sub

    Press ALT + F11

    Double click 'THIS WORKBOOK' in the Microsoft Excel Objects in the upper left quadrant.

    Paste both event handlers into the WorkBook module editing area to the right.

    Close the VBE and return to the worksheet.

    Save the workbook.

    Now, whenever the workbook is opened, the Header and Footer option under the View tab will be disabled (grayed out). When the workbook is closed, the control will be enabled again so it will be accessible in all subsequently opened workbooks.

    If you wish to have access to the header and footer for 'maintenance', copy the following macro to the clipboard:

    Sub HeadnFoot()
    If Application.CommandBars("Worksheet menu bar"). _
    Controls("View").Controls("&Header and Footer...").Enabled = False Then
    Application.CommandBars("Worksheet menu bar"). _
    Controls("View").Controls("&Header and Footer...").Enabled = True
    Else
    Application.CommandBars("Worksheet menu bar"). _
    Controls("View").Controls("&Header and Footer...").Enabled = False
    End If
    End Sub

    ALT + F11 to access the VBE.

    INSERT > MODULE

    Paste the macro into the module editing area to the right.

    Close the VBE.

    Press ALT + F8

    When the Macros window opens, highlight this macro and click 'Options..'.

    Enter a letter to be used as a keyboard shortcut and click 'OK'.

    Close the Macros window.

    Save the workbook.

    Now, when you open the workbook and need to modify the header or footer, press CTRL + your shortcut letter and the control will be enabled. Press the keyboard shortcut again and the control will again be disabled. It is a 'toggle' macro.
    Sincerely,
    Harry
    • Marked as answer by Harry Yuan Thursday, June 30, 2011 1:33 AM
    Tuesday, June 28, 2011 7:01 AM
  • This doesn't work for me in Excel 2010. Isn't there some method that doesn't require VBA?
    Friday, February 17, 2012 7:14 PM
  • This does not work really as well for me in Office 2010.... any other solution?  I just want to have a printable and non editable background picture or a picture in header of excel which I can share with others without washing out my identity in that excel sheet contents. Can anybody help ?
    • Edited by PanShukla Tuesday, February 04, 2014 4:38 AM
    Tuesday, February 04, 2014 4:37 AM