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

Johnny Thunder

Well-known Member
Joined
Apr 9, 2010
Messages
679
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

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,300
Office Version
  1. 365
Platform
  1. Windows
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
 

Johnny Thunder

Well-known Member
Joined
Apr 9, 2010
Messages
679
Office Version
  1. 2016
Platform
  1. MacOS
Woohoo! that was it, and honestly I don't think I would have ever spotted it.

Thanks again!
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,300
Office Version
  1. 365
Platform
  1. Windows
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
 

Johnny Thunder

Well-known Member
Joined
Apr 9, 2010
Messages
679
Office Version
  1. 2016
Platform
  1. MacOS
Thanks for the tip. I appreciate the help on this.
 

Forum statistics

Threads
1,136,862
Messages
5,678,197
Members
419,750
Latest member
crp

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
Top