Excel VBA Center header/footer "Align Left"

Question

Is there any way to align Center Header in Excel? I know there is no any built in solution but is there any VBA code that would work. I have been trying copying cells to header, setting center header with VBA but my Center Header is "Align Center" all the time.

I have even found very complex code to calculate length of sentences and add spaces to each row but it doesn't really work correctly.

I can also set rows to repeat on top and forget about header but what about footer then? How I can set Center Footer to align my two row text to align left?

I have tried:

With ActiveSheet.PageSetup
    .LeftHeader = Range("a1").Value & " " & Range("b1").Value & " " & Range("a2").Value & " " & Range("b2").Value
End With

Also sending named range to header:

Option Explicit

Sub SetCenterHeader()
    Dim txt As String
    Dim myRow As Range

    With Range("NorthHead") ' reference named range
        For Each myRow In .Rows ' loop through referenced range rows
            txt = txt & Join(Application.Transpose(Application.Transpose(myRow.Value)), " ") & vbLf ' update 'txt' with current row cells values joined and separated by a blank
        Next
    End With
    ActiveSheet.PageSetup.CenterHeader = Left(txt, Len(txt) - 1) ' set CenterHeader with resulting 'txt' excluding last vblf character
    ActiveWindow.SelectedSheets.PrintOut Copies:=1
End Sub

Result is always the same:

May try the workaround method by trying to put center header text in some blank left aligned cells, then `Range.CopyPicture` of the cells and export them as jpg. Finally put the jpg with `PageSetup.CenterHeaderPicture`.May refer to [link1](https://learn.microsoft.com/en-us/office/vba/api/excel.pagesetup.centerheaderpicture) and [link2](https://stackoverflow.com/questions/16143877/using-vba-code-how-to-export-excel-worksheets-as-image-in-excel-2003). — Ahmed AU, Dec 08 '18 at 19:06

Ahmed AU · Accepted Answer · 2018-12-09T03:59:26.060

May try the following workaround and modify to your requirements

Sub test2()
Dim CenHd1 As String, CenHd2 As String, Fname As String
Dim Rng As Range
Dim Sht As Worksheet, MnSht As Worksheet
Dim Cht As ChartObject

Set Sht = ThisWorkbook.Worksheets(3)
Set MnSht = ThisWorkbook.Worksheets(1)
Set Rng = Sht.Range("F1:F2")
CenHd1 = "Excel"
CenHd2 = "I am already left Aligned"
Sht.Range("F1").Value = CenHd1
Sht.Range("F2").Value = CenHd2
Sht.Activate
ActiveWindow.DisplayGridlines = False
    With Rng
    .Columns.AutoFit   'added after taking trial snapshot to perfectly center and left align        
    .HorizontalAlignment = xlLeft
    .Font.Name = "Bookman Old Style"
    .Font.Size = 12
    'May specify other visual effects
    End With
Rng.CopyPicture xlScreen, xlPicture

Set Cht = Sht.ChartObjects.Add(0, 0, Rng.Width * 1.01, Rng.Height * 1.01)
Cht.Name = "TmpChart"
Sht.Shapes("TmpChart").Line.Visible = msoFalse
Cht.Chart.Paste

Fname = "C:\Users\user\Desktop\CentHead " & Format(Now, "dd-mm-yy hh-mm-ss") & ".jpg"
Cht.Chart.Export Filename:=Fname, Filtername:="JPG"
DoEvents
Cht.Delete
ActiveWindow.DisplayGridlines = True

MnSht.Activate
With MnSht.PageSetup.CenterHeaderPicture
        .Filename = Fname
        '.Height = 275.25
        '.Width = 463.5
        '.Brightness = 0.36
        '.ColorType = msoPictureGrayscale
        '.Contrast = 0.39
        '.CropBottom = 0
        '.CropLeft = 0
        '.CropRight = 0
        '.CropTop = 0
    End With

'Enable the image to show up in the center header.
MnSht.PageSetup.CenterHeader = "&G"
'for Trial only
ActiveWindow.View = xlPageLayoutView
' Clear junk files
If Dir(Fname) <> "" Then Kill (Fname)
End Sub

Tried as follows

the code could also be modified as a function / procedure with parameters for repeated use with different Sheets, Workbooks etc. Hope it will help to serve the purpose.

Excel VBA Center header/footer "Align Left"

1 Answers1