I have an Excel spread sheet created by another user. The spread sheet
contains a custom header that reflects the name of the employee. I need to be
able to change the name for each employee and print the document. I can
change by going into the custom header but, I would like to be able to change
from the open spread sheet. I would like to be able to view the header on the
sheet. Can this be done?

Duggan

the most common way to do it is with a sub like

Private Sub Workbook_BeforePrint(Cancel As Boolean)

Sheets("Sheet_name").PageSetup.CenterHeader =
Sheets("Sheet_name").cells(1,1).Value

End Sub

in the workbook module
put in the appropriate sheet name and cell designator

if you had a list of names, it would be pretty easy to just pull from the
list to put into the header for each print


"Duggan 59" wrote:

I have an Excel spread sheet created by another user. The spread sheet
contains a custom header that reflects the name of the employee. I need to be
able to change the name for each employee and print the document. I can
change by going into the custom header but, I would like to be able to change
from the open spread sheet. I would like to be able to view the header on the
sheet. Can this be done?

Duggan

Wow! That is WAY over my head! How would use this?

Duggan

"bj" wrote:

the most common way to do it is with a sub like

Private Sub Workbook_BeforePrint(Cancel As Boolean)

Sheets("Sheet_name").PageSetup.CenterHeader =
Sheets("Sheet_name").cells(1,1).Value

End Sub

in the workbook module
put in the appropriate sheet name and cell designator

if you had a list of names, it would be pretty easy to just pull from the
list to put into the header for each print


"Duggan 59" wrote:

I have an Excel spread sheet created by another user. The spread sheet
contains a custom header that reflects the name of the employee. I need to be
able to change the name for each employee and print the document. I can
change by going into the custom header but, I would like to be able to change
from the open spread sheet. I would like to be able to view the header on the
sheet. Can this be done?

Duggan

go to the sheet you want it to work in and right click on the tab
selecy view code
paste the sub in the module

again make sure the Sheet_name is the name you have for the sheet
and the cell reference is the cell in which you will have the name.


"Duggan 59" wrote:

Wow! That is WAY over my head! How would use this?

Duggan

"bj" wrote:

the most common way to do it is with a sub like

Private Sub Workbook_BeforePrint(Cancel As Boolean)

Sheets("Sheet_name").PageSetup.CenterHeader =
Sheets("Sheet_name").cells(1,1).Value

End Sub

in the workbook module
put in the appropriate sheet name and cell designator

if you had a list of names, it would be pretty easy to just pull from the
list to put into the header for each print


"Duggan 59" wrote:

I have an Excel spread sheet created by another user. The spread sheet
contains a custom header that reflects the name of the employee. I need to be
able to change the name for each employee and print the document. I can
change by going into the custom header but, I would like to be able to change
from the open spread sheet. I would like to be able to view the header on the
sheet. Can this be done?

Duggan

the cell designator is row, column
for example, D2 is Cells(2,4)
if you would rathe say the normal cell designation change the Cells(1,1)
to range("A1")
do not forget the quote marks.

with this in the module when you go to print it will automatically change
the header for that sheet before it sends it to the printer.

"Duggan 59" wrote:

Wow! That is WAY over my head! How would use this?

Duggan

"bj" wrote:

the most common way to do it is with a sub like

Private Sub Workbook_BeforePrint(Cancel As Boolean)

Sheets("Sheet_name").PageSetup.CenterHeader =
Sheets("Sheet_name").cells(1,1).Value

End Sub

in the workbook module
put in the appropriate sheet name and cell designator

if you had a list of names, it would be pretty easy to just pull from the
list to put into the header for each print


"Duggan 59" wrote:

I have an Excel spread sheet created by another user. The spread sheet
contains a custom header that reflects the name of the employee. I need to be
able to change the name for each employee and print the document. I can
change by going into the custom header but, I would like to be able to change
from the open spread sheet. I would like to be able to view the header on the
sheet. Can this be done?

Duggan

bj and Duggan

BeforePrint is workbook code and will not run from a sheet module.

Must go into Thisworkbook module.

Right-click on the Excel Icon left of "File" on the main menu in Excel or on the
Excel Icon at left side of Title bar if not maximized.

Select "View Code" from the dropdown.

A module will open.

Paste the code into that module. Save and close then re-open.


Gord Dibben MS Excel MVP

On Thu, 12 Jul 2007 14:22:03 -0700, bj wrote:

go to the sheet you want it to work in and right click on the tab
selecy view code
paste the sub in the module

again make sure the Sheet_name is the name you have for the sheet
and the cell reference is the cell in which you will have the name.

BJ and Gord,

Thank You. I was able to get this to work!!

Duggan

"bj" wrote:

the cell designator is row, column
for example, D2 is Cells(2,4)
if you would rathe say the normal cell designation change the Cells(1,1)
to range("A1")
do not forget the quote marks.

with this in the module when you go to print it will automatically change
the header for that sheet before it sends it to the printer.

"Duggan 59" wrote:

Wow! That is WAY over my head! How would use this?

Duggan

"bj" wrote:

the most common way to do it is with a sub like

Private Sub Workbook_BeforePrint(Cancel As Boolean)

Sheets("Sheet_name").PageSetup.CenterHeader =
Sheets("Sheet_name").cells(1,1).Value

End Sub

in the workbook module
put in the appropriate sheet name and cell designator

if you had a list of names, it would be pretty easy to just pull from the
list to put into the header for each print


"Duggan 59" wrote:

I have an Excel spread sheet created by another user. The spread sheet
contains a custom header that reflects the name of the employee. I need to be
able to change the name for each employee and print the document. I can
change by going into the custom header but, I would like to be able to change
from the open spread sheet. I would like to be able to view the header on the
sheet. Can this be done?

Duggan

thanks for catching that. It is something I have known and often forget.

"Gord Dibben" wrote:

bj and Duggan

BeforePrint is workbook code and will not run from a sheet module.

Must go into Thisworkbook module.

Right-click on the Excel Icon left of "File" on the main menu in Excel or on the
Excel Icon at left side of Title bar if not maximized.

Select "View Code" from the dropdown.

A module will open.

Paste the code into that module. Save and close then re-open.


Gord Dibben MS Excel MVP

On Thu, 12 Jul 2007 14:22:03 -0700, bj wrote:

go to the sheet you want it to work in and right click on the tab
selecy view code
paste the sub in the module

again make sure the Sheet_name is the name you have for the sheet
and the cell reference is the cell in which you will have the name.