make formattted variable in a header work (VBA & Excel 2002) 1

[LTillner]

I tried the above and couldn't get it to work.

I'm using a with statement and doing the following:

    Windows("datafilemacros.xls").Activate
    Dim vbySubtestTitle As String, vFooterPreparedby As String
    vbySubtestTitle = Worksheets("constants").Range("BySubtestTitle")
    vFooterPreparedby = Range("FooterPreparedby")
    
    Windows("bySubtestData.xls").Activate
    With ActiveSheet.PageSetup
        .PrintTitleRows = "$1:$1"
        .PrintTitleColumns = ""
    End With
    ActiveSheet.PageSetup.PrintArea = ""
    With ActiveSheet.PageSetup
        .LeftHeader = ""
        .CenterHeader = vbySubtestTitle
        .RightHeader = ""
        .LeftFooter = vFooterPreparedby
        .CenterFooter = "&D, &T"
        .RightFooter = "Page &P of &N"
        .LeftMargin = Application.InchesToPoints(0.5)
        .RightMargin = Application.InchesToPoints(0.5)
        .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 = 600
        .CenterHorizontally = False
        .CenterVertically = False
        .Orientation = xlLandscape
        .Draft = False
        .PaperSize = xlPaperLetter
        .FirstPageNumber = xlAutomatic
        .Order = xlDownThenOver
        .BlackAndWhite = False
        .Zoom = False
        .FitToPagesWide = 1
        .FitToPagesTall = 100
        .PrintErrors = xlPrintErrorsDisplayed
    End With

I can get EITHER the parameter to appear correctly, or the formatting to work, but then the parameter appears as the parameter name, not the named range.

Using two .CenterHeader lines didn't work, one overwrites the other:

        .CenterHeader = vbySubtestTitle
        .CenterHeader = "&""ARIAL,BOLD""&12"

using the suggestion in thread707-935461 gives me an error that "Method Range of object _Global failed"

        .CenterHeader = "&14 &B &ARIAL" & Range("bySubtestTitle")

What am I missing? There must be some way to make formatted variable in a header work (VBA and Excel 2002)

Thanks!
Lynette

 

[SkipVought]

Hi,

        .CenterHeader = "&14 &B &ARIAL" & Range("bySubtestTitle").Value

ought to work as long as bySubtestTitle is a valid SINGLE CELL range


Skip,
[sub]
[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?" [/sub]

 

[PHV]

And this ?
.CenterHeader = "&14 &B &ARIAL " & vbySubtestTitle

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[LTillner]

Neither works.

Skips suggestions (adding the .value) to the Range statement gives me the Runtime Error 1004 again. And it is a single cell range. In that single cell however, is a concatenation of 2 different cells -- does that make a difference?

PH's suggestion doesn't error out but gives me the following:

"myTabRIAL MyTitleIReallyWant"

so when I look at the Header format it is now Size 14, Bold and Arial but instead of the text just being my desired title, it is myTabRIAL then my title.

Does &A mean the same as &tab?

Strange,

Lynette

 

[LTillner]

OK, I took the &ARIAL out and got my formatted header....

Now, how can I make the type face WORK???

 

[PHV]

And this ?
.CenterHeader = "&""ARIAL,BOLD""&14" & vbySubtestTitle

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[SkipVought]

Your syntax is screwed up.

Macro record again.

Skip,
[sub]
[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?" [/sub]

 

[LTillner]

Thanks PH, have a star. It makes sense that the Font name needed to be FIRST!

Skip, everytime I recorded the macro the font name was last, maybe it was the order I was selecting things in, I don't know, but re-recording it wasn't helping in the first place.

Thanks for all the help guys,

Lynette