Can an Excel cell value be used in a Custom Footer?

[Guest]

Attempting to print some reports from a Pivot Table and should like to use a
cell value as a footer. Is it possible to quote a cell value in the Footers?

 

[Gord Dibben]

Sub CellInFooter()
With ActiveSheet
.PageSetup.CenterFooter = .Range("A1").text
End With
End Sub


Gord Dibben MS Excel MVP

 

[Guest]

Gordon,
Thanks for the advise, having never used code before, I presume I have to go
into
Alt+Shift+F11.
When I seach search for Footer I found the followng line. I have attempted
to change it but the code does not appear to work. Can you advise what I am
doing wrong please?

{mso-footer-data:"&LSub CellInFooter\(\)\000A With ActiveSheet\000A
\PageSetup\CenterFooter = \Range("C5").text\ End Sub&C&D&RPage &P";}
table

Thanks
Richard

 

[Gord Dibben]

This is a macro to be run by hitting a button or shortcut key.

If not familiar with VBA and macros, see David McRitchie's site for more on
"getting started".

http://www.mvps.org/dmcritchie/excel/getstarted.htm

In the meantime..........

First...create a backup copy of your original workbook.

To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

Hit CRTL + R to open Project Explorer.

Find your workbook/project by name and select it.

Right-click and Insert>Module. Paste the code in there. Save the
workbook and hit ALT + Q to return to your workbook.

Run the macro by going to Tool>Macro>Macros.

You can also assign this macro to a button or a shortcut key combo.

An alternative to running the macro manually is to run it whenever you hit the
Print command.

Instead of pasting the code into a General Module, double-click on the
Thisworkbook module when in the VBE

Paste this code into that module.............

Private Sub Workbook_BeforePrint(Cancel As Boolean)
With ActiveSheet
.PageSetup.CenterFooter = .Range("A1").Text
End With
End Sub

Will run whenever you print.


Gord Dibben MS Excel MVP

Gordon,
Thanks for the advise, having never used code before, I presume I have to go
into
Alt+Shift+F11.
When I seach search for Footer I found the followng line. I have attempted
to change it but the code does not appear to work. Can you advise what I am
doing wrong please?

{mso-footer-data:"&LSub CellInFooter\(\)\000A With ActiveSheet\000A
\PageSetup\CenterFooter = \Range("C5").text\ End Sub&C&D&RPage &P";}
table

Thanks
Richard

Gord Dibben MS Excel MVP

 

[Guest]

Hi Gord!

Found your reply in the Newsgroup. Great reply! It works great on my doc on
my machine, the problem is it won't work for anyone else without tweaking.
I saved the VB code told my user to test it...it failed. I have the the
Private Sub Workbook_BeforePrint and the Sub CellInFooter() in This Workbook.
Even changed the Sub to a Private Sub. Still no luck.

The worksheet has a running total at cell W178. I can get that to appear.
But when he opens the file on his machine, it will not update the footer. I
checked the security and had it set to Medium. Nada.

Any clues? Thanks for any ideas!

 

[Gord Dibben]

Couple of points...........

1. The Sub CellInFooter() goes into a standard or general module, not
Thisworkbook module and is run by a button or shortcut key combo or
Tools>Macro>Macros.

2. The BeforePrint code is event code and goes into the Thisworkbook module and
will fire when you hit the "Print" button.

Is the other user enabling macros when opening the workbook?

Does the workbook open for user but can't use the macro or event code won't
fire?


Gord

 

[Guest]

I have made the changes (I am obviously new to the VB arena) and will test on
his machine when possible.

His security has been reset to Medium.
I am not sure where to look to make sure he has not disengaged his macros
when the workbook opens.

 

[Gord Dibben]

With his Macro Security settings at Medium he will get the "this workbook
contains macros" message and the option to "enable" or "disable" macros.

Instruct him to "enable" macros.


Gord