Code for Worksheet Headers

[samsilverman]

I am using Excel 2007.

I currently have a code in each of 3 worksheet modules. They are all slightly different, but basically they change the Worksheet Header when an action is initiated. Here is an example:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column = 5 Then
ActiveSheet.PageSetup.CenterHeader = "&""Calibri""&B&18TEST AREAS " & Chr(10) & Sheet1.Range("C5")
End If
End Sub

What I want to do is change the Worksheet Headers for WorkSheets 1, 2 and 3 based on a cell selection or cell change in WorkSheet1. Is this possible?

Each WorkSheet's Header would have different info, but all three would use the same font/formatting as the code indicates above. Part of the info for each header would include a reference to Sheet1, cell C5.

Thanks

Thank you.

 

[xld]

Re: Code for Worsheet Headers

This is workbook event code.
To input this code, right click on the Excel icon on the worksheet
(or next to the File menu if you maximise your workbooks),
select View Code from the menu, and paste the code

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
If Target.Column = 5 Then
Sh.PageSetup.CenterHeader = "&""Calibri""&B&18TEST AREAS " & Chr(10) & Sh.Range("C5")
End If
End Sub

 

[samsilverman]

Thanks for the attempt. Perhaps I didn't explain clearly.

I need the headers in sheet1 ("account"), sheet2 ("test areas") and sheet3 ("billing") to change after I perform a single action. If I had my way, that action would be a change in cell K5 in sheet1.

Upon changing the value in K5 of sheet1, I would want the headers to say the following:

Sheet1 Header would say: "ACCOUNT" plus whatever is in C5 in Sheet1
Sheet2 Header would say: "TEST AREAS" plus whatever is in C5 in Sheet1
Sheet3 Header would say: "BILLING" plus whatever is in C5 in Sheet1

Each header would be sized 18, Calibri font, and bold. The word I have in quotes would be on the first line in the header. The value from C5/Sheet1 would be on the second line in the header.

Right now I have a macro in each worksheet module that makes the header change after I perform an action in either a column or row in each of those worksheets. The problem with that is the worksheets calculate each time I click that column or row. Plus, I have to perform three different/deliberate actions to get the three changes.

If you have an idea for a fix, I would much appreciate it.
Thanks.

 

[lenze]

Sam: Can't you combine this code with the previous Before_Print event we developed.

PageSetup.CenterHeader = "&""Calibri""&B&18TEST AREAS " & Chr(10) & Sheet1.Range("C5")

I don't see the necessity of this approach as the Beforeprint will set then each time you select print
lenze

 

[samsilverman]

Hey Lenze -

You're really patient with newbies like myself (that's sincere, not sarcasm).

The solution works fine as BeforePrint...for me. The issue is the database is being used by other techs who aren't as familiar with these workarounds and I am fearing (perhaps unfounded) that they will be uncomfortable with the headers not changing until a print command is executed. (A lot of work will be done in the 3 sheets before printing).

Anyway, thanks for all your great input and I'll probably just use this solution and hope for the best with those who are "rookier" than me in our company.

sam

 

[samsilverman]

Lenze -

Problem...how do I get the BeforePrint code to execute the macro for 3 different worksheets, each with a unique header?

I used your suggested code and tried to modify it like this, but I get an error when I try to print:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
If ActiveSheet.Name = "ACCOUNT" Then
ActiveSheet.PageSetup.CenterHeader = "&""Calibri""&B&18ACCOUNT " & Chr(10) & Sheet1.Range("C5")
If ActiveSheet.Name = "TEST AREAS" Then
ActiveSheet.PageSetup.CenterHeader = "&""Calibri""&B&18TEST AREAS " & Chr(10) & Sheet1.Range("C5")
If ActiveSheet.Name = "BILLING" Then
ActiveSheet.PageSetup.CenterHeader = "&""Calibri""&B&18BILLING " & Chr(10) & Sheet1.Range("C5")
End If
End Sub

The error was: Compile Error Block If without End If

 

[samsilverman]

Lenze -

Final fix. Thanks for inspiring me in the right direction.

I played around with worksheet events and chose the following after several experiments:

Private Sub CommandButton1_Click()
Sheet1.PageSetup.CenterHeader = "&""Calibri""&B&18ACCOUNT " & Chr(10) & ActiveSheet.Range("C5")
Sheet2.PageSetup.CenterHeader = "&""Calibri""&B&18TEST AREAS " & Chr(10) & ActiveSheet.Range("C5")
Sheet3.PageSetup.CenterHeader = "&""Calibri""&B&18BILLING " & Chr(10) & ActiveSheet.Range("C5")
End Sub

The Headers are updated by clicking on a button at the top of Sheet1. The button is titled "Update Headers" and it runs the above code placed in the Sheet1 MEO.

Probably a crude solution by pro standards (certainly not as automated), but it is obvious and provides an immediate change.

Thanks again for your help and suggestions.