Formatting header

Last Edited By Krjb Donovan
Last Updated: Mar 05, 2014 09:37 PM GMT

Question

QUESTION: Good day Bob, I'd like to format the existing text in the header and/or footer. The keyhere is EXISTING text. The code written REMOVE what has been entered manually and replaces it by blank ("")

In the LeftHeader, I place the acronym "EWCE", which is good.

Here is an extract of the code used. With ActiveSheet.PageSetup

   .LeftHeader = "&""Arial,Bold""&26EWCE"
   .CenterHeader = "&""Arial,Bold""&26"
   .RightHeader = "&""Arial,Bold""&26"
   .LeftFooter = "&D at &T"
   .CenterFooter = ""
   .RightFooter = "&8&Z&F" & Chr(10) & "&A"

End With

Hope this is clear!

ANSWER: With ActiveSheet.PageSetup

  .LeftHeader = "&""Arial,Bold""&26" & .LeftHeader
  .CenterHeader = "&""Arial,Bold""&26" & .CenterHeader
  .RightHeader = "&""Arial,Bold""&26" & .RightHeader
  .LeftFooter = "&D at &T"
  .CenterFooter = ""
  .RightFooter = "&8&Z&F" & Chr(10) & "&A"

End With


---------- FOLLOW-UP ----------

QUESTION: Get a compilation error. Run-time error '1004' Unable to set the LeftHeader property of the PageSetup class. Any idea?

ANSWER: Works fine for me -- supply the entire VBA code.

---------- FOLLOW-UP ----------

QUESTION: I no longer get the message, strange enough! However, if I run this simple code, it worked only once for the Center & Right Header. If I change the parameter (font, font size) and run the macro again, it does NOT replicate the change!!!! Only the parameters changes for the Left header were applied. Tried this code on an another computer and did not work either. The only way to make it work, is to run this code on a new workbook. But then again, it will work once. Any ideas why?

Sub FormatHeader()

   Range("A1").Select
   With ActiveSheet.PageSetup
     .LeftHeader = "&""Courier,Bold""&26EWCE"        'Place EWCE in Left Header
     .CenterHeader = "&""Arial,Bold""&26" & .CenterHeader  'Takes whatever text is in Center Header and format it
     .RightHeader = "&""Courier,Bold""&28" & .RightHeader  'Takes whatever text is in Right Header and format it
     .LeftFooter = "&D at &T"
     .CenterFooter = ""
     .RightFooter = "&8&Z&F" & Chr(10) & "&A"
   End With

End Sub

Answer

Because it keeps growing. After running it a few times, the new center header, for example, becomes: &"Times New Roman,Bold"&36XX&"Arial,Bold"&36XX&"Arial,Bold"&36&"Arial,Bold"&36&"Arial,Bold"&36&"Arial,Bold"&26

You should first clear the contents of the header

Advertisement

©2021 eLuminary LLC. All rights reserved.