How to add a borderline above each Excel Footer?

[sagain2k]

I can create Excel Footers no problem, and know how to use footer code to force a particular font or font size, and also a macro that can format the footer date to a particular format, like this:

Sub ChangeFooterDateFormat()
ActiveSheet.PageSetup.LeftFooter = Format(Now, "DDDD, MMM DD, YYYY")
End Sub

Is there a way I can format the footer so it always has a borderline just above it on each page? Or is there a macro (similar to above) that can do this? Thanks for any suggestions!

 

[Andrew Poulsom]

Experiment with the number in the second argument of the Rept function until you get what you want:

Sub ChangeFooterDateFormat()
    Dim strLine As String
    strLine = "&U" & WorksheetFunction.Rept(" ", 200) & "&U"
    ActiveSheet.PageSetup.LeftFooter = strLine & Format(Now, "DDDD, MMM DD, YYYY")
End Sub

 

[sagain2k]

I tried this but got the error message "Unable to set the LeftFooter Property of the PageSetup Class" when it tried to do the line:

ActiveSheet.PageSetup.LeftFooter = strLine & Format(Now, "DDDD, MMM DD, YYYY")

Also: can you tell me what VBA code I can use as well to format the footer text as Times New Roman 8p? Thanks!

 

[Andrew Poulsom]

I tried this but got the error message "Unable to set the LeftFooter Property of the PageSetup Class" when it tried to do the line:

ActiveSheet.PageSetup.LeftFooter = strLine & Format(Now, "DDDD, MMM DD, YYYY")

Also: can you tell me what VBA code I can use as well to format the footer text as Times New Roman 8p? Thanks!

I got that error with a repeat of 250, but not with 200. Try reducing it until it is error free.

Recording a macro while setting the footer to Times New Roman 8p manually will give you the code.