Custom Headers??

[Marq]

is there a formula that will populate a custom header once I populate a cell??

what im trying to accomplish is:

If Cell C7 on worksheet titled "SUM" equals (whatever I type in it)

then custom header on worksheet "Phase 1" worksheet equals whatever i type in cell C7 on SUM worksheet

and also custom header on worksheet "Phase 2" equals whatever I type in cell C7 on SUM worksheet

is this possible?

Marq

 

[Norie]

I don't think it is possible with a formula. You probably need code.

Does something like this work?

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim strHeader
Dim I As Integer

    strHeader = Worksheets("SUM").Range("C7")
    
    For I = 1 To 2
        Worksheets("Phase " & I).PageSetup.CenterHeader = strHeader
    Next I
    
End Sub

To use this right click the Excel icon next to File on the main toolbar and select View Code, then paste the above.

 

[Marq]

nope...doesnt work...I pasted that text into the sheet titled "Phase 1", then went to my "SUM" sheet to cell C7, typed in the word "test" and nothing showed up in the custome header

 

[Norie]

The code runs before a workbook is print.

It does not go in a worksheet module, it goes in the ThisWorkbook module.

 

[Marq]

ya' lost me on that one

I'm wanting the custom header to be centered.......18 size font....& bold

whatever i type into cell C7 on the "SUM" worksheet I want to show up in "Phase 1" worksheet header........and "Phase 2 worksheet header"

and your saying the code u posted will make that happen when i go to print?? Thats how im understanding what your saying (im not up to speed with the VB stuff.....its WAY beyond me)

 

[Norie]

Where have you put the code?

Is it in the workbook module?

What do you see when you goto Print Preview?

If you want to change the font then you'll need more code.

 

[Marq]

I put the code onto the "Phase 1" worksheet. I right clicked on the tab titled "Phase 1"......then clicked on "view code"....a blank white screen popped up...I copied and pasted the info into it.......saved......then went to "SUM" sheet...typed in "test" in C7........went back to Phase 1 sheet.....clicked on Print Preview...........the custom header had nothing in it (except fot eh existing text I keep in it already...and the existing text is not in the first row position.....I have existing text that will never change in header....and i start it on the second row down in the header)

for example:

this is what it looks like BEFORE I type in the center custom header (NOTE - excluding the "row 1"..."row 2" text...i put that there to show u the postiions of where the text is at

row 1
row 2 Phase 1 Estimate


Now, when I make the estimate for a work package I put the work package number in the header which then makes it look like this:

row 1 P030699
row 2 Phase 1 Estimate


Currently I manually type in the P030699 into the custom header.

and when it comes time to do the Phase 2 estimate for the same package I follow the same routine on the Phase 2 worksheet


On my SUM sheet (which is my master sheet on the work book) I type the workpqckage number into cell C7.............thats the VERY first thing I do when i open this work book and begin an estimate. So I'm trying to automate as much as possible..........so when I type in P030699 into cell C7 of SUM sheet it will pop up in custom header of Phase 1 and Pahse 2 work sheets (along with the existing text that already is saved into their respected header

 

[Norie]

To use this right click the Excel icon next to File on the main toolbar and select View Code, then paste the above.

Did you not follow this instruction given in a previous post?

I haven't mentioned right clicking on sheet tabs anywhere.

 

[Marq]

ok....u got me....i didnt do the excel icon.....ive seen the view code option before when i right clicked on the tab.....but i see that theres a code for sheet...AND a code for the book......guilty as charged

so i went to the excel icon...did the whole routine....typed "test" into C7 on SUM sheet...went to Phase 1 sheet...clicked print preview and this came up

Private Sub Approved () <a blue box>click()
End Sub
Private Sub Textbox1_Change()
End Sub

also a small bix popped up with the words "Compile Error" and "invalid Character"

and that blue box i mentioed was exatly that...a blue rectangular box of sorts....its the only way i could describe it

would this be happening because i have existing text saved in the custom headers already???

 

[Norie]

Marq

It looks like you have some existing code.

Delete it.