Cell value in header

[MrSassyBritches]

PS: If there is another way to do this, like reference a named section somehow from within the header, that's what the ^[Doc] was meant to be. But everything online points to VBA.

I would the page header to display some values from the workbook as values in the header.  I did google this and none of the results we're able to produce anything working. Not even any errors, the headers just stay blank.
So here is what I would like to be referenced:

And here is the VBA code I have been trying to make work. This is just the latest snippet I have worked with, Its been a few days of scouring the internet for something that will work. I am convinced it's my implementation that is incorrect. 

Function Author()
Author = ThisWorkbook.BuiltinDocumentProperties("Author")
End Function

Function Doc()
Doc = Worksheets("WorkInstructions").Range("D2").Value = ThisWorkbook.CustomDocumentProperties.Item("Doc").Value
End Function

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim sTemp As String

With Worksheets("WorkInstructions")
sTemp = .Range("Z4").Text
PageSetup.LeftHeader = sTemp
End With
End Sub

Any input on this matter would be greatly appreciated, even if you don't think you have the answer. :)

[#2]

@MrSassyBritchesPlease elaborate as to the determining factor for what data appears..?

[#3]

The text. Whatever text is in the cell Z2 I want to be displayed in the top left of the header.

[#4]

within the sheet or when printed ,, or both ??

[#5]

Well the header wont be visible until printed, so when printed I guess.

[#6]

I probably don't fully understand.

But can you not insert a formula at A1 that references Z2..?

[#7]

@MrSassyBritches 

How bout making a1 ( or what ever cell you choose ), =z2 and then designate row 1 as a row title for printing.?

[#8]

would it be better to discuss via phone..?

[#9]

@MrSassyBritches 

think it would be best if we discussed via phone ?

[#10]

I can assure you my communication skills only get worse verbally. I am not looking for a row title, as that is already present in my workbook.

Here is a screenshot to visualize.

Here it is now

and here is what I am looking for (notice the 100 in the top left)

I dont want the user to have to edit the header, they edit that cell next to "Doc" and have the header use that cell.

[#11]

@MrSassyBritches 

thinkin I understand now.....  researching

[#12]

The VBA solution didn’t work?

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

[#13]

So the code I found online, in my original post at the top, I could not get to work. The functions Author() and Doc() do work. I just tried what you posted and could not get it to work, do I need something like ^[Workbook_BeforePrint] in the header or something along those lines?

[#14]

You need to create the code for the workbook, not in a sheet.
Open VBA editor, double click ‘This Workbook’ in the Microsoft Excel Objects folder (in the ‘Project - VBAProject’ panel to the left). In the code window, you will see two drop down menus; in the first select Workbook and the second Before Print...then type the rest of the code as below.

[#15]

@MrSassyBritches

Ok, so I just tested this myself and also couldn't get it to work. I found a solution (or rather a workaround) from the Microsoft support pages....So, if you do as per my last post BUT also enter the same code in a regular module, it will work. Although the background code is 'BeforePrint' the header does not actually display until it has been printed.

Let me know if you can get it working.

[#16]

@Charla74 Thanks so much for the reply. I think I followed your instructions. Here is a screenshot of "ThisWorkbook" and the exact same code is inside "Module1". Does this look correct per your instructions?

[#17]

@MrSassyBritches

Looks good - Give her a print and let me know if it works.

[#18]

@Charla74 I printed and got an error:

As for the result, there was nothing printed on the center header :\
First I printed to a PDF, I then printed to a physical printer and both had the same result, no center header.

[#19]

I wasn’t sure but I suspected that the two FUNCTION segments, in the Worksheet code, may cause an issue. If you remove those two parts of the code and leave them as they are in the regular module you may have a better outcome.