VBA Footer Problem

Question

When i try to set the right and left footer using the same method the code fails.

Sub FootL()

Const FontSize = 12

ActiveSheet.PageSetup.LeftFooter = "&" & FontSize & Range("UKaddress1").Value

ActiveSheet.PageSetup.RightFooter = "&" & FontSize & Range("UKaddress2").Value

End Sub

error code: unable to set the rightfooter property of the pagesetup class

The left footer populates correctly but fails on the right footer

Any help much appreciated

Answer 1

You can't - 255 characters is a hard limit. As an alternative, you could place the addresses at the top of the sheet and repeat the top row(s).

Answer 2

I believe the two addresses have a character count greater than 255 which is causing the issue. Do you know how i can increase the character count?

Answer 3

I created a small sample workbook and named two cells UKaddress1 and UKaddress2.

I tested the code with different values in UKaddress1 and UKaddress2. It ran as intended without error...

Answer 4

UKaddress2 is just a defined cell that contains a normal address. It is the same format as UKaddress1, the code will work if I run them individual but fails when i put them in the same code.

Answer 5

There might be something in the value of UKaddress2 that confuses Excel, for example because it looks like a format code for the footer.

What is the value of UKaddress2?