Format sheet header via vba

[Forum freak \(at work\)]

Hi

I have managed to write code to add the contents of a cell to the sheet
header.

This works ok but I need to alter the font size to 28 and nothing I have
tried works.

I tried pre setting the header to 28 but when I ran my code it reverted to
size 8
I tried setting the cell format to 28 but this did not work
I tried adding "&28" into my code but this just removed the value
altogether.

The code I am using is as follows

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
ActiveSheet.PageSetup.RightHeader = Range("A2").Value
End Sub

How do I modify this so the font is 28 size?

TIA

Kenny
XP Pro
Office 2003

 

[Gord Dibben]

ActiveSheet.PageSetup.RightHeader = "&28" & Range("A2").Value

BTW............why are you running this from a selectionchange event?


Gord Dibben MS Excel MVP

 

[Forum Freak]

Thanks for your help Gord but your suggestion did not work on my sheet.

Your code does work if cell A2 contains text however A2 has the formula
=TEXT(K3,"YYYY") I also tried typing in a number into A2 rather than a
formula, this did not work either!

Incidentally I installed the code in the selectionchange event I was trying
to automate everything on the sheet after the user enters a date in cell B1.
This then populates other cells. The fact that you commented suggests bad
practice on my behalf? Perhaps the code would be better placed in
beforeprint.

Any further suggestions?
Kenny - Forum freak (now at home!)

 

[JLGWhiz]

It certainly does not like numbers, even if they are converted to text. It
still sees the number. No problem with other text.

To force it to work, I picked a cell and put "Yr" in it then concatenated
that cell with the formula in cell A2. Viola, pops up Yr2008 in 28 point
font.

 

[Dave Peterson]

Or just add an extra space between the two values.

ActiveSheet.PageSetup.RightHeader = "&28 " & Range("A2").Value

 

[Forum Freak]

Thanks Guys

Both worked!

Kenny

Or just add an extra space between the two values.

ActiveSheet.PageSetup.RightHeader = "&28 " & Range("A2").Value