Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Lounger
    Join Date
    Aug 2009
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi

    I'm trying my absolute hardest......but to no avail

    Here is my code:

    Public Sub NoFirstPageFooter_AllSheets()
    Dim wsSheet As Worksheet
    For Each wsSheet In ActiveWindow.SelectedSheets
    With wsSheet
    ' Clear footer before printing the first page
    .PageSetup.LeftFooter = ""
    ' Print the first page
    .PrintOut From:=1, To:=1
    ' Set the footer for subsequent pages
    .PageSetup.LeftFooter = "&20OTL Tel: 0845 845 0845 Email: someone@email.co.uk" & "&10 All Rights Reserved"
    ' Print the subsequent pages
    .PrintOut From:=2
    End With
    Next wsSheet
    End Sub

    However, I'd like to make the 'OTL, Tel No and Email addy in a certain font and I'd like the copyright line to be two lines down......

    Can anyone help me with regards to what I need to write in my code to enable it to do that?

    Many thanks everyone....

    Penny

  2. #2
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts
    Hi Penny,

    Messing around with the macro recorder in Excel 2000 (which doesn't support coloured header/footer text) and some nominal font attributes, I got:
    Code:
     With ActiveSheet.PageSetup
    		.LeftHeader = _
    		"&16""&""Arial,Bold""&10 0OTL Tel: 0845 845 0845&""Arial,Regular""&16" & Chr(10) & "" & Chr(10) & "&11Email: &""Arial,Italic""someone@email.co.uk&""Arial,Regular""""&16" & Chr(10) & "" & Chr(10) & " &9& "" All Rights Reserved""&16" & Chr(10) & ""
    ...
    ...
    End With
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  3. #3
    Lounger
    Join Date
    Aug 2009
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi Paul

    Thanks everso much for this....really really helpful.

    So, are you saying that I'm not able to change the colour of the font within the header and footer?

    Many thanks.

    Penny

  4. #4
    Lounger
    Join Date
    Aug 2009
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi Hans and Paul

    I want to thank you both so much for your help and support.....between the three of us - I think I've done it!!!

    We'll see what my client thinks - if you don't hear from me then you know we've won!!!

    Ha ha!!

    Many thanks once again.

    Penny

  5. #5
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts
    [quote name='PennyToulson' post='790942' date='27-Aug-2009 22:09']So, are you saying that I'm not able to change the colour of the font within the header and footer?[/quote]
    Hi Penny,

    Not with Excel 2000. Later versions do support colour, though I'm not sure in which version it was introduced. This should only be an issue for you if the workbook is to be used by people running earlier versions of Excel than you use.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    AFAIK, text color in headers/footers was available in early versions of Excel. It was absent from Excel 4.0 through 2003, and only reintroduced in Excel 2007...

  7. #7
    Lounger
    Join Date
    Aug 2009
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi Hans and Paul!

    Just read your last replies - thank you for that bit of information

    Another quick q for you with regards to this. When I run the macro it automatically prints to the default printer - what do I need to do to make it print to a selected printer or to print to pdf file?

    Many thanks....looking forward to your response!

    Penny

  8. #8
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts
    Hi Penny,

    If you want your users to be able to select the printer or to a pdf (via the pdf print driver), you could replace:
    .PrintOut From:=2
    with:
    Application.Dialogs(xlDialogPrint).Show , 2
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  9. #9
    Lounger
    Join Date
    Aug 2009
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi Paul

    This is the macro.......your suggestion worked great in that it brought up the 'Print' box asking me to select the printer - however, within the macro, half way down, it tells it to print the first page. How do I get it to 'ask me' which printer (or to pdf) to print the first page on?

    Public Sub NoFirstPageHeaderorFooter_AllSheets()
    Dim wsSheet As Worksheet
    For Each wsSheet In ActiveWindow.SelectedSheets
    With wsSheet
    ' Clear header before printing the first page
    .PageSetup.LeftHeader = ""
    ' Clear footer before printing the first page
    .PageSetup.LeftFooter = ""
    ' Print the first page
    .PrintOut From:=1, To:=1
    ' Set the header for subsequent pages
    .PageSetup.LeftHeader = "&40RP for " & wsSheet.Range("C31").Value
    ' Set the footer for subsequent pages
    .PageSetup.LeftFooter = "&20 OT Tel: 0845 845 8454 Email: s@ot.co.uk" & Chr(10) & "" & Chr(10) & " &15& "" All Rights Reserved""&16" & Chr(10) & ""
    ' Print the subsequent pages
    .PrintOut From:=2
    End With
    Next wsSheet
    End Sub


    Many thanks Paul! You're being a great help

    Penny

  10. #10
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts
    Hi Penny,

    You could have the print dialogue box pop up twice - once for the first page:
    Application.Dialogs(xlDialogPrint).Show , 1, 1
    then:
    Application.Dialogs(xlDialogPrint).Show , 2
    for the remaining pages.

    Alternatively, if page 1 always goes to a specific printer, you could hard-code it via the PrintOut Method (but I wouldn't recommend doing so, since the code might fail when you eventually replace that printer).
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  11. #11
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts
    I haven't tested this, but I suspect that if the user selects a printer (using the dialog box) for the first page, then you could use Application.Printout to print the rest of the pages and the same printer would be used.

  12. #12
    Lounger
    Join Date
    Aug 2009
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hiya both!

    Thanks ever so much for those tips......I've got that bit working now!

    I can't believe how many....further issues I'm coming across!!

    If I ask the document to print to pdf it doesn't print the text on the document that it would 'print' if I sent it to a printer!!! AARRRRGGHHH......do you know what I need to do to the code to make it print to pdf or hard copy with that very same text on either copy??

    Thanks again

    Penny

  13. #13
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts
    Hi Penny,

    In at least some MS Office apps (especially Word, but I think Excel also), the current printer driver and its settings determine what will fit on a page. When you change printer drivers, as often happens when you change printers, the app reassesses what will fit on the page and adjusts the output accordingly.

    You may be able to gain more control over the output by the judicious use of manual page breaks (basing them on whiichever driver puts the least amount of data on a given page), or by specifying the # pages onto which the output has to fit (thereby forcing Excel to scale the output accordingly).
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  14. #14
    Lounger
    Join Date
    Aug 2009
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi Paul!

    Thanks for your response again.....sorry for the delays in replying.....I only work on this during office hours depending on whether I'm in the office or not!

    With the code that 'we've' written (as in you, I and Hans!!) the printer dialogue box pops up twice - once for instructions on the first page and the second time for instructions on pages 2 to 5 (there are five pages in total in this document).

    When the dialogue box pops up for instructions on the first page - if I ask it to print to pdf all 5 pages they don't have the header and footer on them.

    When the dialogue box pops up for instructions on the second page - if I ask it to print all 5 pages to pdf, all 5 pages have the header and footer on them.

    Obviously, if I'm wanting to forward a pdf to a client, I don't want it to be as two separate documents, i.e the first page with no header and footer and subsequent pages with the header and footer?!?

    Do you have any idea as to how I can achieve one document with no header and footer on the first page and the subsuquent pages with the header and footer?

    Phewf!

    Thanks again

    Penny

  15. #15
    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
    I think, due to limitations of EXCEL, you will have to create the 2 separate PDFs and then use some program to merge the 2 files (I don't know if the fill Acrobat can do this).

    Another option is to print the file to a printer with the 2 separate printouts, then scan it (many copiers do this) to a PDF and send this scanned PDF to the customer.

    Steve

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
  •