Protect Footer

[poleary2000]

Does anyone know how to protect a sheets footer? I have the sheet protected but the user can still change the footer.

Any suggestions?

 

[brettvba]

I would say u would need code to disable page setup if thats what your after

 

[poleary2000]

I do not want to disable page setup completely, just the footer. Thanks for the suggestions.

Anyone know how to do that?

 

[Jay Petrulis]

I don't know how to disable the page setup, but you might want to write your footer in VBA and run it thrrough the workbook_before_print event.

Something like

Private Sub Workbook_BeforePrint(Cancel As Boolean)

YourSheet.rightfooter = "blah, blah, blah"

End Sub

Jay

 

[s-o-s]

I'm not sure about protecting the footer...

You could write some code that resets your footer back to your defaults before a print... This code has to be entered into the This Workbook Module, Using the Workbook Object (Not General...Click the left hand drop down)
Then click the right hand drop down and select Beforeprint. The 1st line of code will appear automatically.

Private Sub Workbook_BeforePrint(Cancel As Boolean)
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
End Sub

Change the lines between the Width and End Width statments to Your own Info that you want to see.

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 cant just overwrite them anyway...


I'm about to log off now but I hope this helps...maybe if only as a stop gap.

 

[poleary2000]

Hmmmm - sounds like a good way to do it. Thanks for the tip.

My file is very VBA intensive already and I am trying to conserve file space. Does anyone know a way to do this without VBA?