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

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



--
Gary''s Student - gsnu200738


"alan" wrote:

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

sorry, your instructions are beyond my level.

On Thu, 23 Aug 2007 05:26:16 -0700, Gary''s Student
<(E-Mail Removed)> wrote:

>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

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)
--
Gary''s Student - gsnu200738


"alan" wrote:

> sorry, your instructions are beyond my level.
>
> On Thu, 23 Aug 2007 05:26:16 -0700, Gary''s Student
> <(E-Mail Removed)> wrote:
>
> >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
>

On Aug 23, 8:16 am, alan <oldto...@indirectsolutions.com> wrote:
> 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

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???

"Matt" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> On Aug 23, 8:16 am, alan <oldto...@indirectsolutions.com> wrote:
>> 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
>

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.
--
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...
-----------------------------------------------------------------------
"Gary''s Student" <(E-Mail Removed)> wrote in message
news:93C5963B-04BF-448A-92EC-(E-Mail Removed)...
> 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)
> --
> Gary''s Student - gsnu200738
>
>
> "alan" wrote:
>
>> sorry, your instructions are beyond my level.
>>
>> On Thu, 23 Aug 2007 05:26:16 -0700, Gary''s Student
>> <(E-Mail Removed)> wrote:
>>
>> >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
>>

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

Alan

On Thu, 23 Aug 2007 06:30:16 -0700, Gary''s Student
<(E-Mail Removed)> wrote:

>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)