VBA code for inserting footer in the printout

[beca]

Hi All,

Can someone please help with a VBA code, so that the code can insert information on the LeftFooter, CentreFooter and RightFooter for the active selected worksheets in a workbook?

I didn't want to use "add heater & footer" function in the excel, as people can make changes to it. Therefore, I am thinking of a VBA code for this application to prevent people from modifying footer information.

 

[6StringJazzer]

This code can be used as a template to update your header and footer. However, you cannot prevent a user a user for modifying it.

Private Sub EditHEaderFooter()

    ThisWorkbook.ActiveSheet.PageSetup.RightHeader = "Right Header Text"
    ThisWorkbook.ActiveSheet.PageSetup.RightFooter = "Right Footer Text"
    ThisWorkbook.ActiveSheet.PageSetup.CenterFooter = "Center Footer Text"
    ThisWorkbook.ActiveSheet.PageSetup.CenterHeader = "Center Header text"
    ThisWorkbook.ActiveSheet.PageSetup.LeftHeader = "Left Header Text"
    ThisWorkbook.ActiveSheet.PageSetup.LeftFooter = "Left Footer Text"

End Sub

 

[beca]

This code can be used as a template to update your header and footer. However, you cannot prevent a user a user for modifying it.

Private Sub EditHEaderFooter()

    ThisWorkbook.ActiveSheet.PageSetup.RightHeader = "Right Header Text"
    ThisWorkbook.ActiveSheet.PageSetup.RightFooter = "Right Footer Text"
    ThisWorkbook.ActiveSheet.PageSetup.CenterFooter = "Center Footer Text"
    ThisWorkbook.ActiveSheet.PageSetup.CenterHeader = "Center Header text"
    ThisWorkbook.ActiveSheet.PageSetup.LeftHeader = "Left Header Text"
    ThisWorkbook.ActiveSheet.PageSetup.LeftFooter = "Left Footer Text"

End Sub

Hi Jeff, Thanks for the code.

I copied the above code to "this workbook" in "Visual Basic", There is still no header and footer information in the printout.

Any ideas?

 

[6StringJazzer]

You can't just copy the code. You have to run it. Also I would put it in a standard module (like Module1), not ThisWorkbook, but it should work there.

 

[beca]

You can't just copy the code. You have to run it. Also I would put it in a standard module (like Module1), not ThisWorkbook, but it should work there.

I managed to make it work. I still have few problems below:

(1) After I run the micro, I can go into "header and footer" to delete all the information, and the header and footer information will not appear in the printout when I hit the "print" button. Is there a way to automatically run the code again just before printing to overwrite all the previous input? So it doesn't matter if the header and footer information has been deleted manually, it will always be there.
(2) Would you add some codes so I can specify the header and footer, font, size, boldness and colour?

Thank you

 

[6StringJazzer]

Put this into ThisWorkbook module. It will run after printing is requested but before printing starts.

Private Sub Workbook_BeforePrint(Cancel As Boolean) 

' put code here to set the headers and footers as desired

With Worksheets("My Worksheet").PageSetup
    .RightHeader = "Right Header Text"
    .RightFooter = "Right Footer Text"
    .CenterFooter = "Center Footer Text"
    .CenterHeader = "Center Header text"
    .LeftHeader = "Left Header Text"
    .LeftFooter = "Left Footer Text"
End With

End Sub

I am not familiar with text formatting for headers and footers. Try the macro recorder.

 

[beca]

Put this into ThisWorkbook module. It will run after printing is requested but before printing starts.

Private Sub Workbook_BeforePrint(Cancel As Boolean)

' put code here to set the headers and footers as desired

With Worksheets("My Worksheet").PageSetup
    .RightHeader = "Right Header Text"
    .RightFooter = "Right Footer Text"
    .CenterFooter = "Center Footer Text"
    .CenterHeader = "Center Header text"
    .LeftHeader = "Left Header Text"
    .LeftFooter = "Left Footer Text"
End With

End Sub

I am not familiar with text formatting for headers and footers. Try the macro recorder.

Hi, another question I realized was that if I select multiple worksheets in this workbook, the code only inserts header and footer for the first worksheet, not all the selected worksheets. and I tried your code below "worksheets "xxxxx", it does not work either.

Any idea how the code can insert header and footer for all the selected worksheets?