dcsimg
Results 1 to 7 of 7

Thread: Excel VBA Center header/footer “Align Left”

  1. #1

    Thread Starter
    New Member
    Join Date
    Oct 2018
    Posts
    15

    Excel VBA Center header/footer “Align Left”

    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:

    Code:
    With ActiveSheet.PageSetup
        .LeftHeader = Range("a1").Value & " " & Range("b1").Value & " " & Range("a2").Value & " " & Range("b2").Value
    End With
    Also sending named range to header:

    Code:
    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:

    Name:  Capture.jpg
Views: 70
Size:  18.0 KB

  2. #2
    PowerPoster
    Join Date
    Dec 2004
    Posts
    23,882

    Re: Excel VBA Center header/footer “Align Left”

    there are formatting characters available for the headers and footers
    &L for left align

    you should be able to adapt
    Code:
     sht.PageSetup.CenterHeader = "&LThis is my header"
    from msdn
    Format code Description
    &L Left aligns the characters that follow.
    &C Centers the characters that follow.
    &R Right aligns the characters that follow.
    &E Turns double-underline printing on or off.
    &X Turns superscript printing on or off.
    &Y Turns subscript printing on or off.
    &B Turns bold printing on or off.
    &I Turns italic printing on or off.
    &U Turns underline printing on or off.
    &S Turns strikethrough printing on or off.
    &D Prints the current date.
    &T Prints the current time.
    &F Prints the name of the document.
    &A Prints the name of the workbook tab.
    &P Prints the page number.
    &P+number Prints the page number plus the specified number.
    &P-number Prints the page number minus the specified number.
    && Prints a single ampersand.
    & "fontname" Prints the characters that follow in the specified font. Be sure to include the double quotation marks.
    &nn Prints the characters that follow in the specified font size. Use a two-digit number to specify a size in points.
    &N Prints the total number of pages in the document.
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  3. #3

    Thread Starter
    New Member
    Join Date
    Oct 2018
    Posts
    15

    Re: Excel VBA Center header/footer “Align Left”

    Thanks again for your effort. I saw this one already but it looks like &L is sending text to the left header. I was thinking maybe my text is on the left as it is on top of the "Left header" and by spacing I can send it to needed position so the second row will have the same amount of spaces and they will be finally in the "middle aligned to the left" but spaces does not seem to work.

    Code:
    Sub centerheaderdfgh()
    With ActiveSheet.PageSetup
        .CenterHeader = "             " & "&LThis is my header"
    End With
    End Sub
    
    .PageSetup.CenterHeader = Chr(13) & "&""Times New Roman,Bold""&11 " & Range("'Translations Pricelist'!S4").Text & Chr(13) & Chr(13) & "&""Times New Roman,Normal""&11 " & Range("MAIN!D15").Text
    .PageSetup.RightHeader = "&""Times New Roman,Normal""&11 " & "&P (&N)" & Chr(13) & Chr(13) & Chr(13) & "&""Times New Roman,Normal""&11 " & Range("MAIN!D14").Text
    Name:  Capturefgdfg.jpg
