Results 1 to 11 of 11
  1. #1
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    Kontich, Vlaanderen, Belgium
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Adding custom Header/Footer to Default Listbox (Excel 97)

    Hi all,

    I have a workbook in which I add every week a new sheet. Contents of each sheet differs, so, in order to print it, I have to do a File|Page Setup every week again.
    The Footer I want to have on each sheet has the same structure : some standard text (same each week) + the info that is on the TAB of the sheet. This goes on the left of the page, on the right of the page it has to be : Page + "Pagenumer" of "Number of pages".

    But this Footer is not one that appears in the "Default Footers Listbox". How can I add my "custom footer" to that "Default List" ?

    I have looked through the other threads, but none gave my the answer.
    Can anyone help me ?

    Best regards,

    Walter

  2. #2
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Adding custom Header/Footer to Default Listbox (Excel 97)

    in the page-setup dialog, where you see the list of default options, you should also see a "Custom" Button - click on this and you'll be able to add your own - sample attached (from 2K but from memory it is the same)

  3. #3
    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: Adding custom Header/Footer to Default Listbox (Excel 97)

    Did you try this <post#=271544>post 271544</post#>

    Steve

  4. #4
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Adding custom Header/Footer to Default Listbox (Excel 97)

    My apologies Walter - looking at Steve's response and the post He links to, I believe I may have misinterpreted your question <img src=/S/blush.gif border=0 alt=blush width=15 height=15> !

  5. #5
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    Kontich, Vlaanderen, Belgium
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Adding custom Header/Footer to Default Listbox (Excel 97)

    Well, Steve's response and the post he links to, is actually what I mean.

    I really appreciate your answer (post 363124) <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15> , but I knew about the possibility of "making" a custom Header/Footer.
    But what I really want is to "save" my "custom-built" Header/Footer, so that it is available in the "drop-down list" the next time I want to setup a page for printing.

    Looking at the thread Steve's answer is in, I must admit I overlooked this one (most probably because it referres to Excel 2000 SR1, and I still use Excel 97).

    In Steve's thread, the post (275206) from Satiria describes what I want, but that thread ends with a post (275248) pointing to a VBA solution. This VBA solution was not posted, so I am still stuck. Anyone who can give me a step-by-step guide-through with a VBA solution ? <img src=/S/help.gif border=0 alt=help width=23 height=15>

    Thanks and best regards,

    Walter

  6. #6
    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: Adding custom Header/Footer to Default Listbox (Excel 97)

    The post I referenced tells how you can add the footer to the default book/sheet and then that default footer would be in the custome list.

    The VB solution will not add it to the custom list. Based on what you want it to do you could add this to your personal.xls file, add it to a toolbar and call it anytime
    Change the "standard text as desired

    <pre>Sub CustomFooter()
    With ActiveSheet.PageSetup
    .LeftFooter = "Standard Text &A"
    .RightFooter = "Page &P of &N"
    End With
    End Sub</pre>


    For info on Personal.xls see the <!profile=LegareColeman>LegareColeman<!/profile>'s star<post#=118382>post 118382</post#>

    Steve

  7. #7
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    Kontich, Vlaanderen, Belgium
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Adding custom Header/Footer to Default Listbox (Excel 97)

    Thank you Steve,

    I added the macro to my Personal.xls and also added a button to my "private toolbar".
    Now I just have to click the button and the Footer I want is inserted.

    But this made me thinking: are there more printsettings that I could add to the macro ? As all the sheets in this specific workbook always have to be printed in Landscape, can I put this in the same macro ? What is the code ?

    Can I define a range in each sheet that I can use to indicate the printrange, and only use the range name in the print settings ? And can this be added to the macro ?

    Yep, one solution leads to more questions .....

    Can you put me on the right track ?

    Best regards,

    Walter

  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: Adding custom Header/Footer to Default Listbox (Excel 97)

    The best way to learn the objects is to use the macrorecorder (tools - macro - record new macro)

    Then while recording, run page setup, and set the options. when done, stop the macro and look at the code in VB. This will give you the code (and more than you want/need!)

    Note, when you use the macro recorder, any dialog you open with the recorder running, all the options (whether you change them or not) will be recorded. It should be "relatively obvious" to you what the items are, dlete the onew you do not want to change/define and keep the things you do.

    If you have questions on them, while in VB select the property/method name and press <F1> and you will go to help on that item. You may also post more questions here for additional clarification if required.

    Once you understand, you can even add to the code to generalize it, prompt the user for items (you could use a message box to ask about landcape/portrait directly at runtime.

    Hope this helps,

    Steve

  9. #9
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    Kontich, Vlaanderen, Belgium
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Adding custom Header/Footer to Default Listbox (Excel 97)

    Thanks again Steve,

    I just recorded a macro with the page-setup settings I usually do for this specific workbook. I looked at the code that was generated, and I think I can use quite a lot of it in my application.

    What you said about "Once you understand, you can even add to the code to generalize it, prompt the user for items (you could use a message box to ask about landcape/portrait directly at runtime." is not clear to me. Could you give me a hint here ?

    Best regards,

    Walter

  10. #10
    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: Adding custom Header/Footer to Default Listbox (Excel 97)

    I was making a general statement. If you want to do something with the code that the macrorecorder does not handle, you will have to be specific about your want/need and we can try to answer it.

    I wanted you to be aware that the recorder was only a starting point. We can answer any specific questions, but without an idea of what you want to do with it, we can't give any general solutions.

    Steve

  11. #11
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    Kontich, Vlaanderen, Belgium
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Adding custom Header/Footer to Default Listbox (Excel 97)

    OK, Steve!

    When I have a more specific question, I will come back with a new post.
    In the meantime, thanks for your help.

    Best regards,

    Walter

Posting Permissions

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