Protecting a header/footer
 

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.

Protecting a header/footer
 

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...

--
Please remember to indicate when the post is answered so others can benefit
from it later.


"Michaelcip" wrote:

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.

Protecting a header/footer
 

Michaelcip wrote:
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

Protecting a header/footer
 

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

"KC" wrote:

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...

--
Please remember to indicate when the post is answered so others can benefit
from it later.


"Michaelcip" wrote:

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.