Results 1 to 6 of 6
  1. #1
    Star Lounger
    Join Date
    Jun 2001
    Location
    Illinois
    Posts
    59
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Manager Name In Header (Excel 2000 / SR-1)

    I am creating a template for a report, and I would like to place the managers name, entered in the properties, into the header. Is there a way to do this?

    I was also wondering if there is a way to have numbers with a label in a cell. I would like to have something like "110 days" in several cells, and then add up the numbers.

    Last, I have dates set up as 3/23/2001, and I would like to subtract one from another to create a number. Like "4/2/2001 - 3/23/2001" would equal 6 days. This actually equals 10 days, but I want it to exclude Sat. and Sun. from the subtraction.

    Thanks for you help,
    Bithmus

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

    Re: Manager Name In Header (Excel 2000 / SR-1)

    > to place the managers name, entered in the properties, into the header

    ActiveSheet.PageSetup.LeftHeader = ActiveWorkbook.BuiltinDocumentProperties("Manager" ).Value

    > 110 days in several cells, and then add up the numbers

    Just enter the number and the sum function, then use the Format, Cells menu with the Number tab to create the custom format: 0 " days"

    > Number of workdays between two dates
    Use the Tools, AddIns... menu to add the Analysis toolpack (and the VBA Analysis toolpack if doing this in VBA) then use the NETWORKDAYS worksheet function

    Hope this helps! --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
    Star Lounger
    Join Date
    Jun 2001
    Location
    Illinois
    Posts
    59
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Manager Name In Header (Excel 2000 / SR-1)

    I currently have
    &"Tab" -- I used quotes because the square brackes wouldn't show up when around tab.
    UNIV OF IL
    "Manager Name"
    as of &[Date]

    in my center header.
    I tried doing ActiveSheet.PageSetup.LeftHeader =
    ActiveWorkbook.BuiltinDocumentProperties("Manager" ).Value
    in the VBS editor, but nothing happened. When does PageSetup happen?

    I was wondering if I could set a value like manager = ActiveWorkbook.BuiltinDocumentProperties("Manager" ).Value
    and then put &[manager] into the header.

    Also, I am looking for a way to create a bunch of sheets for different managers in one workbook. Could I somehow set something in each sheet, and have it inserted into the header?

    Thanks,
    Bithmus

  4. #4
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Manager Name In Header (Excel 2000 / SR-1)

    That depends where the Manager's name in the properties has been entered. if you have put his name in the Username property, then add the following line of code to the ThisWorkbook module in the Workbook_BeforePrint event of the template:

    ActiveSheet.PageSetup.LeftHeader = Application.UserName

    or simply


    ActiveSheet.PageSetup.LeftHeader = "Manager"

  5. #5
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Winnipeg, Canada
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Manager Name In Header (Excel 2000 / SR-1)

    Try this. Record a macro to setup the header info and then modifiy it to contain the value of a cell on the active sheet. The cell contains the manager's name, resulting from a lookup in the ManagerList on a separate sheet.

    The macro would look something like:

    Sub Setup_Header()
    Dim myManager As String

    myManager = ActiveSheet.Range("C1").Value

    With ActiveSheet.PageSetup
    .PrintTitleRows = ""
    .PrintTitleColumns = ""
    End With
    ActiveSheet.PageSetup.PrintArea = "$B$3:$C$12"
    With ActiveSheet.PageSetup
    .LeftHeader = ""
    .CenterHeader = "&A" & Chr(10) & "UNIV of IL" & Chr(10) & myManager & Chr(10) & "as of &D"
    .RightHeader = ""
    .LeftFooter = ""
    .CenterFooter = ""
    .Zoom = 100
    End With
    ActiveWindow.SelectedSheets.PrintPreview
    End Sub

    Read the .CenterHeader code and see attached xlt to
    try it.
    Attached Files Attached Files

  6. #6
    Star Lounger
    Join Date
    Jun 2001
    Location
    Illinois
    Posts
    59
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Manager Name In Header (Excel 2000 / SR-1)

    Thanks for your help, I'll try this out over the next few days, and re-post if I need any more help.

    Thanks everyone,
    Bithmus

Posting Permissions

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