Programming header/footer strings with line feed

[XP]

I am using Windows XP with Office 2003.

I have a VBA program in which I can enter a header or footer string in a
sheet and the program enters this string for me into the header or footer of
hundreds of files that are formatted automatically. For example a set up
string might look like:

&"Arial,Bold"&10&UACCOUNT SUMMARY CONTINUED:

The above would be "Arial", bold, 10-points, underlined. This works great.

The only thing is, I need to be able to insert a return or a line feed at
the beginning and/or end of the string and I can't seem to get it to work.
For example, I tried variations of the following without success:

&Chr(10) &"Arial,Bold"&10&UACCOUNT SUMMARY CONTINUED:
&"Chr(13)" &"Arial,Bold"&10&UACCOUNT SUMMARY CONTINUED:

Could someone PLEASE help me out on this and post back a correction to make
it work?

Thanks so much in advance.

 

[Bob Phillips]

CHR(10) works fine

Just tried this fine

With ActiveSheet.PageSetup
.LeftHeader = "&D" & Chr(10) & "&T"
End With


--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[XP]

Thanks Bob,

But if you put that string in a cell in the spreadsheet and then run the
following:

Dim sHeader as string
sHeader = Activecell.Formular1c1
Activesheet.PageSetup.CenterHeader = sHeader

It doesn't work. That is what I'm trying to do...

Thanks for your reply, please help if you can, any ideas?

 

[William Benson]

I recorded this with the macro recorder, maybe it can give you a lead ...


.LeftFooter = "&""Arial,Bold""&11&UMy Idea" & Chr(10) & "Is This"

 

[William Benson]

I may have it ... I think your mistake is in assuming that CHR(10) is a part
of the text of the string, it is really what CHR(10) evaluates to which is a
part of the string.

Play with this logic in the code, you may see what to do:


'This sets the footer the way you want it (just about)
.LeftFooter = "&""Arial,Bold""&11&UMy Idea" & Chr(10) & "Was This"
'Now set F1 = that result
[F1].Value = .LeftFooter

Take a look at F1 ... you will see it separates the line of text
automatically
So this is how you would enter a value manually into F1 (using
Alt-Enter)

&"Arial,Bold"&11&UMy Idea
Was This


'So now ...
.LeftFooter = [F1].Value works

You have to have the actual line break

 

[XP]

Thanks William!

I may have it ... I think your mistake is in assuming that CHR(10) is a part
of the text of the string, it is really what CHR(10) evaluates to which is a
part of the string.

Play with this logic in the code, you may see what to do:


'This sets the footer the way you want it (just about)
.LeftFooter = "&""Arial,Bold""&11&UMy Idea" & Chr(10) & "Was This"
'Now set F1 = that result
[F1].Value = .LeftFooter

Take a look at F1 ... you will see it separates the line of text
automatically
So this is how you would enter a value manually into F1 (using
Alt-Enter)

&"Arial,Bold"&11&UMy Idea
Was This


'So now ...
.LeftFooter = [F1].Value works

You have to have the actual line break

I recorded this with the macro recorder, maybe it can give you a lead ...


.LeftFooter = "&""Arial,Bold""&11&UMy Idea" & Chr(10) & "Is This"