How to get the Right Header Number to change after every print?
 

I have the macro below that will show the counter on the printed sheet but I
need the number to change with each print from the right header text. I was
lucky to get the code below and information from Gord Dibben and Gary''s
Student. I would really appreciate any help I can get. Thanks Jennifer


This macro will create a custom property that will hold the counter:

Sub firstone()
Dim dp As DocumentProperties
Set dp = ThisWorkbook.CustomDocumentProperties
dp.Add Name:="jenny", _
LinkToContent:=False, _
Type:=msoPropertyTypeNumber, _
Value:=1
End Sub

and then the print sub:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
If ActiveSheet.Name = "Sheet1" Then
n = ThisWorkbook.CustomDocumentProperties("jenny").Val ue
n = n + 1
ActiveSheet.PageSetup.RightHeader = n
ThisWorkbook.CustomDocumentProperties("jenny").Val ue = n
End If
End Sub

How to get the Right Header Number to change after every print?
 

Jennifer

I have tested this and I get an incremented number in right header each time I
print the sheet. Even after saving/closing then reopening.

What are you getting for a number in right header?

Only change I would make is to make the value in "jenny" a zero rather than 1 to
start with.

Sub firstone()
Dim dp As DocumentProperties
Set dp = ThisWorkbook.CustomDocumentProperties
dp.Add Name:="jenny", _
LinkToContent:=False, _
Type:=msoPropertyTypeNumber, _
Value:=0
End Sub

Go to FilePropertiesCustom and delete the "jenny" property then re-run the
firstone macro,


Gord


On Wed, 29 Aug 2007 05:42:05 -0700, Jennifer-Houston, TX
wrote:

I have the macro below that will show the counter on the printed sheet but I
need the number to change with each print from the right header text. I was
lucky to get the code below and information from Gord Dibben and Gary''s
Student. I would really appreciate any help I can get. Thanks Jennifer


This macro will create a custom property that will hold the counter:

Sub firstone()
Dim dp As DocumentProperties
Set dp = ThisWorkbook.CustomDocumentProperties
dp.Add Name:="jenny", _
LinkToContent:=False, _
Type:=msoPropertyTypeNumber, _
Value:=1
End Sub

and then the print sub:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
If ActiveSheet.Name = "Sheet1" Then
n = ThisWorkbook.CustomDocumentProperties("jenny").Val ue
n = n + 1
ActiveSheet.PageSetup.RightHeader = n
ThisWorkbook.CustomDocumentProperties("jenny").Val ue = n
End If
End Sub