Format to Headers in excel using VBA

[bto.zapata]

Hi there!

i have been trying to give special format to my workbook so that i can have a different header in each page of my excel so it can be ready to print. I've done my research and ended up by deciding to use VBA to do this. this is my code:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim ws As Worksheet
        For Each ws In ActiveWorkbook.Worksheets
                 ws.PageSetup.LeftHeader = ws.Range("A2").Value
         Next ws
End Sub

this works great... but the value in "A2" allways appears in arial 12 with black colour... ¡even if i change it manualy previously!

Does anyone knows hoy to change the font type and font colour of a header using vba?

Thanks!

 

[Momentman]

I am not sure I get your request.

Is it the header's font you wan to change or range("A2")'s font.

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim ws As Worksheet
        For Each ws In ActiveWorkbook.Worksheets
                   With ws.Range("A2")
                      .Font.Color = 5
                      .Font.Name = "Calibri"
                      .Font.Size = 12
                  End With
       
                ws.PageSetup.LeftHeader = ws.Range("A2").Value
      Next ws
End Sub

 

[shg]

Another way.

In ThisWorkbook,

Private Sub Workbook_BeforePrint(Cancel As Boolean)
    Dim wks         As Worksheet

    For Each wks In ActiveWorkbook.Worksheets
        wks.PageSetup.LeftHeader = HdrFtr(ws.Range("A2").Value, "Calibri,Regular", vbBlue, 36)
    Next wks
End Sub

In a standard module,

Function HdrFtr(sText As String, _
                Optional ByVal sFont As String, _
                Optional iColor As Long, _
                Optional iFontSize As Long) As String
    ' shg 2012

    Dim sColor      As String
    Dim sFontSize   As String

    If Len(sFont) Then sFont = "&""" & sFont & """"

    If Abs(iFontSize) Then sFontSize = "&" & Abs(iFontSize)

    If iColor <> 0 Then
        sColor = "&K" & _
        Right("0" & Hex(iColor And &HFF), 2) & _
                 Right("0" & Hex(iColor \ &H100 And &HFF), 2) & _
                 Right("0" & Hex(iColor \ &H10000 And &HFF), 2)
    End If

    HdrFtr = sFont & sFontSize & sColor & sText
End Function

 

[bto.zapata]

Thanks for that! i'm almost there!

Creating a function in a module and add it to "This Workbook" was a great idea!

my intention was to give format to the headers, not to the cell range so tha was very helpful. now,

I'm sorry if i'm beeing a little anoying but... is there a way to give the HEADER a "fill colour" as you normaly do to a cell? It's becouse i'm trying to give it like a banner style but i need to have:

1)Left Header: different text on each page but to keep a brown background
2)Center Header: just the brown background
3)Right Header: my employees logo (allways)

I tried using an image that will cross all the worksheet if i place it in the right header my image covers the text in the left header so it won't be seen or printed.

¿Any ideas?

 

[shg]

is there a way to give the HEADER a "fill colour" as you normaly do to a cell?

Maybe using a picture; never tried.

 

[bto.zapata]

Never mind! problem solved... when tha macro runs again, the left header is applied and the image goes to the background leaving my "customable" header in front available to preview/print. thanks for the help thou

 

[shg]

Thou are welcome, good luck.