Help with VBA Event

[samsilverman]

I am using Excel 2007
I have 3 worksheets I need this code fixed for:
ACCOUNT, TEST AREAS and BILLING

I have this code in my first worksheet:

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

The code works now, but it recalculates the header anytime I click in the worksheet. Two questions:

The first action a user completes is to filter a range (N31:Z300). This changes several cell values in the ACCOUNT worksheet. Is it possible to use this filtering as an event in order to change the headers in all three worksheets above? If so, can you tell me how in very simple terms?

If that won't work, is it possible to narrow down the "SelectionChange" Event to a one cell range or button. The idea would be to have the user click on the cell or button after filtering, thereby updating the three worksheet headers as well. If so, can you tell me how in very simple terms?

Thanks.

 

[BungleNZ]

As opposed to having to rely on the filtering of the range to trigger the event, you could just make it so that any change event in the target range (N31:Z300) will fire the code? This would prevent the code running for changes the are in irrelavant cells.

 

[lenze]

Just use the Before_Print Event per my answer to your previous post. Selection_Change is not the correct approach.

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

This will place the header in ANY sheet before it is printed!!
If you want the headers to vary by sheet someway, you can then test for the ActiveSheet Name and assign the appropriate constraints

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

lenze

 

[samsilverman]

Once again, TY Lenze