VB Script not working in Excel 2010

[doki60]

Hello

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

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

Any ideas on how to get this to run again

Thanks

 

[Jim Cone]

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" <[email protected]>
wrote in message
news:[email protected]...

 

[doki60]

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 particula workbook
Have you changed anything in the workbook
In what module is the code installed
What is "'revisiondate'!b2"
Jim Con
Portland, Oregon US
http://www.mediafire.com/PrimitiveSoftwar
(Extras for Excel add-in: convenience built-in



"doki60
wrote in messag
news

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

No longer worked means the worksheet doesn't change the existing date in th
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 o
the revision date worksheet located within the current workbook. It seem
lik
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 workboo
where the date is stored. b2 is the cell where the date is stored. With thi

am able to change the date in that cell and have it update in the header fo
al
worksheets. I test this in the worksheet by changing the date and hittin
prin
preview to see if the date in the header changes.

 

[GS]

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

 

[doki60]

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

try..

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

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

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

HT

Garr

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

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

 

[Jim Cone]

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" <[email protected]>
wrote in message

 

[Don Guillett]

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 yourcode 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" <[email protected]>
wrote in message

I tried a proper sub in the ThisWorkbook module and got the same
result in xl2010.... Bug??
The header is changed when running from a standard macro.......;.

 

[GS]

As Don says, it must be a BUG! I'd put the code in a standard module
and call it from the BeforePrint event...

In ThisWorkbook:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Call SetRevisionDate
End Sub

In a standard module:

Sub SetRevisionDate()
ActiveSheet.PageSetup.RightHeader = Range("RevisionDate").Value
End Sub

Actually, I've gotten into the habit of calling procs from events
rather than running the code within the event for similar issues that
don't get raised when the code lies elsewhere.

 

[doki60]

Jim Cone wrote on 11/02/2011 12:34 ET

Garry's sub should work for you..

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

Take the line..
ActiveSheet.PageSetup.RightHeader
Sheets("revisiondate").Range("B2").Valu
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 you cod
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 Con
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 messag
news

Thanks for clarifying. It is in the ThisWorkbook (Code) module.

 

[CellShocked]

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.

 

[CellShocked]

Is it that maybe he is not seeing the 'OK to use VB' prompt at the top
of the page when he opens the workbook?