2

When I run the VBA code to set-up and format the footer for the Excel page, I get 1004 Runtime error - "Unable to set the CenterFooter property of the PageSetup class". The code is below:

With Sheets("Letter").PageSetup
        .LeftFooter = "&""Arial,Regular""&6" & Sheets("Footer").Range("A1")
        .CenterFooter = "&""Arial,Regular""&6" & Sheets("Footer").Range("E2")
        .LeftHeader = "&""Arial,Regular""&8" & Sheets("Footer").Range("I5")
End With

The code refers to the specific cells in the "Footer" named spreadsheet to pick-up the wording for left and center footers, as well as for the left header of the page.

When I run the code it picks up the left footer, but the error window pops-up on the .CenterFooter line. If I comment that line, LeftFooter and LeftHeader work perfectly.

I also noted that, if I simplify the code taking out the formatting piece like below, it works perfectly:

With Sheets("Letter").PageSetup
        .LeftFooter = Sheets("Footer").Range("A1")
        .CenterFooter = Sheets("Footer").Range("E2")
        .LeftHeader = Sheets("Footer").Range("I5")
End With

I'm relatively new in VBA coding, hence appreciate if your answer would be easy to understand.

Nimantha
  • 6,405
  • 6
  • 28
  • 69
J.Hun
  • 21
  • 4
  • 1
    When stepping through your code in the VBE (hitting `F8` for each line to execute the code line by line) is the `.LeftFooter = Sheets("Footer").Range("A1")` property set OK? – Samuel Everson Apr 25 '20 at 08:29
  • Hi @SamuelEverson, I think yes, because when I run that block with '.LeftFooter = Sheets("Footer").Range("A1")', it works good. The issue appears when I try to run the block with the code '.LeftFooter = "&""Arial,Regular""&6" & Sheets("Footer").Range("A1")' – J.Hun Apr 25 '20 at 08:46
  • 1
    Interesting, I tested on a new workbook with dummy values in the range values and it worked ok. Is there anything special about the value in `...Range("E2")? – Samuel Everson Apr 25 '20 at 08:58
  • 1
    No, nothing special in `...Range("E2")` , just a text same as in `...Range("A1")`. Did you test the first block with `.LeftFooter = "&""Arial,Regular""&6" & Sheets("Footer").Range("A1")` ? – J.Hun Apr 25 '20 at 09:07
  • 1
    Yes I was able to successfully execute the full code block. The 'dummy' data I entered to each cell was it's own cell reference - e.g. in cell `A1` I typed in `A1` etc. All values were entered to the relevant footer/header section with the font and sizing specified. – Samuel Everson Apr 25 '20 at 13:25
  • 1
    Might be worth adding a specific reference to the `Range("A1")` **`.Value`** etc. too. – Samuel Everson Apr 25 '20 at 13:26
  • I tried with `Range("A1:).Value`, no any difference... I was able to run each line separately, but when I try to run them together, the error message appears – J.Hun Apr 25 '20 at 15:00
  • 1
    @SamuelEverson, I understood what the issue was, and why you could run it, but I couldn't. The length of text in two of my cells is 245 characters, and when I add additional characters, like `"&""Arial, Regular""&6"` it exceeds the 256 character limit for footer. I assume your text for footer was much shorter. When I reduced a length of the text, it worked fine too. Now I need to find a way how to create a footer with longer text... might be as a picture? – J.Hun Apr 25 '20 at 20:43
  • 1
    Ahh I see! Good pick up. It's worth updating your question with this. – Samuel Everson Apr 25 '20 at 21:44
  • From what I can find it seems to be inserting a picture is probably the easiest work around to the character limit. Be sure to post an answer to your question if you find a better way! – Samuel Everson Apr 26 '20 at 08:39

0 Answers0