Formatting Headers through VBA

[yussi1870]

In my code I copy a header from a differnt workbook to a new workbook. The format of the old header can vary. I then have to reformat the new header to be TimesNewRoman,Bold and 12 size. It seems the formatting of the old header gets caught in the text whenever I try to reformat it.

Any ideas? Is it possible to store the old header in a variable, rip out the formatting, paste in the new formatting and then set the new header equal to this variable? I can't figure out a way to do this though.

Thanks.

[Sbirro Cornuto]

On 2002-03-20 08:04, yussi1870 wrote:
In my code I copy a header from a differnt workbook to a new workbook. The format of the old header can vary. I then have to reformat the new header to be TimesNewRoman,Bold and 12 size. It seems the formatting of the old header gets caught in the text whenever I try to reformat it.

Any ideas? Is it possible to store the old header in a variable, rip out the formatting, paste in the new formatting and then set the new header equal to this variable? I can't figure out a way to do this though.

Thanks.

ActiveSheet.PageSetup.LeftHeader = "&""Times New Roman,Bold""&12" & your left Header

same goes of course for CenterHeader and RightHeader

[yussi1870]

I entered the code:

ActiveSheet.PageSetup.CenterHeader = "&""Times New Roman,Bold""&12" & sHeader

sHeader contains the header of another worksheet and is in Arial, Bold, 10. This is concatenated with a cell in the current worksheet. .CenterHeader code above doesn't even effect the formatting! The header from the other worksheet is still in Arial, Bold, 10 and the Cell value is still in its original formatting of Arial, Bold, 12.

Any ideas?

Thanks.

[Sbirro Cornuto]

Oh yeah, I'm sorry, I didn't read your original post very well now did I? If the source of the new Header is an old one you do need to rip out the formatting. How to do it? Like the big man said I'll be back! I really gotto go now, but I dig the solution up for you. I've done it in one of my prior projects, it shouldn't be a problem to find it...

Check me later, all right?

[Sbirro Cornuto]

On 2002-03-20 11:39, yussi1870 wrote:
I entered the code:

ActiveSheet.PageSetup.CenterHeader = "&""Times New Roman,Bold""&12" & sHeader

sHeader contains the header of another worksheet and is in Arial, Bold, 10. This is concatenated with a cell in the current worksheet. .CenterHeader code above doesn't even effect the formatting! The header from the other worksheet is still in Arial, Bold, 10 and the Cell value is still in its original formatting of Arial, Bold, 12.

Any ideas?

Thanks.

Sorry man it took so long, but here is something you can try:

*********************************************

Function sHeader(FullHeader As String)

Dim FontStripped As String
Dim StyleStripped As String

On Error Resume Next

StripIndex1 = InStr(1, FullHeader, ",")

If StripIndex1 = 0 Then

StyleStripped = FullHeader

GoTo Size

End If

FontStripped = Mid(FullHeader, StripIndex1)

StripIndex2 = InStr(1, FontStripped, Chr(34))

StyleStripped = Mid(FontStripped, StripIndex2 + 1)

Size:

sHeader = StyleStripped

If IsNumeric(Mid(StyleStripped, 2, 1)) Then
sHeader = Mid(StyleStripped, 3)
End If

If IsNumeric(Mid(StyleStripped, 2, 2)) Then
sHeader = Mid(StyleStripped, 4)
End If

If IsNumeric(Mid(StyleStripped, 2, 3)) Then
sHeader = Mid(StyleStripped, 5)
End If

End Function

*********************************************

It has worked for me in a similar situation, It's not good programming, I know, but try it and change it better if you wish. Just call the function and it should return one stripped up header value.

[yussi1870]

Thank you so much! This worked like a charm. Thanks!