Formula in Custom Header

Microsoft Excel 2003 (full)
February 20, 2010 at 06:33:00
Specs: Windows XP, 1.0Gb
I am trying to use a formula in a custom header. I need to reference cell A1 in spreadsheet "Summary." I used "=summary!a1" (without the quotes) but the header printed "=summary!a1" (without the quotes).

Thank you.


See More: Formula in Custom Header

Report •


#1
February 20, 2010 at 08:57:36
I suggest you use a BeforePrint or BeforeSave macro in the ThisWorkbook module of VBA.

Use Alt-F11 to open the VBA editor.

In the left hand pane, double click the ThisWorkbook module.

Paste this code into the pane that opens:

Option Explicit
Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim rFormulaCell As Range
 Set rFormulaCell = Sheets("Summary").[A1]
  ActiveSheet.PageSetup.CenterFooter = rFormulaCell
End Sub


Report •

#2
February 20, 2010 at 09:30:17
DerbyDad03,

Thank you for your reply. I typed the code into the VBA editor but do not know how use it so that the correct data will be printed in the custom header..

Brian W


Report •

#3
February 20, 2010 at 09:57:11
My error...I used CenterFooter

Change that string to read CenterHeader

Following the instructions I offered earlier, make sure you paste the code into the ThisWorkbook module.

When you print the sheet, the code will run first and put whatever value is in A1 in the CenterHeader.

If this is not working for you, you'll need to tell us what is happening.


Report •

Related Solutions

#4
February 20, 2010 at 17:54:55
DerbyDad03,

It is not working. I'm not sure how to paste the code into the spreadsheet module. The cell I need is A1 in "Summary" and I want the information in Summary:A1 to print in the custom header in spreadsheet "Telephone."

Thank you.
Brian W


Report •

#5
February 20, 2010 at 18:51:42
The following step-by-step instructions worked for me. If they don't work for you, you'll have to tell me exactly where things don't work as I described.

Since I can't see what you are doing from where I'm sitting, nor do I know what your level of knowledge is, I'm going to be very specific. Don't take anything I say as an insult, I'm only trying to help.

The method I am suggesting uses what is known as a "BeforePrint Macro". The BeforePrint macro is a feature available within Excel VBA that allows you to automatically run a VBA macro immediately after Print or Print Preview is chosen.

These step-by-step instructions should explain how to place the BeforePrint macro into your workbook.

1 - Highlight the code at the bottom of this response. Either right-click and choose Copy or hit Ctrl-C. This will place a copy of the code on the Windows Clipboard.

2 - Open the workbook with the Summary and Telephone sheets.

3 - Hold down the Alt key and press F11. This should open the VBA editor. The title of this window should end in [Sheet1 (Code)]

4 - On the left hand side of the window you should see a pane entitled "Project - VBAProject". Assuming that the workbook where you want the custom header to be placed is the only one open, you should see a list of the sheets in the workbook. The workbook that I tested this in causes the list to look something like this:

Sheet1 (Summary)
Sheet2 (Telephone)
Sheet3 (Sheet3)
ThisWorkbook

If you have more sheets with different names, you should them all, with ThisWorkbook at the bottom of the list.

5 - Double-click the word ThisWorkbook. The title bar at the top of the window should now end with the string:

[ThisWorkbook (Code)]

6 - Right-click in the big empty pane on the right hand side of the window and choose Paste. This should place the Code you copied into that pane.

The pasted text should contain some blueish text and some black text, but no red text. If it contains any red text something is wrong. Other than the color differences, the code should look exactly as it does below.

7 - Click the Red X in the upper right corner of the VBA Editor window to close it.

8 - Back in the Telephone sheet, either Print it or use Print Preview. Whatever value is in Summary!A1 should be placed into Center Header.

If you change the value in Summary!A1 and choose Print or Print Preview, the CenterHeader will change to that new value.

If you are unable to see the results I described in any of these steps you'll have to tell me exactly what you see (or don't see). Simply stating that it's "not working" will not allow me to help you since I can't see what you are doing.

Option Explicit
Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim rFormulaCell As Range
 Set rFormulaCell = Sheets("Summary").[A1]
  ActiveSheet.PageSetup.CenterHeader = rFormulaCell
End Sub


Report •

#6
February 20, 2010 at 19:49:14
DerbyDad03,

Thank you for your assistance; I realize that you can not "see" what I am doing.

I was unable to get this to work. When I hold down the Alt key and press F11, I get a blank screen with 'Microsoft Visual Basic - budget.xls" as the title. I can not paste the code to the screen and there aren't any panes on the left side of the windows.

Additionally, I get a Security Message concerning macros and I pressed "enable".

Once again, thank you for your assistance.

Brian W


Report •

#7
February 20, 2010 at 20:32:32
In Response #2 you said:

I typed the code into the VBA editor

If you don't have any panes, where did you type the code?

In any case, try this.

1 - In Excel, go to Tools...Macro...Security

2 - If the Low setting is not selected, read the Warning associated with the Low setting, and if you agree, click it.

3 - Use Alt-F11 to open the VBA Editor.

4 - Go to View...Project Explorer. This should give you the list that contains the names of your sheets and ThisWorkbook.

5 - Double click ThisWorkbook and the [ThisWorkbook (Code)] window should open.

6 - Paste the code in that window.

7 - Close the VBA window

8 - In the spreadsheet, choose Print or Print Preview


Report •

#8
February 21, 2010 at 05:24:17
DerbyDad03,

Thank you for your patience in helping me with this issue; the formula in the custom header prints correctly.

Brian W


Report •

#9
February 21, 2010 at 07:24:00
Glad to have been of assistance.

Report •


Ask Question