Change font in header

[sandyaggie]

I want to change the font in the page header, but can't figure out how to do it in VB. This macro formats data exported from another program. That program populates the header, which is fine, but I just want to change the font.

Can I somehow pick up the current text in the header into a variable?

Any other ideas?

 

[VoG]

Example

ActiveSheet.PageSetup.CenterFooter = "&""Times New Roman,Regular""&14" & Sheets("Employees").Cells(i, 1).Value

 

[sandyaggie]

Thanks for that fast reply.

If I'm not mistaken, your code sets the text header to be whatever is in the worksheet called "Employees".

I want my code to leave the text of what is already in the header and change the font. Here's my example:

ActiveSheet.PageSetup.RightHeader = "&""Tempus Sans ITC,Bold""&12 & [Company Name text goes here]

 

[VoG]

Maybe like this (untested)

With ActiveSheet.PageSetup
    s = .RightHeader.Text
    .RightHeader = "&""Tempus Sans ITC,Bold""&12 & s"
End With

 

[sandyaggie]

Drat. Tried that and it came back with a 424 error- Object Required.

Any other ideas?

So close and yet so far!

 

[VoG]

Try deleting .Text

 

[sandyaggie]

Tried that, but then it ends up picking up the formatting info as well. That is, the variable's value becomes ""&""Times New Roman,Regular""&14 & [Company Name text]"". Unfortunately, it doesn't extract the text from the header.

I also tried .value and got the same Object Required error.

 

[VoG]

So maybe like this

.RightHeader = replace(...,...)

 

[sandyaggie]

Genius! I didn't even know Replace existed. I learn something new every every day. Thank you so much!

 

[VoG]

So do I. You are welcome