Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Apr 2002
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Macro for header input (Office 2000)

    This is probably a simple solution but I have been unable to find any resource that helps me.

    I would like to have a macro open up the custom header input window as soon as the sheet opens. Any help or directions to where I can get the information will be greatly appreciated.

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Macro for header input (Office 2000)

    Assuming by Sheet you mean Workbook, open the WB, Alt-F11 to open the VBE. On the left hand Project Explorer window, find the WB, and double click the "This Workbook" under Microsoft Excel Objects. Up top of the VBE window you will see a drop box which says (General). Click it and select Workbook, and the following lines will be created for you:

    <font color=blue>Private Sub Workbook_Open()

    End Sub</font color=blue>

    (It won't appear in blue, I'm using blue to show the code here.) In the middle of that, place the following code:

    <font color=blue> Dim strEntry As String
    Dim shtWS As Worksheet
    Application.ScreenUpdating = False
    strEntry = _
    Application.InputBox("Enter Header", "Header Entry", , , , , , 2)
    For Each shtWS In ThisWorkbook.Sheets
    shtWS.PageSetup.CenterHeader = strEntry
    Next shtWS
    Application.ScreenUpdating = True</font color=blue>

    This code will set the same center heading for every Sheet in the WB. Let us know if you want to vary this.
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    New Lounger
    Join Date
    Apr 2002
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro for header input (Office 2000)

    JohnBF,

    That was EXACTLY what I was looking for. I changed it slightly to reflect my need to place it in the left header.

    My apologies for the lack of proper nomenclature (Sheet when it should have been Workbook).

    Thank you very much for your help!

Posting Permissions

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