Custom Print Header

C

Chuckles123

Is it possible to include a cell reference in a print header? I hav
one cell containing a variable string that I would like to appear i
the printed header.

Thanks in advance for your input.

Chuckles12
 
P

Paul B

Chuckles, you can with VBA, try this, will put whats in A1 in the right
header

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

To put in this macro, from your workbook right-click the workbook's icon and
pick View Code. This icon is to the left of the "File" menu this will open
the VBA editor, in the left hand window double click on thisworkbook, under
your workbook name, and paste the code in the window that opens on the right
hand side, press Alt and Q to close this window and go back to your
workbook, now this will run every time before you print the sheet. If you
are using excel 2000 or newer you may have to change the macro security
settings to get the macro to run. To change the security settings go to
tools, macro, security, security level and set it to medium


--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003
 
C

Chuckles123

Paul,

Thanks much -- it was very helpful. :)

Two complicating issues:

- my macros are stored in an .xlA file on a shared drive for ou
network; if the code is under "ThisWorkbook" in the .xlA file, then th
print command, generated from vba code in a module and on a workbook "d
jour", will never see it - right?

- I want the right header to be a combination of cell A1 plus canne
text that will not be in cell A1; my idea is that the canned text woul
be part of the vba code.

Thanks in advance, again.
Chuckles12
 
C

Chuckles123

I moved the code to a separate procedure in the same module that i
being referenced by the custom button I created to adtivate the prin
macro.
The custom header is not being picked up.

Chuckles12
 
P

Paul B

Chuckles, I have never used a macro from a metwork drive so I can not help
you with that, I would think that you could just call this line of code when
you want to change the header and it would work, this should add you "canned
text" to what is in A1

ActiveSheet.PageSetup.RightHeader = Range("A1") & " Your Text Here"

--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003
 
C

Chuckles123

Paul,

Thanks for the string formula; it works with the "canned text
before/after 'Range ("A1")'. :)
Side note: Range("A1").Value gives exactly the same result; I a
somewhat surprised that the suffix is not required by vba.

OK, now we are getting to the heart of my issue. Having the macr
stored in an .xlA file on a network shared drive has nothing to do wit
it; the heart of the matter is NOT having the Private Sub coding in th
same workbook that is being printed. As we speak, I do not have acces
to the network; so I set-up the Private Sub coding in an .xlS fil
(MACRO FILE) on my hard drive (this file also contains other macro
that are linked to custom buttons that I have set up).
Then, when I open another workbook file and activate a print macro, th
custom header is not printed.

If I enter some data into a worksheet for the MACRO FILE, specify a
appropriate Print Area, and then Print ==> the custom header i
printed. (This is how I learned the results in my first paragrap
above.)

Your continued input would be appreciated.

Chuckles12
 
P

Paul B

Chuckles, before print is a workbook event, to do it from somewhere else
have a look here http://www.cpearson.com/excel/AppEvent.htm

You could put in somewhere else and just call it when you want to change the
header
Sub My_Header()
ActiveSheet.PageSetup.RightHeader = Range("A1") & " Your Text Here"
End Sub


--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003
 
C

Chuckles123

Paul,

Thanks again.
With some modification, I came up with the following. It seems to
work:


Under ‘ThisWorkbook’:

Option Explicit
Dim gxlApp As New Class1
__________________________________________________
____________________
Private Sub Workbook_Open()
Set gxlApp = New Class1
Set gxlApp.xlApp = Application
End Sub


Under Class1 (a named ‘Class Module’):

Option Explicit
Public WithEvents xlApp As Application
__________________________________________________
____________________
Private Sub xlApp_WorkbookBeforePrint(ByVal Wb As Workbook, Cancel As
Boolean)
With ActiveSheet.PageSetup
CenterHeader = "&""Arial,Bold""&16A ""BLENDED"" HIT LIST - " &
Range("A2").Value
End With
End Sub

Chuckles123
 
Top