Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Mar 2001
    Location
    Gauteng, South Africa
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Referencing a cell from a header (Excel 97)

    I want to reference a cell (say "A1") to the header info (not Print_Titles). MS Knowledgebase shows how it can be done with a macro. Can it be done without a macro, or can the macro be run automatically each time I preview or print? Other people will also be using this file, and neither I nor they are proficient in macros.

  2. #2
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Referencing a cell from a header (Excel 97)

    Gideon, you want to use the BeforePrint event:
    1. <LI>From Excel, press <Alt> <F11> to get to the VBE
      <LI>In the Project Explorer Windor (upper left), double click on ThisWorkbook
      <LI>In the code window that appears, enter this code <pre>Option Explicit
      Private Sub Workbook_BeforePrint(Cancel As Boolean)
      Dim s As Object ' Worksheet or Chart
      For Each s In ActiveWindow.SelectedSheets
      s.PageSetup.RightHeader = "Printed for " & [Sheet1!A1]
      Next s
      End Sub</pre>

      Note -- It is best to copy this code from the Lounge, paste it into Word, then copy again and paste it into the code window. Saves typos.

      <LI>Change the fifth line of code to use the protion of the header that you want to use, the text that you want, and the cell that you want.
    HTH --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Referencing a cell from a header (Excel 97)

    No, it can only be done from a macro, formulas are not allowed in the print header or footer. Yes, it can be done automatically every time you print or preview by putting the code into the workbook before print event routine. The following code will take a date from cell A1 on Sheet1 and put it into the Center portion of Sheet1's print header:

    <pre>Private Sub Workbook_BeforePrint(Cancel As Boolean)
    Worksheets("Sheet1").PageSetup.CenterHeader = Format(Worksheets("Sheet1").Range("A1"), "mmmm d, yyyy")
    End Sub
    </pre>


    To find the workbook before print routine, first open the VBE editor by pressing Alt+F11. Then in the project explorer at the right of the screen find your workbook, right click on the ThisWorkbook object and then click on View code. Now in the code edit window click on the arrow in the list box on the top left of the window (it should say (General) when the window is first opened) and select Workbook from the list. Then click on the arrow in the left list box and select BeforePrint from that list. That should display an empty procedure in the edit window where you can put your code.
    Legare Coleman

  4. #4
    New Lounger
    Join Date
    Mar 2001
    Location
    Gauteng, South Africa
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Referencing a cell from a header (Excel 97)

    Thanks to both of you for the prompt reply - it was easier than it sounded. Now my boss thinks I'm only too clever!

    Gideon

  5. #5
    2 Star Lounger
    Join Date
    Dec 2000
    Posts
    140
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Referencing a cell from a header (Excel 97)

    You (probably) could use the trick shown in this slick utility:
    <A target="_blank" HREF=http://www.rb-ad.dircon.co.uk/rob/excelvba/utils/index.htm>http://www.rb-ad.dircon.co.uk/rob/ex...tils/index.htm</A>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •