Assigning Footers in VBA - What is going on here?

[TheSaint0001]

I am really confused by this???

The following code performs page setup on the active sheet; I am trying to obtain:

Left footer = worksheet tab name (in this example assume this to be "Bruce Jennings")
Centre footer = Page x (where x is the page no)
Right footer = Printed on dd-mm-yyyy

when I run this code as it is (i.e. the Stops remarked out) this is the result:

Left footer is blank (incorrect)
Centre footer is "Page&LBruce Jenni" (incorrect)
Right footer is Printed on 09-01-2014 (correct)

With me so far...

Now then... When I run the code with the Stops included and continue after each break I obtain the desired left, centre and right footers

What is going on here???

Please Login or Register  to view this content.

This is really confusing...

Any help (of the psychiatric kind if not Excel) would be gratefully received...

[GC Excel]

Hi,

Can you use code tags in your code...?
Honestly, not sure why it does work.
Here's a code that I use without any problem.

Please Login or Register  to view this content.

[GC Excel]

And here's a code adjusting the print area as per your code :

Please Login or Register  to view this content.

[TheSaint0001]

Thx, GC Excel, I will try your code although I did start out using tags and got similarly bad results!

What does the Screenupdating switch do?

[TheSaint0001]

Hi GC Excel

I have just tried your code (just substituting the left, centre and right footer lines) and the result??? - Not pretty:

Left footer is "Bruce Jen" (incorrect)
Centre footer is "Printed on" (incorrect)
Right footer is "Page" (incorrect)

It's as if there is not enough room for the footer text to be placed in the margins for each of the left, centre and right footers?

Gulp!

NB - I know what the Screenupdating switch does now - thx...

[GC Excel]

Can you share your file so I can test?

[TheSaint0001]

I could - how do I do that? (I'm new!)

[GC Excel]

Click on the "Go Advanced" button and then you will have the option to attach a file (by clicking on the icon next to the happy face !)

[TheSaint0001]

You are not going to believe this but I ran the revised code (see above) but with Stops in-between each footer line and guess what - it works!

I obviously cannot leave the Stops in the final code; something mysterious is happening to Excel???

Area 51 looms ever nearer!!!

[GC Excel]

Hmmmm...
Can you try this one :

Please Login or Register  to view this content.

[TheSaint0001]

GC Excel - you are a wizard - this works!!

Why it works I have absolutely no idea but the footers are now correct in my worksheet

Just one concern - Does Printercommunication interfere with anything else???

Your patience and help is greatly appreciated!

[GC Excel]

Hi Simon,

The PrintCommunication setting can be used to "accelerate" the code.
When set to false it will be faster but it will NOT work for the header / footer. That is a known bug with Excel.
So what you can do is set PrintCommunication = False to change all setting EXCEPT the header / footer.
Then set it to True to modify the header/footer

This is the compromise between speed and a code that works.

Thanks for the rep and cheers !

[Fotis1991]

Welcome to the forum.

I have added code tags to your post. As per forum rule 3, you need to use them whenever you put any code in your post. Please add them in future. In order to put code tags, either type

Please Login or Register  to view this content.

at the end of it, OR you can highlight your code and click the # icon at the top of your post window.