CODEWITHSUNDEEP
vbaexcel
Blake
Blake

Reputation: 21

Excel Header Macro maxes out at 3 lines

This macro is to set a four line left header for all the worksheets in the workbook. The point of this macro is to have control of each line's font size, and attribute i.e bold. I have a separate sheet called header that has the values I use. It will work for 3 lines, but when I add the fourth it bugs out. I get this error. Run-time error '1004': Unable to set the LeftHeader property of the PageSetup class

Also note I am running Excel 2010 64 bit.

Sub Header()
    '
    ' Header Macro
    '
    lHeader = "&""Calibri,Regular""&10" & Worksheets("Header").Range("B2").Value
    lHeader = lHeader & Chr(13) & "&""Calibri,Regular""&10" & Worksheets("Header").Range("B3").Value
    lHeader = lHeader & Chr(13) & "&""Calibri,Regular""&10" & Worksheets("Header").Range("B4").Value
    lHeader = lHeader & Chr(13) & "&""Calibri,Regular""&10" & Worksheets("Header").Range("B5").Value

    Dim Naam As String, NameFile() As String
    ReDim NameFile(1 To Sheets.Count)

    With Application
        .ScreenUpdating = False
        i = 1
        For Each shtNext In Sheets
            With Sheets(i).PageSetup
                .LeftHeader = lHeader

                .LeftFooter = Format(Now, "mmmm d, yyyy")

                .CenterFooter = "&""Calibri,Regular""&10" & Format(Worksheets("Header").Range("C8").Value)

                .RightFooter = "&""Calibri,Regular""&10" & Format(Worksheets("Header").Range("D8").Value) & "&p of &N"
            End With
            i = i + 1
        Next shtNext
    End With
End Sub

Upvotes: 0

Views: 2384

Answers (2)

Blake
Blake

Reputation: 21

The 255 character limit is for real, even when separating the macros. The font values are a big part of the total. So I removed them all execpt the last line. But If I ever need a report that has different font sizes. I have a little wiggle room to change 2 of the lines without it getting too big. 

LHeader = Worksheets("Header").Range("B2").Value
LHeader = LHeader & Chr(10) & Worksheets("Header").Range("B3").Value
LHeader = LHeader & Chr(10) & Worksheets("Header").Range("B4").Value
LHeader = "&""Calibri,Regular""&10" & LHeader & Chr(10) & Worksheets("Header").Range("B5").Value

CHeader = Worksheets("Header").Range("C2").Value
CHeader = CHeader & Chr(10) & Worksheets("Header").Range("C3").Value
CHeader = CHeader & Chr(10) & Worksheets("Header").Range("C4").Value
CHeader = "&""Calibri,Bold""&14" & CHeader & Chr(10) & Worksheets("Header").Range("B5").Value


RHeader = Worksheets("Header").Range("D2").Value
RHeader = RHeader & Chr(10) & Worksheets("Header").Range("D3").Value
RHeader = RHeader & Chr(10) & Worksheets("Header").Range("D4").Value
RHeader = "&""Calibri,Regular""&10" & RHeader & Chr(10) & Worksheets("Header").Range("D5").Value

Upvotes: 0

Siddharth Rout
Siddharth Rout

Reputation: 149297

You are getting that error because you are exceeding the 255 character limit. Unfortunately I couldn't find an MSDN article to back it up but it can be easily re-produced.

The current lengths that you have is

HeaderL - 121 
HeaderC - 112
HeaderR - 121

Total   - 354

Try this code. Here you can experiment it to reduce the characters.

Sub Header()
    HeaderL = "&""Calibri,Regular""&10" & Worksheets("Header").Range("B2").Value
    HeaderL = HeaderL & Chr(10) & "&""Calibri,Regular""&10" & Worksheets("Header").Range("B3").Value
    HeaderL = HeaderL & Chr(10) & "&""Calibri,Regular""&10" & Worksheets("Header").Range("B4").Value
    HeaderL = HeaderL & Chr(10) & "&""Calibri,Regular""&10" & Worksheets("Header").Range("B5").Value

    HeaderC = "&""Calibri,Bold""&14" & Worksheets("Header").Range("C2").Value
    HeaderC = HeaderC & Chr(10) & "&""Calibri,Bold""&14" & Worksheets("Header").Range("C3").Value
    HeaderC = HeaderC & Chr(10) & "&""Calibri,Bold""&14" & Worksheets("Header").Range("C4").Value
    HeaderC = HeaderC & Chr(10) & "&""Calibri,Regular""&14" & Worksheets("Header").Range("B5").Value

    HeaderR = "&""Calibri,Regular""&10" & Worksheets("Header").Range("D2").Value
    HeaderR = HeaderR & Chr(10) & "&""Calibri,Regular""&10" & Worksheets("Header").Range("D3").Value
    HeaderR = HeaderR & Chr(10) & "&""Calibri,Regular""&10" & Worksheets("Header").Range("D4").Value
    HeaderR = HeaderR & Chr(10) & "&""Calibri,Regular""&10" & Worksheets("Header").Range("D5").Value

    If Len(HeaderL) + Len(HeaderC) + Len(HeaderR) > 255 Then
        MsgBox "Oops, You have exceeded the character limit. Please reduce it and try again"
        Exit Sub
    End If

    Dim ws As WorkSheet

    Application.ScreenUpdating = False

    For Each ws In ThisWorkbook.Sheets
        With ws.PageSetup
            .LeftHeader = HeaderL

            .CenterHeader = HeaderC

            .RightHeader = HeaderR

            .LeftFooter = Format(Now, "mmmm d, yyyy")

            .CenterFooter = "&""Calibri,Regular""&10" & Format(Worksheets("Header").Range("C8").Value)

            .RightFooter = "&""Calibri,Regular""&10" & Format(Worksheets("Header").Range("D8").Value) & "&p of &N"
        End With
    Next ws

    Application.ScreenUpdating = True
End Sub

Upvotes: 1

Related Questions