Conversation Re: Cell value in header in Excelhttps://techcommunity.microsoft.com/t5/excel/cell-value-in-header/m-p/1148823#M51085
<P><LI-USER uid="543554"></LI-USER>&nbsp;</P><P>&nbsp;</P><P>think it would be best if we discussed via phone ?</P>Mon, 03 Feb 2020 20:35:06 GMTMOB692020-02-03T20:35:06ZCell value in headerhttps://techcommunity.microsoft.com/t5/excel/cell-value-in-header/m-p/1148638#M51066
<P>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.<BR /><BR />I would the page header to display some values from the workbook as values in the header.&nbsp; 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.<BR />So here is what I would like to be referenced:</P><P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="clipboard_image_1.png" style="width: 400px;"><img src="https://gxcuf89792.i.lithium.com/t5/image/serverpage/image-id/168699iFCCD5335B38F89DF/image-size/medium?v=1.0&amp;px=400" title="clipboard_image_1.png" alt="clipboard_image_1.png" /></span></P><P>&nbsp;</P><P>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.&nbsp;</P><P>&nbsp;</P><P><FONT face="courier new,courier">Function Author()</FONT><BR /><FONT face="courier new,courier">Author = ThisWorkbook.BuiltinDocumentProperties("Author")</FONT><BR /><FONT face="courier new,courier">End Function</FONT></P><P>&nbsp;</P><P><FONT face="courier new,courier">Function Doc()</FONT><BR /><FONT face="courier new,courier">Doc = Worksheets("WorkInstructions").Range("D2").Value = ThisWorkbook.CustomDocumentProperties.Item("Doc").Value</FONT><BR /><FONT face="courier new,courier">End Function</FONT></P><P>&nbsp;</P><P><FONT face="courier new,courier">Private Sub Workbook_BeforePrint(Cancel As Boolean)</FONT><BR /><FONT face="courier new,courier">Dim sTemp As String</FONT></P><P><FONT face="courier new,courier">With Worksheets("WorkInstructions")</FONT><BR /><FONT face="courier new,courier">sTemp = .Range("Z4").Text</FONT><BR /><FONT face="courier new,courier">PageSetup.LeftHeader = sTemp</FONT><BR /><FONT face="courier new,courier">End With</FONT><BR /><FONT face="courier new,courier">End Sub<BR /><BR /><FONT face="arial,helvetica,sans-serif">Any input on this matter would be greatly appreciated, even if you don't think you have the answer. <LI-EMOJI id="lia_slightly-smiling-face" title=":slightly_smiling_face:"></LI-EMOJI></FONT><BR /></FONT></P>Mon, 03 Feb 2020 19:20:24 GMThttps://techcommunity.microsoft.com/t5/excel/cell-value-in-header/m-p/1148638#M51066MrSassyBritches2020-02-03T19:20:24ZRe: Cell value in headerhttps://techcommunity.microsoft.com/t5/excel/cell-value-in-header/m-p/1148761#M51076
<P><LI-USER uid="543554"></LI-USER>Please elaborate as to the determining factor for what data appears..?</P>Mon, 03 Feb 2020 20:08:09 GMThttps://techcommunity.microsoft.com/t5/excel/cell-value-in-header/m-p/1148761#M51076MOB692020-02-03T20:08:09ZRe: Cell value in headerhttps://techcommunity.microsoft.com/t5/excel/cell-value-in-header/m-p/1148763#M51077
The text. Whatever text is in the cell Z2 I want to be displayed in the top left of the header.Mon, 03 Feb 2020 20:11:21 GMThttps://techcommunity.microsoft.com/t5/excel/cell-value-in-header/m-p/1148763#M51077MrSassyBritches2020-02-03T20:11:21ZRe: Cell value in headerhttps://techcommunity.microsoft.com/t5/excel/cell-value-in-header/m-p/1148771#M51079
within the sheet or when printed ,, or both ??<BR />Mon, 03 Feb 2020 20:14:25 GMThttps://techcommunity.microsoft.com/t5/excel/cell-value-in-header/m-p/1148771#M51079MOB692020-02-03T20:14:25ZRe: Cell value in headerhttps://techcommunity.microsoft.com/t5/excel/cell-value-in-header/m-p/1148779#M51080
<P>Well the header wont be visible until printed, so when printed I guess.</P>Mon, 03 Feb 2020 20:16:07 GMThttps://techcommunity.microsoft.com/t5/excel/cell-value-in-header/m-p/1148779#M51080MrSassyBritches2020-02-03T20:16:07ZRe: Cell value in headerhttps://techcommunity.microsoft.com/t5/excel/cell-value-in-header/m-p/1148781#M51081
I probably don't fully understand.<BR /><BR />But can you not insert a formula at A1 that references Z2..?Mon, 03 Feb 2020 20:17:08 GMThttps://techcommunity.microsoft.com/t5/excel/cell-value-in-header/m-p/1148781#M51081MOB692020-02-03T20:17:08ZRe: Cell value in headerhttps://techcommunity.microsoft.com/t5/excel/cell-value-in-header/m-p/1148797#M51083
<P><LI-USER uid="543554"></LI-USER>&nbsp;</P><P>How bout making a1 ( or what ever cell you choose ), =z2 and then designate row 1 as a row title for printing.?</P><P>&nbsp;</P>Mon, 03 Feb 2020 20:23:23 GMThttps://techcommunity.microsoft.com/t5/excel/cell-value-in-header/m-p/1148797#M51083MOB692020-02-03T20:23:23ZRe: Cell value in headerhttps://techcommunity.microsoft.com/t5/excel/cell-value-in-header/m-p/1148805#M51084
would it be better to discuss via phone..?Mon, 03 Feb 2020 20:25:47 GMThttps://techcommunity.microsoft.com/t5/excel/cell-value-in-header/m-p/1148805#M51084MOB692020-02-03T20:25:47ZRe: Cell value in headerhttps://techcommunity.microsoft.com/t5/excel/cell-value-in-header/m-p/1148823#M51085
<P><LI-USER uid="543554"></LI-USER>&nbsp;</P><P>&nbsp;</P><P>think it would be best if we discussed via phone ?</P>Mon, 03 Feb 2020 20:35:06 GMThttps://techcommunity.microsoft.com/t5/excel/cell-value-in-header/m-p/1148823#M51085MOB692020-02-03T20:35:06ZRe: Cell value in headerhttps://techcommunity.microsoft.com/t5/excel/cell-value-in-header/m-p/1148837#M51086
<P>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.</P><P>Here is a screenshot to visualize.<BR /><BR />Here it is now</P><P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="clipboard_image_0.png" style="width: 400px;"><img src="https://gxcuf89792.i.lithium.com/t5/image/serverpage/image-id/168725iE04B73919361D496/image-size/medium?v=1.0&amp;px=400" title="clipboard_image_0.png" alt="clipboard_image_0.png" /></span></P><P>&nbsp;</P><P>and here is what I am looking for (notice the 100 in the top left)</P><P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="clipboard_image_1.png" style="width: 400px;"><img src="https://gxcuf89792.i.lithium.com/t5/image/serverpage/image-id/168726iF380A78EC48D5537/image-size/medium?v=1.0&amp;px=400" title="clipboard_image_1.png" alt="clipboard_image_1.png" /></span></P><P>&nbsp;</P><P>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.</P>Mon, 03 Feb 2020 20:37:57 GMThttps://techcommunity.microsoft.com/t5/excel/cell-value-in-header/m-p/1148837#M51086MrSassyBritches2020-02-03T20:37:57ZRe: Cell value in headerhttps://techcommunity.microsoft.com/t5/excel/cell-value-in-header/m-p/1148874#M51087
<P><LI-USER uid="543554"></LI-USER>&nbsp;</P><P>thinkin I understand now.....&nbsp; researching</P>Mon, 03 Feb 2020 20:57:52 GMThttps://techcommunity.microsoft.com/t5/excel/cell-value-in-header/m-p/1148874#M51087MOB692020-02-03T20:57:52ZRe: Cell value in headerhttps://techcommunity.microsoft.com/t5/excel/cell-value-in-header/m-p/1149466#M51112
The VBA solution didn’t work?<BR /><BR />Private Sub Workbook_BeforePrint(Cancel As Boolean)<BR />ActiveSheet.PageSetup.CenterHeader = Range("Z4").Value<BR />End SubTue, 04 Feb 2020 06:48:59 GMThttps://techcommunity.microsoft.com/t5/excel/cell-value-in-header/m-p/1149466#M51112Charla742020-02-04T06:48:59ZRe: Cell value in headerhttps://techcommunity.microsoft.com/t5/excel/cell-value-in-header/m-p/1150369#M51182
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?Tue, 04 Feb 2020 15:31:58 GMThttps://techcommunity.microsoft.com/t5/excel/cell-value-in-header/m-p/1150369#M51182MrSassyBritches2020-02-04T15:31:58ZRe: Cell value in headerhttps://techcommunity.microsoft.com/t5/excel/cell-value-in-header/m-p/1150396#M51185
You need to create the code for the workbook, not in a sheet.<BR />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.Tue, 04 Feb 2020 15:45:22 GMThttps://techcommunity.microsoft.com/t5/excel/cell-value-in-header/m-p/1150396#M51185Charla742020-02-04T15:45:22ZRe: Cell value in headerhttps://techcommunity.microsoft.com/t5/excel/cell-value-in-header/m-p/1150749#M51208
<LI-USER uid="543554" login="MrSassyBritches"></LI-USER><BR /><BR />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.<BR /><BR />Let me know if you can get it working.Tue, 04 Feb 2020 17:36:58 GMThttps://techcommunity.microsoft.com/t5/excel/cell-value-in-header/m-p/1150749#M51208Charla742020-02-04T17:36:58ZRe: Cell value in headerhttps://techcommunity.microsoft.com/t5/excel/cell-value-in-header/m-p/1151042#M51236
<P><LI-USER uid="531239"></LI-USER>&nbsp;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?<BR /><BR /></P><P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="clipboard_image_0.png" style="width: 400px;"><img src="https://gxcuf89792.i.lithium.com/t5/image/serverpage/image-id/169010i38BA219CB1B1AAD4/image-size/medium?v=1.0&amp;px=400" title="clipboard_image_0.png" alt="clipboard_image_0.png" /></span></P>Tue, 04 Feb 2020 19:29:31 GMThttps://techcommunity.microsoft.com/t5/excel/cell-value-in-header/m-p/1151042#M51236MrSassyBritches2020-02-04T19:29:31ZRe: Cell value in headerhttps://techcommunity.microsoft.com/t5/excel/cell-value-in-header/m-p/1151063#M51240
<LI-USER uid="543554" login="MrSassyBritches"></LI-USER><BR /><BR />Looks good - Give her a print and let me know if it works.Tue, 04 Feb 2020 19:38:06 GMThttps://techcommunity.microsoft.com/t5/excel/cell-value-in-header/m-p/1151063#M51240Charla742020-02-04T19:38:06ZRe: Cell value in headerhttps://techcommunity.microsoft.com/t5/excel/cell-value-in-header/m-p/1151251#M51251
<P><LI-USER uid="531239"></LI-USER>&nbsp;I printed and got an error:</P><P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="clipboard_image_2.png" style="width: 400px;"><img src="https://gxcuf89792.i.lithium.com/t5/image/serverpage/image-id/169029i2DAC3BC944DE36D1/image-size/medium?v=1.0&amp;px=400" title="clipboard_image_2.png" alt="clipboard_image_2.png" /></span></P><P>As for the result, there was nothing printed on the center header <LI-EMOJI id="lia_confused-face" title=":confused_face:"></LI-EMOJI><BR />First I printed to a PDF, I then printed to a physical printer and both had the same result, no center header.</P><P>&nbsp;</P>Tue, 04 Feb 2020 20:49:58 GMThttps://techcommunity.microsoft.com/t5/excel/cell-value-in-header/m-p/1151251#M51251MrSassyBritches2020-02-04T20:49:58ZRe: Cell value in headerhttps://techcommunity.microsoft.com/t5/excel/cell-value-in-header/m-p/1151291#M51252
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.Tue, 04 Feb 2020 20:57:41 GMThttps://techcommunity.microsoft.com/t5/excel/cell-value-in-header/m-p/1151291#M51252Charla742020-02-04T20:57:41Z