VB Script not working in Excel 2010
 

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

VB Script not working in Excel 2010
 

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

VB Script not working in Excel 2010
 

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.

VB Script not working in Excel 2010
 

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

VB Script not working in Excel 2010
 

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.

VB Script not working in Excel 2010
 

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.

VB Script not working in Excel 2010
 

On Nov 2, 12:34*pm, "Jim Cone" wrote:
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 .. .







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 athttp://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.

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.......;.

VB Script not working in Excel 2010
 

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.

--
Garry

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

VB Script not working in Excel 2010
 

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").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
news:
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.


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

VB Script not working in Excel 2010
 

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

VB Script not working in Excel 2010
 

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?


On Wed, 2 Nov 2011 10:34:10 -0700, "Jim Cone"
wrote:


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.