VB Code to make text blue and on two lines please???

[PennyToulson]

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

[macropod]

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:

 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

[PennyToulson]

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

[PennyToulson]

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

[macropod]

[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.

[HansV]

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...

[PennyToulson]

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

[macropod]

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

[PennyToulson]

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

[macropod]

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).

[StuartR]

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.

[PennyToulson]

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

[macropod]

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).

[PennyToulson]

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

[sdckapr]

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