Views: 9
Size:  21.4 KB
    Last edited by mrwad; Dec 9th, 2018 at 06:45 AM.

  4. #4
    PowerPoster
    Join Date
    Dec 2004
    Posts
    23,882

    Re: Excel VBA Center header/footer “Align Left”

    i would think that you could put your center header in the left header with some spaces or tabs between to align

    on testing, if you do
    .CenterHeader = " " & "&LThis is my header"
    you effectively position the text in the left most position which would overwrite the spaces or any text already in the left header

    you can try
    Code:
    .LeftHeader = "this is some string" & Space(30) & "this is my header"
    you can vary the number of spaces to align the 2nd part to your desired position, this would be much more fiddley with multiple line headers or word wrap on the header
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  5. #5

    Thread Starter
    New Member
    Join Date
    Oct 2018
    Posts
    15

    Re: Excel VBA Center header/footer “Align Left”

    This would be easy solution if I would have the same text in my header all the time. The problem is that I use =VLOOKUP function to look for translations of text on another Sheet. I have drop down to select language of my output document English/German/Spanish etc. =VLOOKUP function searches for correct text to insert to certain cell. From that cell according to chosen by user language I input this text to header. If user selects English text will be for example "car" (3 letters) and if it is German then it will be "wagen" (5 letters). So there is different amount of letters in each translations = different amount of spaces. It is hard to input amount of spaces from the left for each translation.

    'Translations Pricelist'!S4 is where =VLOOKUP function located. Amount of letters depends on language user has selected for output format.

    On the left I have company logo that is sat up through "Page Layout" view "Header and Footer Elements"

    Here is my actual code I am using for VBA headers:

    Code:
    Sub centerheaderdfgh()
    
    ThisWorkbook.Worksheets("sheet1").PageSetup.CenterHeader = Chr(13) & "&""Times New Roman,Bold""&11 " & Range("'Translations Pricelist'!S4").Text & Chr(13) & Chr(13) & "&""Times New Roman,Normal""&11 " & Range("MAIN!D15").Text
    ThisWorkbook.Worksheets("sheet1").PageSetup.RightHeader = "&""Times New Roman,Normal""&11 " & "&P (&N)" & Chr(13) & Chr(13) & Chr(13) & "&""Times New Roman,Normal""&11 " & Range("MAIN!D14").Text
    End Sub

  6. #6
    PowerPoster
    Join Date
    Dec 2004
    Posts
    23,882

    Re: Excel VBA Center header/footer “Align Left”

    some time ago i wrote an activex to use in vba that worked with the textwidth of any string, the usage of it in this instance would not be trivial, but it should allow you to determine the length of any part of the string, based on the characteristics of the font, it would also (if required) be able to break a string into substrings of a maximum wiidth, to force your own word wrap, it is an unfinished project, but i can have a look at it, if you are interested

    as you appear to be always using the same font, it is probably easier just to work with the count of characters even though that would be less accurate

    as far as i can tell, using alignment for the header text is the same as specifying which header to use
    that is right header aligned left is the same as left header

    a possible alternative is to insert textboxes on each sheet at top and bottom as desired
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  7. #7

    Thread Starter
    New Member
    Join Date
    Oct 2018
    Posts
    15

    Re: Excel VBA Center header/footer “Align Left”

    For me it doesn't really matter what type to use as it would just do what I need.

    - I have tried with image. Code is taking a screenshot of cells and inserts an image into center header but then you have lower quality for that part as image will be an image when you convert file to pdf and you will see lower quality of text in image part.
    - I know that the last solution (if nothing else better) would be to set Print Titles in Page Setup. Then I can simply insert =VLOOKUP function in cells at the top. So basically I will just set up my header on top of each page, logo on the left, text in the center. The problem here is page numbers as I don't know how to set up page numbers inside the "Print Titles". If I input page numbers into upper right header the layout will be not so good looking.
    - As an alternative I was also thinking about transporting Excel pages to Word and setting all the styles there by VBA but it sounds quite like XY solution when I am avoiding some problem instead of trying to solve it. Also that's quite a lot of additional code.

    Textboxes idea sounds good but I am not sure that you can insert textboxes to header area?

    You are right I am using the same Font in all my documents also font size for header is the same all the time.

    Here is the code I found in internet for counting spaces but it does not work correctly:

    Code:
    Sub AddRightHeader()
     
    Dim HeaderArray()
    Dim HeaderFont As String, HeaderText As String
     
    ReDim HeaderArray(0 To 5, 0 To 1)
     
    HeaderArray(0, 0) = "Alexander Farzakarak"
    HeaderArray(1, 0) = "Some Company"
    HeaderArray(2, 0) = "Chief Man"
    HeaderArray(3, 0) = "800.234.5678"
    HeaderArray(4, 0) = "PO Box 123, Jackson Mt, PN  45678"
     
    HeaderFont = "Arial"
     
    HeaderArray = GetLeftAlignSpacing(HeaderArray, HeaderFont)
     
    HeaderText = ""
    For i = 0 To 4
      HeaderText = HeaderText & HeaderArray(i, 0) & WorksheetFunction.Rept(" ", HeaderArray(i, 1))
        If Application.Version >= 12 Then
          HeaderText = HeaderText & "." ' "&K00+000.&K01+000"
        Else
          HeaderText = HeaderText & "."
        End If
      If i < UBound(HeaderArray) Then HeaderText = HeaderText & vbCr
    Next i
     
    ' HeaderText = "Jack    " & "&K00+000.&K01+000" & vbCr & "Jane        " & "&K00+000.&K01+000"  ' This works in Excel 2007
     
    For i = 0 To 4
    With ActiveSheet.PageSetup
      .RightHeader = HeaderText
    End With
    Next i
     
    'ActiveSheet.PrintPreview enablechanges:=False
     
    End Sub
     
     
     
    Function GetLeftAlignSpacing(HeaderArray, HeaderFont As String)
     
    Dim TempSheet As Worksheet
    Dim MaxBoxWidth As Single, LineItem As String, SpacesToRight As Integer
     
    Application.ScreenUpdating = False
     
    Sheets.Add
    Set TempSheet = ActiveSheet
    TempSheet.Range("A1").Font.Name = HeaderFont
     
    MaxBoxWidth = 0
    For i = 0 To 4
      TempSheet.Range("A1").Value = HeaderArray(i, 0)
      TempSheet.Columns(1).AutoFit
      If TempSheet.Columns(1).ColumnWidth > MaxBoxWidth Then MaxBoxWidth = TempSheet.Columns(1).ColumnWidth
    Next i
           
    For i = 0 To 4
    SpacesToRight = 0: LineItem = ""
      Do While Columns(1).ColumnWidth <= MaxBoxWidth
        SpacesToRight = SpacesToRight + 1
          LineItem = HeaderArray(i, 0) & WorksheetFunction.Rept(" ", SpacesToRight)
          Range("A1").Value = LineItem
          TempSheet.Columns(1).AutoFit
      Loop
    HeaderArray(i, 1) = SpacesToRight - 1
    Range("A1").Value = ".": TempSheet.Columns(1).AutoFit
    Next i
     
    Application.DisplayAlerts = False
      TempSheet.Delete
    Application.DisplayAlerts = True
     
    GetLeftAlignSpacing = HeaderArray
     
    Application.ScreenUpdating = True
     
    End Function
    Here is the code with image but quality will not be great and you can notice the difference after converting to pdf:

    Code:
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width