Header Headache!

C

CWit

Frank did a great job helping with with the code to link a cell into th
header

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim wkSht As Worksheet
For Each wkSht In Me.Worksheets
With wkSht.PageSetup
.CenterFooter = wkSht.Range("A1").value
End With
Next wkSht
End Sub

I'm right clicking on the sheet tab and pasting this code under "vie
code". When I go back to the excel sheet I put the text I want in Cel
A1. Then go to print preview and nothing, I printed and nothing. So
Thought there might be something wrong. So I searched the forums fo
the same topic and read 5 post where this same code worked for them.

Is there a step I'm missing, I'm going crazy!
I'm using Excel 2002

Thanks in advance for the asprin!

Oh and I tried opening a plain NEW excel sheet putting "JohnDoe" i
cell A1 then pasting this code in the editor, still nothing
 
F

Frank Kabel

Hi
this code goes in the workbook module. Try the following
- open your workbook
- open the VBA editor (ALT+F11)
- doubleclick on 'ThisWorkbook' in the left explorer window of the VBA
editor
- paste the code
- close the VBA editor and save the workbook

No goto print preview and have a look if it works
Note: This will link cell A1 for all sheets of your workbook!
 
H

Harald Staff

Aspirin:
That code belongs not in a sheet module, but in the ThisWorkbook module.
 
T

Tom Ogilvy

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim wkSht As Worksheet
For Each wkSht In Me.Worksheets
With wkSht.PageSetup
.CenterFooter = wkSht.Range("A1").value
End With
Next wkSht
End Sub

You should have a period before CenterFooter
 
C

CWit

Well, I open a brand new excel sheet and hit alt-F11 and it open the VB
editor. I see File Edit View etc. And the rest is blank. Now if I got
"View" - then "Code" It opens a white box on the left there is a dro
down menu with 2 selections "General" and "Worksheet". No where on th
VBA editor do I see ThisWorkBook. I even searched for ThisWorkBook i
help and all it said was ThisWorkBook properties but did not show ho
to get there.

Srry, I can't find it
 
F

Frank Kabel

Hi
in the left window should be a eplorer tree view with your workbook
file as one project. Expand this project and the first entry below
should be 'ThisWorkbook'

another option:
- open your workbook
- right-click on the Excel symbol directly left to the menu 'File'
- choose 'Code'
- now you're in the workbook module
 
C

CWit

if you look at the code i posted it has period before CenterFooter...

I just can't seem to find the "Thisworkbook"

I'm such a N00b
 
C

CWit

OMG THANK YOU FRANK!!! Huge Smile on my FACE :) :)

I right clicked the symbol and viewed code and it work!! Thanks Fran
and everyone else for making this Simple but Frustrating Headache g
away..
 
T

Tom Ogilvy

If you look, you will see you don't. I don't doubt that you do actually
have it, but periods on the left margin can get removed in postings.

in the VBE, go to the projecte explorer Ctrl+R if it isn't visible. Under
your workbook name, you should find an entry ThisWorkbook. Double click on
that or right click on it and select view code to get to the ThisWorkbook
module

In the left dropdown at the top, select Workbook and in the Right select
Open.

This shoul put in the proper declaration for the workbook_Open event. This
is the way you should enter event macros. No you can put your code in this
procedure.
 
Top