| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Format sheet header via vba
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Format sheet header via vba
ActiveSheet.PageSetup.RightHeader = "&28" & Range("A2").Value
BTW............why are you running this from a selectionchange event? Gord Dibben MS Excel MVP On Thu, 4 Dec 2008 16:29:07 -0000, "Forum freak \(at work\)" wrote: 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Format sheet header via vba
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!) "Gord Dibben" <gorddibbATshawDOTca wrote in message ... ActiveSheet.PageSetup.RightHeader = "&28" & Range("A2").Value BTW............why are you running this from a selectionchange event? Gord Dibben MS Excel MVP On Thu, 4 Dec 2008 16:29:07 -0000, "Forum freak \(at work\)" wrote: 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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Format sheet header via vba
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. "Forum Freak" wrote: 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!) "Gord Dibben" <gorddibbATshawDOTca wrote in message ... ActiveSheet.PageSetup.RightHeader = "&28" & Range("A2").Value BTW............why are you running this from a selectionchange event? Gord Dibben MS Excel MVP On Thu, 4 Dec 2008 16:29:07 -0000, "Forum freak \(at work\)" wrote: 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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Format sheet header via vba
Or just add an extra space between the two values.
ActiveSheet.PageSetup.RightHeader = "&28 " & Range("A2").Value Forum Freak wrote: 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!) "Gord Dibben" <gorddibbATshawDOTca wrote in message ... ActiveSheet.PageSetup.RightHeader = "&28" & Range("A2").Value BTW............why are you running this from a selectionchange event? Gord Dibben MS Excel MVP On Thu, 4 Dec 2008 16:29:07 -0000, "Forum freak \(at work\)" wrote: 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 -- Dave Peterson |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Format sheet header via vba
Thanks Guys
Both worked! Kenny "Dave Peterson" wrote in message ... Or just add an extra space between the two values. ActiveSheet.PageSetup.RightHeader = "&28 " & Range("A2").Value Forum Freak wrote: 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!) "Gord Dibben" <gorddibbATshawDOTca wrote in message ... ActiveSheet.PageSetup.RightHeader = "&28" & Range("A2").Value BTW............why are you running this from a selectionchange event? Gord Dibben MS Excel MVP On Thu, 4 Dec 2008 16:29:07 -0000, "Forum freak \(at work\)" wrote: 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 -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do i change column header from R[1] C format to A1 format? | Excel Discussion (Misc queries) | |||
| Column header format | Excel Discussion (Misc queries) | |||
| How can you format a date in the header/footer of Excel sheet | Excel Discussion (Misc queries) | |||
| help with header row format | Excel Discussion (Misc queries) | |||
| Format a header when using Range() | Excel Programming | |||
Linear Mode
