Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Jan 2001
    Posts
    51
    Thanks
    0
    Thanked 0 Times in 0 Posts

    user defined function in footer

    I am trying to put our document management system's (PC DOCS) document number in Excel 97 footers.

    =PCDOCSProfileInfo("DOCNUMBER") works in a cell and produces a number, such as 999999.

    When I put it in the footer, it shows up as the literal text, not the number.

    Any ideas?

    Thanks.

  2. #2
    2 Star Lounger
    Join Date
    Dec 2000
    Location
    eastern Connecticut, Connecticut, USA
    Posts
    113
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: user defined function in footer

    If you can get the results in a cell, you can insert the cell value in the footer via a VBA statement placed in the WorkBook BeforePrint event (also fires for Print Preview).

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

    Re: user defined function in footer

    Unfortunately, Excel does not evaluate any formula or functions put into the header or footer. To do what you want to do, you will need to do it from a macro. Put something similar to the following in a macro to put your document number in the center footer:

    <pre> Worksheets("Sheet1").PageSetup.CenterFooter = PCDOCSProfileInfo("DOCNUMBER")
    </pre>

    Legare Coleman

  4. #4
    Star Lounger
    Join Date
    Jan 2001
    Posts
    51
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: user defined function in footer

    Thanks for your reply. Unfortunately, as a newbie, I am not familiar with "events." Can you point me in the right direction? Thanks again for your help.

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

    Re: user defined function in footer

    Open the workbook and press Alt+F11 to get into the VBA editor. On the left sid of the resulting window that should be a window with a label of "Project - VBAProject". If this window is not there, then from the View menu select "Project Explorer" to display it. In this window, find your workbook name in parenthesis after a label that says "VBAProject". Under this line that should be a list of all the sheets in your workbook followed by "This Workbook". Right click on "This Workbook" and select "View Code" from the pop up menu. Now on the right side of the window, there should be two dropdown list boxes near the top. The left one should say "General". Drop this list down and select "Workbook" (it should be the only other choice). There should now be a procedure template in lower part of the window for a procedure named "Private Sub Workbook_Open()". Ignore this and go to the drop down list on the right side, drop it down, scroll up and select "BeforePrint". You should now have a procedure template for a propcedure named "Private Sub Workbook_BeforePrint(Cancel As Boolean)". You can put the code to put the document number into the footer here.

    You can use a statement like the one in my other post in this thread to insert the document number into the footer. Hoever, using this will call your document number function and put the returned number into the footer every time you print the document. That means that if the function returns a different document number each time, then the document number in the footer will change each time you print it. If that is what you want, then that is great. If that is not what you want, they you may need to use a different event procedure, or store the document number somewhere else and move it from there to the footer.
    Legare Coleman

Posting Permissions

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