Formatting Headers through VBA

yussi1870

Board Regular
Joined
Mar 18, 2002
Messages
139
Office Version
  1. 365
Platform
  1. Windows
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.
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
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
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,040
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top