Can you put a formula in a header?

B

bozrdang

Title pretty much says it all. I simply want to be able to have th
contents of a cell entered as text in my header. For example I want m
header to read "Part #1234". In cell D6 the actual part number "1234
is entered. Can I do this somehow
 
D

Dave O

Do you mean a column header on a sprdsht? If yes a formula will do it:
using your example,
="Part #"&D6
 
M

Miguel Zapico

You may use the workbook event "Before_print". This basic macro will
substitute whatever you have in the center header with the values of the
range A1 on the active sheet:

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

You can make it as complicate as you want, the event takes place even before
the print preview, so you can see the results before printing.

Hope this helps,
Miguel.
 
B

bozrdang

Thanks guys! That does exactly what I asked, my only problem is that
Since it's a header I need it to be a larger font size and bold. Not
to mention that it was meant to be the 2nd line in 3 lines of text in
the Header. Some brief searching seems to indicate you can't format
text in VBA. My ultimate goal was something like this:

* C o m p a n y N a m e *
PART # 1234 WORK INSTRUCTIONS
::page 1 of 2::


LOL, I must be a high maintenance Office user becasue I dabble a little
bit in both Excel and Access and I invariably end up wanting to do
something that seems relatively simple, but ends up being extremely
difficult to achieve. :rolleyes:
 
M

Miguel Zapico

Well, VBA may be able to do what you need. The best way of finding out is
recording a macro while you do all the formating that you want to do, and
then look at the code that has been generated. In the line that sets the
CenterHeader you may find the code you are looking for.

Miguel.
 
B

bozrdang

Thanks a ton Miguel! I managed to get it to do exactly what I wanted
doing that. I've never played with macros before and I've only played
with VBA in Access so I didn't know you could do that. It's kinda like
building a query in QBE in Access and then looking at the SQL code it
creates. Very handy.

I guess some posts I read elsewhere were incorrect about formatting
text through VBA. At least in a header anyway.

Thanks again Miguel and Dave!
 
M

Miguel Zapico

That's a good way of saying it, I have also used QBE to take a look at the
SQL after :)

I'm glad that it helped,
Miguel.
 
Top