Custom Headers??

Marq

Well-known Member
Joined
Dec 13, 2004
Messages
914
Office Version
  1. 365
  2. 2007
Platform
  1. Windows
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
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,318
Office Version
  1. 365
Platform
  1. Windows
I don't think it is possible with a formula. You probably need code.

Does something like this work?
Code:
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

Well-known Member
Joined
Dec 13, 2004
Messages
914
Office Version
  1. 365
  2. 2007
Platform
  1. Windows
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,318
Office Version
  1. 365
Platform
  1. Windows
The code runs before a workbook is print.

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

Marq

Well-known Member
Joined
Dec 13, 2004
Messages
914
Office Version
  1. 365
  2. 2007
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Apr 28, 2004
Messages
76,318
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Dec 13, 2004
Messages
914
Office Version
  1. 365
  2. 2007
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Apr 28, 2004
Messages
76,318
Office Version
  1. 365
Platform
  1. Windows
Norie said:
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

Well-known Member
Joined
Dec 13, 2004
Messages
914
Office Version
  1. 365
  2. 2007
Platform
  1. Windows
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,318
Office Version
  1. 365
Platform
  1. Windows
Marq

It looks like you have some existing code.

Delete it.
 

Forum statistics

Threads
1,148,397
Messages
5,746,460
Members
424,020
Latest member
LongDoo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top