A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » General Discussion
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Protecting a header/footer



 
 
Thread Tools Display Modes
  #1  
Old January 26th, 2010, 06:45 PM posted to microsoft.public.excel.misc
Michaelcip
external usenet poster
 
Posts: 21
Default 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.
  #2  
Old January 26th, 2010, 08:04 PM posted to microsoft.public.excel.misc
KC hotmail com>
external usenet poster
 
Posts: 57
Default 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.

  #3  
Old January 27th, 2010, 12:32 AM posted to microsoft.public.excel.misc
Bill Sharpe
external usenet poster
 
Posts: 360
Default 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
  #4  
Old March 26th, 2010, 11:44 PM posted to microsoft.public.excel.misc
Michaelcip
external usenet poster
 
Posts: 21
Default 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.

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 03:04 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.