Protecting a header/footer

M

Michaelcip

I intend on password protecting my spreadsheet, when I protected the
header/footer was still vulnerable to modification. How do I prevent such?
Thanks in advance, M.C.
 
K

KC hotmail com>

Your only option is to run a macro that changes the header/footer to what you
want before printing. If you're wanting to avoid macros (which only run if
enabled by the end user), then there's no solution that I'm aware of.

In Excel 2003, right-click the spreadsheet icon up near the top left menus
labeled File, Edit, etc. and select "View Code". This should take you to the
VBA Editor and pull up "ThisWorkbook".

At the top of the editor window are 2 drop downs. The left one says
"(General)" but you want to change that to "Workbook" and then in the right
drop-down select "BeforePrint".

Now you should see this:
Private Sub Workbook_BeforePrint(Cancel As Boolean)

End Sub

In that blank row between "Private" and "End Sub", paste this code (provided
by S-O-S XL Solutions in another Excel help forum back in 2002) starting and
ending with the '*****:

'********************
Application.ScreenUpdating = False
NumOfSheets = Worksheets.Count
For sh = 1 To Worksheets.Count
vis = Sheets(sh).Visible
If vis = 0 Then
Sheets(sh).Visible = True
End If
Sheets(sh).Select
With ActiveSheet.PageSetup
..LeftFooter = "Company Name" & Chr(10) & "Confidential"
..CenterFooter = "&F - &A" & Chr(10) & "&D : &T - Page &P / &N"
..RightFooter = "Reference No" & Chr(10) & "Prepared by s.o.s XL Solutions"
End With
If vis = 0 Then
Sheets(sh).Visible = False
End If
Next sh
Application.ScreenUpdating = True
'********************

Change the left, center, and right footers as desired, and add headers if
desired following the same principles above. At least if someone changes
your footer it will be corrected back before it prints.

You may want to look at protecting and hiding the code so that determined
users can't just overwrite them anyway...
 
B

Bill Sharpe

Michaelcip said:
I intend on password protecting my spreadsheet, when I protected the
header/footer was still vulnerable to modification. How do I prevent such?
Thanks in advance, M.C.

Be aware that password protection in Excel is notoriously weak.

Bill
 
M

Michaelcip

Thanks, I would've responded earlier, but I never got the email to notify of
replies & forgot all about this one. Thanks for your time, MC
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top