Results 1 to 10 of 10
  1. #1
    2 Star Lounger
    Join Date
    Mar 2007
    Location
    Montreal, Quebec
    Posts
    125
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Printing content of a cell in footer (Office 2000)

    Hi gang,
    Glad the lounge is up and running again.
    I have a workbook with 15+ worksheets. I want the content of one cell (or a range) to appear in the footer of all worksheets when I print. The content of this cell changes quarterly. It's a real pain to have to copy it in all the footers every time.
    How? and will it keep the format I give it in the cell (strikethrough or underline).
    Thanks.
    Johanne Champagne
    Montreal (Quebec) CANADA

  2. #2
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Printing content of a cell in footer (Office 2

    Hi Johanne

    Have you tried the Camera Tool?

    Good reference here:

    http://j-walk.com/ss/excel/odd/odd04.htm
    Jerry

  3. #3
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Printing content of a cell in footer (Office 2

    ......and then I thought about this:


    Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
    ActiveSheet.PageSetup.LeftFooter = Range("A1").Text
    End Sub


    1) Right hand mouse click the worksheet tab
    2) View Code
    3) Paste the above code in the Editor

    Now when A1's content chages the footer is updated.
    Jerry

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Printing content of a cell in footer (Office 2000)

    Instead of using the Selection_Change of the worksheet, which runs rather often and may slow down things, I'd use the Workbook_BeforePrint event in the ThisWorkbook module. This runs only when the workbook is printed, i.e. when it is needed. For example:

    Private Sub Workbook_BeforePrint(Cancel As Boolean)
    Dim wsh As Worksheet
    For Each wsh In Worksheets
    wsh.PageSetup.CenterFooter = Worksheets("Sheet1").Range("A1")
    Next wsh
    End Sub

  5. #5
    2 Star Lounger
    Join Date
    Mar 2007
    Location
    Montreal, Quebec
    Posts
    125
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Printing content of a cell in footer (Office 2000)

    Thanks Hans.
    One more thing. How can I keep the font format when I print. The footer prints as regular text. I need to print the cell formatted as I see it on the screen.

    tks.
    Johanne Champagne
    Montreal (Quebec) CANADA

  6. #6
    2 Star Lounger
    Join Date
    Mar 2007
    Location
    Montreal, Quebec
    Posts
    125
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Printing content of a cell in footer (Office 2

    Hi Jerry,

    This is a very interesting tool. I see a number of use for it.
    Tks for the tip.
    Johanne Champagne
    Montreal (Quebec) CANADA

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Printing content of a cell in footer (Office 2000)

    If you want the text in the footer to take on the same formatting as the cell, the camera tool might be better suited than VBA code, for the formatting options in the header and footer are limited: you can change some font characteristics, such as font name and size, but not font color, background color or borders.
    If you want to use code, you'd have to write a lot of code to analyze the cell formatting and convert it to formatting codes for the footer.

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Printing content of a cell in footer (Office 2000)

    This is some of what Hans alluded to. It adjusts the Font, the size, and the bold/italic to match the cell...

    Steve

    <pre>Option Explicit
    Private Sub Workbook_BeforePrint(Cancel As Boolean)
    Dim wks As Worksheet
    Dim sFooter As String
    For Each wks In Worksheets
    With Worksheets("Sheet1").Range("a1")
    sFooter = "&" & Chr(34) & .Font.Name
    With .Font
    If .Bold Then
    sFooter = sFooter & ",Bold"
    If .Italic Then
    sFooter = sFooter & " Italic"
    End If
    ElseIf .Italic Then
    sFooter = sFooter & ",Italic"
    Else
    sFooter = sFooter & ",Regular"
    End If
    sFooter = sFooter & Chr(34) & "&" & .Size
    End With
    sFooter = sFooter & .Value
    End With
    wks.PageSetup.CenterFooter = sFooter
    Next
    Set wks = Nothing
    End Sub</pre>


  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Printing content of a cell in footer (Office 2000)

    Or similar:
    <code>
    Private Sub Workbook_BeforePrint(Cancel As Boolean)
    Dim wsh As Worksheet
    Dim strFooter As String
    With Worksheets("Sheet1").Range("A1")
    strFooter = "&" & Chr(34) & .Font.Name & Chr(34)
    strFooter = strFooter & "&" & .Font.Size
    If .Font.Bold Then
    strFooter = strFooter & "&B"
    End If
    If .Font.Italic Then
    strFooter = strFooter & "&I"
    End If
    If .Font.Superscript Then
    strFooter = strFooter & "&X"
    End If
    If .Font.Subscript Then
    strFooter = strFooter & "&Y"
    End If
    If .Font.Strikethrough Then
    strFooter = strFooter & "&S"
    End If
    Select Case .Font.Underline
    Case xlUnderlineStyleSingle
    strFooter = strFooter & "&U"
    Case xlUnderlineStyleDouble
    strFooter = strFooter & "&E"
    End Select
    strFooter = strFooter & .Value
    End With
    For Each wsh In Worksheets
    wsh.PageSetup.CenterFooter = strFooter
    Next wsh
    End Sub
    </code>
    Note: if the text in the cell contains an ampersand & it may be interpreted as a formatting code together with the character that follows it.

  10. #10
    2 Star Lounger
    Join Date
    Mar 2007
    Location
    Montreal, Quebec
    Posts
    125
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Printing content of a cell in footer (Office 2000)

    Spectacular!
    Tks a million guys.
    Johanne Champagne
    Montreal (Quebec) CANADA

Posting Permissions

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