I'm trying to set up spreadsheets where the footer changes depending on
certain cells contents. Has anyone been able to do that?

Thanks.

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

Better yet...............

Place the code in a BeforePrint event.

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

Right-click on the Excel Icon left of "File" on the menubar.

Select "View Code"

Copy/paste the BeforePrint code above into that module.


Gord Dibben MS Excel MVP

On Mon, 21 Aug 2006 14:47:32 -0700, djoslyn
wrote:

I'm trying to set up spreadsheets where the footer changes depending on
certain cells contents. Has anyone been able to do that?

Thanks.

Thank you. Any way to link it to a certain sheet called Project Inputs,
versus only the active sheet. Also, what are the chances I can select the
font and sized of the footer?

Thanks again

"Gord Dibben" wrote:

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

Better yet...............

Place the code in a BeforePrint event.

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

Right-click on the Excel Icon left of "File" on the menubar.

Select "View Code"

Copy/paste the BeforePrint code above into that module.


Gord Dibben MS Excel MVP

On Mon, 21 Aug 2006 14:47:32 -0700, djoslyn
wrote:

I'm trying to set up spreadsheets where the footer changes depending on
certain cells contents. Has anyone been able to do that?

Thanks.

Instead of With ActiveSheet

With Sheets("Project Inputs")

Not sure how to change font and size.

Will work on it while waiting for someone to leap in.


Gord

On Tue, 22 Aug 2006 09:01:02 -0700, djoslyn
wrote:

Thank you. Any way to link it to a certain sheet called Project Inputs,
versus only the active sheet. Also, what are the chances I can select the
font and sized of the footer?

Thanks again

"Gord Dibben" wrote:

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

Better yet...............

Place the code in a BeforePrint event.

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

Right-click on the Excel Icon left of "File" on the menubar.

Select "View Code"

Copy/paste the BeforePrint code above into that module.


Gord Dibben MS Excel MVP

On Mon, 21 Aug 2006 14:47:32 -0700, djoslyn
wrote:

I'm trying to set up spreadsheets where the footer changes depending on
certain cells contents. Has anyone been able to do that?

Thanks.

Gord,

I'm trying to do basically the same thing but when I used this version of
the code:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
With Sheets("Quote Form")
.PageSetup.CenterFooter = .Range("D59").Text
End With
End Sub

I get a runtime error: "Unable to set the CenterFooter property of the
PageSetup class"

I'm also interested in the font formatting info but will be soooo happy if I
can just make this part work.

ALso, there are several other sheets in this workbook that I don't want to
print this particular footer on... does using the BeforePrint event here
mean that I need to do that for each worksheet - specify the sheet and the
text in the VBE editor?

Thanks,

Steve E


"Gord Dibben" wrote:

Instead of With ActiveSheet

With Sheets("Project Inputs")

Not sure how to change font and size.

Will work on it while waiting for someone to leap in.


Gord

On Tue, 22 Aug 2006 09:01:02 -0700, djoslyn
wrote:

Thank you. Any way to link it to a certain sheet called Project Inputs,
versus only the active sheet. Also, what are the chances I can select the
font and sized of the footer?

Thanks again

"Gord Dibben" wrote:

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

Better yet...............

Place the code in a BeforePrint event.

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

Right-click on the Excel Icon left of "File" on the menubar.

Select "View Code"

Copy/paste the BeforePrint code above into that module.


Gord Dibben MS Excel MVP

On Mon, 21 Aug 2006 14:47:32 -0700, djoslyn
wrote:

I'm trying to set up spreadsheets where the footer changes depending on
certain cells contents. Has anyone been able to do that?

Thanks.

You code worked for me when placed in a *workbook* module
I changed line 3 to .PageSetup.CenterFooter = .Range("D59")
That is, I removed .TEXT
Any help?
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Steve E" wrote in message
...
Gord,

I'm trying to do basically the same thing but when I used this version of
the code:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
With Sheets("Quote Form")
.PageSetup.CenterFooter = .Range("D59").Text
End With
End Sub

I get a runtime error: "Unable to set the CenterFooter property of the
PageSetup class"

I'm also interested in the font formatting info but will be soooo happy if
I
can just make this part work.

ALso, there are several other sheets in this workbook that I don't want to
print this particular footer on... does using the BeforePrint event here
mean that I need to do that for each worksheet - specify the sheet and the
text in the VBE editor?

Thanks,

Steve E


"Gord Dibben" wrote:

Instead of With ActiveSheet

With Sheets("Project Inputs")

Not sure how to change font and size.

Will work on it while waiting for someone to leap in.


Gord

On Tue, 22 Aug 2006 09:01:02 -0700, djoslyn

wrote:

Thank you. Any way to link it to a certain sheet called Project Inputs,
versus only the active sheet. Also, what are the chances I can select
the
font and sized of the footer?

Thanks again

"Gord Dibben" wrote:

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

Better yet...............

Place the code in a BeforePrint event.

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

Right-click on the Excel Icon left of "File" on the menubar.

Select "View Code"

Copy/paste the BeforePrint code above into that module.


Gord Dibben MS Excel MVP

On Mon, 21 Aug 2006 14:47:32 -0700, djoslyn

wrote:

I'm trying to set up spreadsheets where the footer changes depending
on
certain cells contents. Has anyone been able to do that?

Thanks.

What do you see in D59 of the "Quote Form" worksheet?

And I would be very specific about changing the footers. I'd specify each
worksheet with code very similar to what you have.



Steve E wrote:

Gord,

I'm trying to do basically the same thing but when I used this version of
the code:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
With Sheets("Quote Form")
.PageSetup.CenterFooter = .Range("D59").Text
End With
End Sub

I get a runtime error: "Unable to set the CenterFooter property of the
PageSetup class"

I'm also interested in the font formatting info but will be soooo happy if I
can just make this part work.

ALso, there are several other sheets in this workbook that I don't want to
print this particular footer on... does using the BeforePrint event here
mean that I need to do that for each worksheet - specify the sheet and the
text in the VBE editor?

Thanks,

Steve E

"Gord Dibben" wrote:

Instead of With ActiveSheet

With Sheets("Project Inputs")

Not sure how to change font and size.

Will work on it while waiting for someone to leap in.


Gord

On Tue, 22 Aug 2006 09:01:02 -0700, djoslyn
wrote:

Thank you. Any way to link it to a certain sheet called Project Inputs,
versus only the active sheet. Also, what are the chances I can select the
font and sized of the footer?

Thanks again

"Gord Dibben" wrote:

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

Better yet...............

Place the code in a BeforePrint event.

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

Right-click on the Excel Icon left of "File" on the menubar.

Select "View Code"

Copy/paste the BeforePrint code above into that module.


Gord Dibben MS Excel MVP

On Mon, 21 Aug 2006 14:47:32 -0700, djoslyn
wrote:

I'm trying to set up spreadsheets where the footer changes depending on
certain cells contents. Has anyone been able to do that?

Thanks.





--

Dave Peterson

Try this tiny macro. It sets the center footer to the value in cell A1:

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

This macro should be pasted in workbook code, not a standard module.
--
Gary's Student


"djoslyn" wrote:

I'm trying to set up spreadsheets where the footer changes depending on
certain cells contents. Has anyone been able to do that?

Thanks.