VBA Help - Working with Pagesetup Function - Issues with Setting Header Font Size

Johnny Thunder

Well-known Member
Joined
Apr 9, 2010
Messages
693
Office Version
  1. 2016
Platform
  1. MacOS
Hi gang,

I am working with some code that sets print formats for a summary page and I am encountering the strangest thing.

Issue

Whenever I run the code on files that contain filenames that have numbers in them (example: 300 Racks) the .PageSetup.CenterHeader code that I have gets overwritten and makes the font size 409 instead of the size 24 I am specifying.

Not sure whats going wrong but it is like hit or miss when I run the code on different files.

Here is the code, see anything that could be causing the issue?

Any help is appreciated :)

Code:
Sub FormatPrint2(LastRow, MaxCurr)
'----------------------------------------------------------------------------------------------------------------------
'--- 4. This subroutine defines the print area and formats the printed sheets
'----------------------------------------------------------------------------------------------------------------------
Dim HeaderName As String
Dim HeadText As String
Dim Tall As Integer
Dim Dimension As String
Dim PrintEnd As Integer


PrintEnd = LastRow + MaxCurr + 1
ActiveSheet.PageSetup.PrintArea = "$B$1:$P" & PrintEnd


HeaderName = Range("H11").Value
Tall = ActiveSheet.PageSetup.Pages.Count + 2
Dimension = "{1," & Tall & "}"
Application.PrintCommunication = False


With ActiveSheet
    'Setup for the Print Area and Format of Margins
    .PageSetup.Zoom = False
    .PageSetup.LeftMargin = Application.InchesToPoints(0.75)
    .PageSetup.RightMargin = Application.InchesToPoints(0.75)
    .PageSetup.TopMargin = Application.InchesToPoints(1)
    .PageSetup.BottomMargin = Application.InchesToPoints(1)
    .PageSetup.HeaderMargin = Application.InchesToPoints(0.5)
    .PageSetup.FooterMargin = Application.InchesToPoints(0.5)
    .PageSetup.CenterHeader = "&C&24" & HeaderName '<----------------------------Drops the File Name in Center Header-----------------------"
    .PageSetup.CenterFooter = "&P/&N"
    .PageSetup.RightFooter = "&D &T"
    .PageSetup.FitToPagesWide = 1
    .PageSetup.FitToPagesTall = 5
    .PageSetup.Orientation = xlLandscape 'or xlPortrait
    .PageSetup.PaperSize = xlPaperLetter
    .PageSetup.PrintTitleRows = "$1:$10"
    .PageSetup.LeftFooter = "&""Calibri,Regular""&11Confidential"
    .PageSetup.RightFooter = "&""Calibri,Regular""&11&P/&N"
    .PageSetup.CenterHorizontally = True
    .PageSetup.CenterVertically = False
    .PageSetup.BlackAndWhite = False
    .PageSetup.FirstPageNumber = xlAutomatic
End With


Application.PrintCommunication = True


End Sub
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
The number at the start of the filename is being included in the font size, try putting a space after 24.
Code:
.PageSetup.CenterHeader = "&C&24 " & HeaderName
 
Upvote 0
Woohoo! that was it, and honestly I don't think I would have ever spotted it.

Thanks again!
 
Upvote 0
When you check the headers manually by going to Page Setup it doesn't show you but if you enter this in the immediate window after running your code you will.
Code:
?ActiveSheet.PageSetup.CenterHeader
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,999
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top