VBA to Format Header

LikeButtah

Board Regular
Joined
Jun 24, 2011
Messages
168
Please take a look at this code below. I know I'm not coding this correctly because it's not formatting the Left Header the way I would like. I want the left header to display the value of A2 like June 11, 2016 , A2 has a date like 6/11/16. The center header is fine, which is a picture. But when I try to move the left header code around it either doesn't make the font the right size or it doesn't format correctly and gives me a 6/11/16 date. Any help would be appreciated, Thanks.


Code:
Dim ws As Worksheet    

For Each ws In Worksheets
        
        
        ws.PageSetup.LeftHeader = Format(ws.Range("A2").Value, "mmmm d, yyyy")
        
        ActiveSheet.PageSetup.CenterHeaderPicture.Filename = _
        "C:\Users\Box\Pictures\BTW.png"
        ws.PageSetup.CenterHeaderPicture.Filename = _
        "C:\Users\Box\Pictures\BTW.png"
      
         ws.PageSetup.CenterHeader = "&G"
         ws.PageSetup.LeftHeader = "&""Neue,Bold""&1" & ws.Range("A2")
         
         Application.PrintCommunication = False
    With ws.PageSetup
        .PrintTitleRows = "1:1"
        .PrintTitleColumns = ""
    End With
    Application.PrintCommunication = True
    ws.PageSetup.PrintArea = ""
    Application.PrintCommunication = False
    With ws.PageSetup
    
        
        
        .RightHeader = ""
        .LeftFooter = ""
        .CenterFooter = ""
        .RightFooter = ""
        .LeftMargin = Application.InchesToPoints(0.5)
        .RightMargin = Application.InchesToPoints(0.1)
        .TopMargin = Application.InchesToPoints(1.25)
        .BottomMargin = Application.InchesToPoints(0.75)
        .HeaderMargin = Application.InchesToPoints(0.3)
        .FooterMargin = Application.InchesToPoints(0.3)
        .PrintHeadings = False
        .PrintGridlines = False
        .PrintComments = xlPrintNoComments
        .PrintQuality = 600
        .CenterHorizontally = False
        .CenterVertically = False
        .Orientation = xlPortrait
        .Draft = False
        .PaperSize = xlPaperLetter
        .FirstPageNumber = xlAutomatic
        .Order = xlDownThenOver
        .BlackAndWhite = False
        .Zoom = 100
        .PrintErrors = xlPrintErrorsDisplayed
        .OddAndEvenPagesHeaderFooter = False
        .DifferentFirstPageHeaderFooter = False
        .ScaleWithDocHeaderFooter = True
        .AlignMarginsHeaderFooter = True
        .EvenPage.LeftHeader.Text = ""
        .EvenPage.CenterHeader.Text = ""
        .EvenPage.RightHeader.Text = ""
        .EvenPage.LeftFooter.Text = ""
        .EvenPage.CenterFooter.Text = ""
        .EvenPage.RightFooter.Text = ""
        .FirstPage.LeftHeader.Text = ""
        .FirstPage.CenterHeader.Text = ""
        .FirstPage.RightHeader.Text = ""
        .FirstPage.LeftFooter.Text = ""
        .FirstPage.CenterFooter.Text = ""
        .FirstPage.RightFooter.Text = ""
    End With
    Application.PrintCommunication = True
    
   
    
     Next ws
 
Last edited:

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

mrmmickle1

Well-known Member
Joined
May 11, 2012
Messages
2,461
Remove this line of code it's cancelling out the first line of code which inputs the date correctly:
Code:
ws.PageSetup.LeftHeader = "&""Neue,Bold""&1" & ws.Range("A2")
 

LikeButtah

Board Regular
Joined
Jun 24, 2011
Messages
168
Thanks for looking at this. When I remove the below line it does put the date in correctly but its so small, I would like to make the font bigger and maybe bold it. Any suggestions ?
Rich (BB code):
ws.PageSetup.LeftHeader = "&""Neue,Bold""&1" & ws.Range("A2")



 

mrmmickle1

Well-known Member
Joined
May 11, 2012
Messages
2,461
Sure. Try this modification:

Code:
    [COLOR=#333333]ws.PageSetup.LeftHeader [/COLOR] = "&""-,Bold""&16" & Format(Range("A2").Value, "mmmm d, yyyy")
 
Last edited:

Forum statistics

Threads
1,147,568
Messages
5,741,882
Members
423,693
Latest member
Excelquestion35

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