Excel VBA Center header/footer “Align Left”

[mrwad]

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:

I have also got a solution to use image capture and input it to the header but then you will notice quality difference of the text on your printed our document.
https://stackoverflow.com/questions/53682601/excel-vba-center-header-footer-align-left?noredirect=1

 

[charlesdh]

Hi,
I think it would help if you post a workbook for us to review.

Charles

 

[Hui]

You don't get much control over the alignment of each section as they are predefined

You can use Format Codes in each Section, but they don't stay in the section
ie: applying a format code of &L to text in the centre section left aligns the text after that in the left section

You may wish to have a read of:
https://excel.tips.net/T002057_Header_and_Footer_Formatting_Codes.html

You might be able to use 2 or 3 rows of cells as a Header
eg: Rows 1&2 will be a header on all pages