Page 1 of 2 12 LastLast
Results 1 to 15 of 25
  1. #1
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Headers and footers (97, 2000,xp,2003)

    Hi everyone,

    I am thinking of developing a utility to easily manage headers and
    footers.

    What options should such a utility have?
    (Yes, I'm looking for ideas)

    - a toolbar that exposes them through comboboxes
    - copy to other sheet
    - apply to all sheets
    - 1st page header
    - remaining pages header
    - ???
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  2. #2
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Headers and footers (97, 2000,xp,2003)

    Nice idea Jan.

    When I insert date and time into the H/F, I often want to format the date or time, ie the date must be in long date format...
    It could be nice to have a feature to enable the user to specify the necessary date and time format for the H/F directly from the toolbar!?
    If this is an option, you could also attempt to have the user add their Login or UserID / User Name into the H/F. I have in the past had to go to Tools Options General to check what the User Name is to add it as a marker into the H/F. A button to do this would be great too!

    Hope these are acceptable/useful ideas!
    Regards,
    Rudi

  3. #3
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Headers and footers (97, 2000,xp,2003)

    All ideas are welcome and yours make perfect sense to me!
    Thanks.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

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

    Re: Headers and footers (97, 2000,xp,2003)

    One of my peeves is roughly explained in my old <post#= 21855>post 21855</post#>, and I think it is still true. A user (me!) can easily overwrite settings they didn't intend to when using complicated intertwining headers and footers (H/F). It's VERY easy to broadcast changes to sheets you don't intend to when you have something like these sample Workbook settings:

    <table border=1><td align=center>Custom
    Header</td><td align=center>Sheet 1</td><td align=center>Sheet 2</td><td align=center>Sheet 3</td><td align=center>Sheet 4</td><td align=center>Sheet 5</td><td align=center>Left</td><td align=center><blank></td><td align=center>&[Page] of &[Pages]</td><td align=center>&[Page] of &[Pages]</td><td align=center>&[Page] of &[Pages]</td><td align=center>&[Page] of &[Pages]</td><tr><td align=center>Center</td><td align=center>Company name
    -John ... I float in liquid gardens
    UTC -7ąDS

  5. #5
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Headers and footers (97, 2000,xp,2003)

    So a utility that makes this process more transparent will be a help, right?
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

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

    Re: Headers and footers (97, 2000,xp,2003)

    <big><big><big>YES!</big></big></big> <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    -John ... I float in liquid gardens
    UTC -7ąDS

  7. #7
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Headers and footers (97, 2000,xp,2003)

    Hi everyone,

    This is my first (zero, beta) version of my Excel Header and Footer manager.

    Have a go and please send me any comments!

    BTW: only the top toolbar of this version is fully functional (headers).
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  8. #8
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    UPDATE: Headers and footers (97, 2000,xp,2003)

    Hi all,

    Here is the latest version...
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

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

    Re: UPDATE: Headers and footers (97, 2000,xp,2003)

    Hi Jan Karel,

    The add-in looks promising. Some comments (you're probably already aware of these points, but anyway):
    - The toolbars aren't updated if the user modifies the header and footer via File | Page Setup... or File | Print Preview > Setup...
    - The toolbars aren't updated when all workbooks are closed (the boxes should be cleared.)
    - Codes as used in File | Page Setup (&[Page], for example) aren't accepted.
    - Codes can't be picked from a list.
    I assume you are working on the latter.

  10. #10
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: UPDATE: Headers and footers (97, 2000,xp,2003)

    Thanks for testing Hans!
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  11. #11
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: UPDATE: Headers and footers (97, 2000,xp,2003)

    Jan Karel,

    I'm not sure of the kind of feedback you're looking for but...

    Many people have asked on the lounge whether it's possible to do certain things as far as numbering physical sheets that get printed as a result of printing a worksheet or change headers/footers when printing a worksheet. Quite some time ago, I had an interaction with Hans (but I think it was not HansV but don't recall; forgive me) that convinced me that, if given the time, I would write a utility that would do this. SInce then, I've answered a few posts saying it was theoretically possible and gave ideas that I developed as a result of the discussion. I still have the posts.

    I'm not sure if this fits in what you view as the headers and footers but here goes. When I think of headers and footers in Excel, I'm also thinking of printing.

    I'm envisioning a print add-in that takes Excel's printing capabilities and puts them into super gear.

    Excel allows you to choose printing across-down or down-across for sheets of papers that result from an Excel sheet. So that has to be maintained and is the driving factor.

    When I print a sheet, I may want to have page numbering of form:
    - sheetname-number (eg, Sheet1-1, Sheet1-2,...; or Sheet1-A, Sheet1-B,...)
    - major number-minor number where major number is like a chapter number
    - etc.

    It is possible in VBA to see how many horizontal and vertical sheet would be printed. I forget the name of the property. But you can step across-down or vice versa under control of VBA in a loop. WIthin the loop, you could change the header/footer so you get the right page numbering per above.

    Page numbering "strategy" would be governed via a form with a bunch of radio buttons with the different alternatives.

    Page control would be via a checkbox for down-across v across-down.

    There would be a checkbox to say whether "interior" blank physical sheets should be printed or counted in the page numbering. By an interior blank physical sheet, I mean a collection of cells that would fill a physical sheet but has nothing on it; however, cells to the right and/or below have entries and come out on a diff physical sheet.

    Once the different options are selected, the add-in would control the printing of "print areas" (which I think are actually used in Hans' response) which just move accordingly. Headers and footers can be changed within a worksheet for different physical sheets under control of the macro as you iterate thru loops.

    I think I had some other ideas also for "print options" but don't have them with me right now. If this is within the scope of what you're doing and you'd like, I'll dig those up.

    Fred

  12. #12
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: UPDATE: Headers and footers (97, 2000,xp,2003)

    Hi Fred,

    I have given those options a thought, and of course this would be a very useful addin indeed.

    But the work it is going to require is daunting and I simply lack the time to do that.

    This little utility I've got so far might grow into what you stated, but it is a loooong way to get there.

    Thanks for your comments.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  13. #13
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: UPDATE: Headers and footers (97, 2000,xp,2003)

    Hi Jan Karel,

    In reviewing the original features in your first post, I can see that my suggestion certainly streeeetches the concept. In fact, it could be viewed as a separate add-in. Other than the last 2 features (first page header and other page header), your list of features really has nothing to do with printing; they have more to do with easing the work of creating headers/footers for other sheets.

    I think to manipulate first page vs other page headers/footers, you have to be talking about printing. I'd almost suggest separating these features from the others if that's the case.

    Just my input.

    Fred

  14. #14
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: UPDATE: Headers and footers (97, 2000,xp,2003)

    They shoul dbe viewed separately, I agree. This little utility is just scraping the surface of what needs to be improved to Excel's printing features.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  15. #15
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: UPDATE: Headers and footers (97, 2000,xp,2003)

    Hi Jan Karel,

    Here are a few comments. I tested against the version that you said was the latest (to which this is a reply).

    1. It's definitely something needed to allow people to easily copy headers/footers (h/f) created for 1 sheet for use in another sheet.

    2. When I first added a h/f thru the normal Page Setup, I did not see the info in the drop-down boxes. I had to change sheets to be able to see what I had entered in Page Setup reflected in the drop-downs. That is, entering a h/f for Sheet 2 thru Page Setup shows nothing in the drop-downs after I exit the Page Setup dialog while I'm still on Sheet 2. If I click on Sheet 3, your h/f boxes are blank but the drop-downs contain the info from Sheet 2, from which I can now choose. If I go back to Sheet 2, I see the items properly filled in on your toolbars. Kind of in agmt with HansV's comment but not entirely.

    3. BTW: it was not immediately obvious when opening a new workbook what the 3 drop-downs were to the right of the sheet drop-down were. There was no tooltip when the drop-downs were blank like there was for the toolbar. This might be disconcerting to a relatively new user of Excel.

    4. When I changed an item (I think it was left footer but I don't think it would matter) by selecting from the drop-down, I did get a msg box about are you sure you want to change (BTW: Footer is spelled as "Foter" in the msg). I said Yes. But now the drop-down shows 2 identical entries. The item I changed From is no longer in the drop-down. I would not think this is expected behavior. A little more experimenting showed a couple of items where the From item was no longer available in the drop-down but one which kept the old entry in the drop-down. I did not track this rigourously.

    5. Something I did triggered the dashed lines that show where the page breaks are. This too is not desirable, at least for me. Can that be turned off in your code? (It's one of the options.)

    6. When I started a new workbook w/o closing Excel, the drop-downs were re-initialized. I would prefer that they not be re-initialized. Kind of the opposite of what HansV found.

    7. Is it the intent that you cannot create info in the drop-downs? All creation is done thru the normal page setup? Wasn't obvious at first. Maybe the documentation (did I hear "what documentation"?) will tell us that.

    8. I looked at your original specs in the first post you did on this.
    --Wasn't obvious how to apply to all sheets. I guess I select the desired sheets. So I can select All, but I also can select more than 1 but less than All. Documentation?
    -- wasn't clear how to do 1st page and remaining page selections.

    9. HansV mentioned about codes (&Page) not being accepted and not being available in a list. Was it the intent to be able to enter even static items (eg, my name) in one of your boxes? I didn't see it in the specs and was not able to do it. Only able to influence what's in the drop-downs, hence what's in the box, by entering items thru Page Setup.

    Well done. I like it. Are you ready for bigger things? :>)

    Fred

Page 1 of 2 12 LastLast

Posting Permissions

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