Cell as header info

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

alan - 23 Aug 2007 13:16 GMT

Any way to use a spreadsheets cell info as a header (or footer) field?

Reply to this Message

Gary''s Student - 23 Aug 2007 13:26 GMT

Put the following macro in the ThisWorkbook code area:

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

Signature

Gary''s Student - gsnu200738

> Any way to use a spreadsheets cell info as a header (or footer) field?

Reply to this Message

alan - 23 Aug 2007 14:12 GMT

sorry, your instructions are beyond my level.

>Put the following macro in the ThisWorkbook code area:
>
>Private Sub Workbook_BeforePrint(Cancel As Boolean)
>v = Range("A1").Value
>ActiveSheet.PageSetup.CenterHeader = v
>End Sub

Reply to this Message

Gary''s Student - 23 Aug 2007 14:30 GMT

You can do it.

At the top of Excel is the Menu Bar containing:

File Edit View Insert ...

Just to the left of File is a small spreadsheet icon

1. right-click the icon and select View Code
2. the VBA window will open up
3. just paste the material in
4. the code works for cell A1. Change it if you like.
5. close the VBA window (save is not necessary)
6. try it ( like print preview)

Signature

Gary''s Student - gsnu200738

> sorry, your instructions are beyond my level.
>
[quoted text clipped - 4 lines]
> >ActiveSheet.PageSetup.CenterHeader = v
> >End Sub

Reply to this Message

Earl Kiosterud - 23 Aug 2007 17:22 GMT

Let me add some steps to be taken after step 2.

2a. You should have a Project Explorer window at the left of the VBE. It looks like an
outline, and will include your project (workbook). If you don't see it, do View - Project
Explorer.
2a. Locate your workbook (project) in the Project Explorer. You should see a module for
each sheet (Sheet1, Sheet 2, etc, and one for This Workbook. You may have to expand (click
the +) the project if it's collapsed.
2c. Double-click ThisWorkbook. This should open a window for ThisWorkbook towards the
right.
2d. Click to put the cursor in that window, at the bottom of anything that might be in it.
Now do the paste. You need to have selected and copied the text of Gary's Student's macro.
After the paste, you'll need to remove any line breaks that might have been put in it in the
post.

Signature

Regards from Virginia Beach,

Earl Kiosterud
www.smokeylake.com

Note: Top-posting has been the norm here.
Some folks prefer bottom-posting.
But if you bottom-post to a reply that's
already top-posted, the thread gets messy.
When in Rome...
-----------------------------------------------------------------------

> You can do it.
>
[quoted text clipped - 19 lines]
>> >ActiveSheet.PageSetup.CenterHeader = v
>> >End Sub

Reply to this Message

alan - 24 Aug 2007 04:16 GMT

Worked great, just as you directed. You're brilliant! Thanks!

Alan

>You can do it.
>
[quoted text clipped - 10 lines]
>5. close the VBA window (save is not necessary)
>6. try it ( like print preview)

Reply to this Message

Matt - 23 Aug 2007 14:50 GMT

> Any way to use a spreadsheets cell info as a header (or footer) field?

It is possible, but I believe it can only be done with a Macro. If
you do not have experience with Macros perhaps is a good time to
start. You don't need to know too much about it as long as you can
follow along. I would suggest trying to record a Macro manually then
looking at the code of what you've created. Then you could possbly
use code like the portion listed below to help you:

ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
.LeftHeader = Sheets("Macro").[A2] & "-" & Sheets("Macro").
[B2]
.CenterHeader = Sheets("Macro").[C2]
.RightHeader = ""
.LeftFooter = "&A"
.CenterFooter = ""
.RightFooter = "&D"
End With

Reply to this Message

Khalil Handal - 23 Aug 2007 15:45 GMT

Hi,
Matt,
I tried to have 4 line in LeftHeader and 4 lines in RighHeader and picture
in the CenterHeader. The Left and Right Headers have different font size and
type.
It didn't work even with VBA code!!!
Is this a LIMIT of Excel 2003???

>> Any way to use a spreadsheets cell info as a header (or footer) field?
>
[quoted text clipped - 15 lines]
> .RightFooter = "&D"
> End With

Reply to this Message

Rate this thread: