Reference Cell in Page Header Using VBA - Excel 2007

[mcapril]

Hello, all! I am stumped!

I have a 150-tab product catalog built in Excel 2007. I want to reference cell A3 (on the tab I have renamed Master) in the header.

My ultimate desired result: we type in XYZ Company in cell A3 on the Master tab, then all left headers on every tab will read XYZ Company.

So far, I've tried inserting these in Worksheet Open and Worksheet BeforePrint:

Please Login or Register  to view this content.

Can anyone tell me where I'm going wrong with the code and where, exactly, in the VBA editor I'm supposed to insert said code?

Thanks in advance!!

[Greg M]

Hi there!

Try inserting the following code into a standard VBA Code Module and see if it does what you want:

Please Login or Register  to view this content.

If the Company Name is not changed, the above macro needs to be run only once - if the Company Name is changed on a regular basis the "heart" of the above routine can be copied to the "Workbook_BeforePrint" routine.

Hope this helps - please let me know how you get on.

Regards,

Greg M

[alansidman]

mcapril;Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here



I have added these for you. Please adhere to our rules in the future.

[mcapril]

I tried inserting what I have quoted below on the VBA Editor Worksheet: BeforePrint and do not see any changes. Is it something I should see populate the header field in page layout view, or will I have to view via print preview in order to see the name?

Yes, the plan is to change the name on a regular basis so that custom price pages may be printed for various prospects, so what would I need to do to run the macro and update the header, please?

Many thanks!!

Hi there!

Try inserting the following code into a standard VBA Code Module and see if it does what you want:

Please Login or Register  to view this content.

alansidman: I am absolutely embarrassed over here. Thank you for correcting my post. I will pay much more attention next time.

[Greg M]

Hello again,

Sorry for the delay in replying.

Yes, Page Headers and Footers (and therefore also any changes to them) are viewable only in the Print Preview view.

Including the "heart" of the previous routine in the Workbook_BeforePrint routine will ensure that the Page Header (modified if appropriate) is generated before the worksheet is printed.

Regards,

Greg M