Variables in Excel Header

[JCScoobyRS]

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

[adocwra]

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

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

[Dave Sell]

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

Dave

[JCScoobyRS]

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:

ActiveSheet.PageSetup.CenterHeader

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

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

[RobDog888]

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

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

[Dave Sell]

Jeremy,

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

ActiveSheet.PageSetup.CenterHeader

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

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]

[Dave Sell]

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...

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

Dave

[JCScoobyRS]

Originally posted by Dave Sell
Jeremy,

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

[RobDog888]

More info on page headers/footers.

[Dave Sell]

Great info, thanks!

[RobDog888]

No prob.

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

[Dave Sell]

OK this is really shameful, but ... I DID try it. I couldnt figure out how to record a macro... 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

[RobDog888]

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

[Dave Sell]

Thanks, I will definately try it this week!

[Dave Sell]

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