Hello,

I have a VB script that I was running for all of my worksheets in Excel 2007
to
put a date in the header of all worksheets in a workbook before printing.
When
I upgraded to 2010 it no longer worked. I've gone through the Trust Center
and
enabled macros and clicked trust access to VBA. The script being run is as
follows:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
ActiveSheet.PageSetup.RightHeader = Range("'revisiondate'!b2").Value
End Sub

Any ideas on how to get this to run again?

Thanks

What does "no longer worked" mean? Do you get an error message?
Did it function, in xl2007, in every workbook or just in a particular workbook?
Have you changed anything in the workbook?
In what module is the code installed?
What is "'revisiondate'!b2" ?
--
Jim Cone
Portland, Oregon USA
http://www.mediafire.com/PrimitiveSoftware
(Extras for Excel add-in: convenience built-in)



"doki60"
wrote in message
...
Hello,

I have a VB script that I was running for all of my worksheets in Excel 2007
to
put a date in the header of all worksheets in a workbook before printing.
When
I upgraded to 2010 it no longer worked. I've gone through the Trust Center
and
enabled macros and clicked trust access to VBA. The script being run is as
follows:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
ActiveSheet.PageSetup.RightHeader = Range("'revisiondate'!b2").Value
End Sub

Any ideas on how to get this to run again?

Thanks

Jim Cone wrote on 11/02/2011 07:36 ET :
What does "no longer worked" mean? Do you get an error message?
Did it function, in xl2007, in every workbook or just in a particular
workbook?
Have you changed anything in the workbook?
In what module is the code installed?
What is "'revisiondate'!b2" ?
Jim Cone
Portland, Oregon USA
http://www.mediafire.com/PrimitiveSoftware
(Extras for Excel add-in: convenience built-in)



"doki60"
wrote in message
news:
Hello,

I have a VB script that I was running for all of my worksheets in Excel 2007
to
put a date in the header of all worksheets in a workbook before printing.
When
I upgraded to 2010 it no longer worked. I've gone through the Trust Center
and
enabled macros and clicked trust access to VBA. The script being run is as
follows:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
ActiveSheet.PageSetup.RightHeader = Range("'revisiondate'!b2").Value
End Sub

Any ideas on how to get this to run again?

Thanks


Thanks for your response. Here are the answers to your questions.

No longer worked means the worksheet doesn't change the existing date in the
worksheet. When I go to print the worksheet as indicated in the script,
whatever date was in the header does not change to the new date in cell b2 of
the revision date worksheet located within the current workbook. It seems
like
th script is not running.

It did previously function in every workbook run in xl2007.

Nothing has changed in the workbook.

The code is in Microsoft Visual Basic for Applications.

"'revisiondate'!b2" = revisiondate is a worksheet within the workbook
where the date is stored. b2 is the cell where the date is stored. With this
I
am able to change the date in that cell and have it update in the header for
all
worksheets. I test this in the worksheet by changing the date and hitting
print
preview to see if the date in the header changes.

try...

Private Sub Workbook_BeforePrint(Cancel As Boolean)
ActiveSheet.PageSetup.RightHeader = _
Sheets("revisiondate").Range("B2").Value
End Sub

Optionally, you could assign a name to the cell on each sheet that
contains this date. Obviously, the defined name will have to have local
scope to be able to use the same name on every sheet. If there's no
place on each sheet for this date and it's confined to sheet
"revisiondate" then just select that cell and type "RevisionDate" into
the Namebox left side of the Formula Bar and press the Enter key. Now
you can just refer to it in VBA as follows:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
ActiveSheet.PageSetup.RightHeader = Range("RevisionDate").Value
End Sub

HTH

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

GS wrote on 11/02/2011 11:01 ET :
try...

Private Sub Workbook_BeforePrint(Cancel As Boolean)
ActiveSheet.PageSetup.RightHeader = _
Sheets("revisiondate").Range("B2").Value
End Sub

Optionally, you could assign a name to the cell on each sheet that
contains this date. Obviously, the defined name will have to have local
scope to be able to use the same name on every sheet. If there's no
place on each sheet for this date and it's confined to sheet
"revisiondate" then just select that cell and type
"RevisionDate" into
the Namebox left side of the Formula Bar and press the Enter key. Now
you can just refer to it in VBA as follows:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
ActiveSheet.PageSetup.RightHeader = Range("RevisionDate").Value
End Sub

HTH

Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

All good ideas but still no change. I suspect something is preventing the
routine from running btu I can't figure out what it would be.

Garry's sub should work for you...
'---
Private Sub Workbook_BeforePrint(Cancel As Boolean)
ActiveSheet.PageSetup.RightHeader = _
Sheets("revisiondate").Range("B2").Value
End Sub
'---
Take the line...
ActiveSheet.PageSetup.RightHeader = Sheets("revisiondate").Range("B2").Value
and place it in a standard module and run it.
It should place the value from B2 in the header.
If that works, then let me ask this question again... What module is your code in?

There are code modules behind each sheet in a workbook.
There is a code module for each userform.
There are standard and class modules that the code writer can add.
There is a "ThisWorkbook" code module.

Your code belongs in the ThisWorkbook module.
'--
Jim Cone
Portland, Oregon USA .
http://www.contextures.com/excel-sort-addin.html .
(editorial review of Special Sort excel add-in (30 ways to sort)




"doki60"
wrote in message
...
GS wrote on 11/02/2011 11:01 ET :
try...

Private Sub Workbook_BeforePrint(Cancel As Boolean)
ActiveSheet.PageSetup.RightHeader = _
Sheets("revisiondate").Range("B2").Value
End Sub

Optionally, you could assign a name to the cell on each sheet that
contains this date. Obviously, the defined name will have to have local
scope to be able to use the same name on every sheet. If there's no
place on each sheet for this date and it's confined to sheet
"revisiondate" then just select that cell and type
"RevisionDate" into
the Namebox left side of the Formula Bar and press the Enter key. Now
you can just refer to it in VBA as follows:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
ActiveSheet.PageSetup.RightHeader = Range("RevisionDate").Value
End Sub

HTH

Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

All good ideas but still no change. I suspect something is preventing the
routine from running btu I can't figure out what it would be.

On Wed, 02 Nov 2011 12:01:36 -0400, GS wrote:

try...

Private Sub Workbook_BeforePrint(Cancel As Boolean)
ActiveSheet.PageSetup.RightHeader = _
Sheets("revisiondate").Range("B2").Value
End Sub

Optionally, you could assign a name to the cell on each sheet that
contains this date.


That is what I do. Make the cell a named range and make calls to that
range. No VB needed for that part at least.

(of course you can call it whatever you want as long as you do not break
the named range naming convention rules)

RevDate

In the upper left corner where the cell location is stated. Then make
all you calls to that, and std formula manipulations are all you need to
grab it or add to or subtract from it. Normal date functions.







Obviously, the defined name will have to have local
scope to be able to use the same name on every sheet. If there's no
place on each sheet for this date and it's confined to sheet
"revisiondate" then just select that cell and type "RevisionDate" into
the Namebox left side of the Formula Bar and press the Enter key. Now
you can just refer to it in VBA as follows:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
ActiveSheet.PageSetup.RightHeader = Range("RevisionDate").Value
End Sub

HTH