Variables in Excel Header

Is there a way to have VB put variables into the Header/Footer of an Excel worksheet? I'd like to be able to have it so that the header contained a variable like an employee name or something. Anyone got any ideas? Thanks, Jeremy

Code:

---

With ActiveSheet.PageSetup
       
        .CenterHeader = Range("rTitle").Value
       
       
End With

---

this will place the contents of the named range into thecenter header

Anyone know how to adjust the Font in a header programatically?

Dave

The same way you would any other font object in Excel. I'm not being mean about this. What I am saying is that all object in Excel are basically visible to the Object Model. Example:

Code:

---

ActiveSheet.PageSetup.CenterHeader

---

And once you get to an object, you can modify it's attributes/parameters the same way. Example:

Code:

---

objExcelQuote.Selection.NumberFormat = "$#,##0.0000"

---

So...if you put both of what I said together, you'll be able to do this, I assure you. Laters, Jeremy

This is the most control you can get over the headers or footers in Excel.

VB Code:

Sub FormatPageHeaderFooter()
 
    With ActiveSheet.PageSetup
        .LeftHeader = "&""Arial,Bold""&12Font color test"
        '.LeftHeader = "&D" 'PRINTS CURRENT DATE
        .CenterHeader = ""
        .RightHeader = ""
        .LeftFooter = ""
        .CenterFooter = ""
        .RightFooter = ""
        .LeftMargin = Application.InchesToPoints(0.75)
        .RightMargin = Application.InchesToPoints(0.75)
        .TopMargin = Application.InchesToPoints(1)
        .BottomMargin = Application.InchesToPoints(1)
        .HeaderMargin = Application.InchesToPoints(0.5)
        .FooterMargin = Application.InchesToPoints(0.5)
        .PrintHeadings = False
        .PrintGridlines = False
        .PrintComments = xlPrintNoComments
        .PrintQuality = -3
        .CenterHorizontally = False
        .CenterVertically = False
        .Orientation = xlPortrait
        .Draft = False
        .PaperSize = xlPaperLetter
        .FirstPageNumber = xlAutomatic
        .Order = xlDownThenOver
        .BlackAndWhite = False
        .Zoom = 100
        .PrintErrors = xlPrintErrorsDisplayed
    End With
    ActiveWindow.SelectedSheets.PrintPreview
    
End Sub

Jeremy,

Code:

---

ActiveSheet.PageSetup.CenterHeader

---

Does not return an object with which to control the font or even get a Font object.

The "CenterHeader" part is itself an object of type String, and Strings do not return handles to objects like the Font object.

Dave

Quote:

---

Code:

---

ActiveSheet.PageSetup.CenterHeader

---

And once you get to an object, you can modify it's attributes/parameters the same way. Example:

Code:

---

objExcelQuote.Selection.NumberFormat = "$#,##0.0000"

---

So...if you put both of what I said together, you'll be able to do this, I assure you. Laters, Jeremy [/B]

---

Thanks, robdog, this definately works! I assume you got this by recording a macro and looking at the code? I should really learn to do that too...

Quote:

---

.LeftHeader = "&""Arial,Bold""&12Font color test"

---

Dave

Quote:

---

Originally posted by Dave Sell
Jeremy,

Code:

---

ActiveSheet.PageSetup.CenterHeader

---

Does not return an object with which to control the font or even get a Font object.

The "CenterHeader" part is itself an object of type String, and Strings do not return handles to objects like the Font object.

Dave

---

d00d...that was an example of how a user would access object and attributes. You are correct on what you said but in my example, it was to show that you can access object/attributes using that methodology. Thanks, Jeremy

More info on page headers/footers.

Great info, thanks!

No prob.

btw, I did grab the info from recording a macro.
Its really easy. You should try it.

OK this is really shameful, but ... I DID try it. I couldnt figure out how to record a macro... :eek: I know it makes me sound stupid but I just havent tackled it yet.

Maybe you have some quick pointers? I had the macro recording window opened from Excel, and I could see how to play a macro - but I didnt see a way to record one.

Thanks,

Dave

Tools > Macro > Record New Macros... >
Then name the macro as you wish under the Macro Name:
Then store in this workbook, usually unless you want to store
your macro in the personal macros workbook or another
workbook.
Then, add a description if you want.

Click ok and you are recording.
When you are finished, click on the stop button of the macro
toolbar that will popup when you start recording.

Then you can view the code by going to the VBA Editor.

HTH

Thanks, I will definately try it this week!

Got it to work, that'll be worth gold!