can I use a cell content to auto-populate to a header in excel?

You have a couple of typos in your code.

Private Sub Workbook_Open()
ActiveSheet.PageSetup.CenterHeader = "&""Arial,Bold""&18" & _
Range("E1").Value
End Sub

This attempted revision you made is not a macro to be run manually and must
be stored in Thisworkbook module, not a standard module or worksheet module.

It is event code which runs when you open the workbook.

Is the Activesheet always the same sheet?

The event code runs on whatever sheet is active when the workbook opens.

Are you sure you want to do this?

Maybe Before_Print event would be better and designate a specific sheet?


Gord


On Fri, 19 Jun 2009 06:40:01 -0700, DaveC
wrote:

Gord,

OK I found the "sub cellinheader22, when I enter as a marco should it look
lkie this?

Private Sub Workbook_Open()
ActiveSheet.PageSetup.CenterHeader = "&""arial,bold""&18& rANGE("e1").VALUE
ActiveSheet.PageSetup.CenterHeader = Range("e1").Value
End Sub

This is how I have it now and it does not chnage the font when I run the
marco. It does populate the header with the cell content from E1, that is
great. Thanks for any help

Dave

"DaveC" wrote:

Gord,

Sorry for my dumbness. What is "sub Cellinheader22"?

Thanks

"Gord Dibben" wrote:

You cannot change and save the default settings for a larger font.

Sub Cellinheader22 gives you a method of changing font type and font size.

Did you not notice that?


Gord

On Thu, 18 Jun 2009 10:56:01 -0700, DaveC
wrote:

Thanks a million, I actually made this work. Feeling good. another question
if I may. Is there a way to change and save the default font settings in the
header and footer? I can manually do this but want it to be automatic in a
larger font when it is populated from a cell referance that I just ran the
macro for. Thanks for any help.

"Gord Dibben" wrote:

You don't type anything into the header.

I provided macros. You choose which one you want to use and run it.

If you're not familiar with VBA and macros, see David McRitchie's site for
more on "getting started".

http://www.mvps.org/dmcritchie/excel/getstarted.htm

or Ron de De Bruin's site on where to store macros.

http://www.rondebruin.nl/code.htm

In the meantime..........

First...create a backup copy of your original workbook.

To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

Hit CRTL + r to open Project Explorer.

Find your workbook/project and select it.

Right-click and InsertModule. Paste the code in there. Save the
workbook and hit ALT + Q to return to your workbook.

Run or edit the macro by going to ToolMacroMacros.

You can also assign this macro to a button or a shortcut key combo.


Gord

On Thu, 18 Jun 2009 06:45:01 -0700, DaveC
wrote:

Gord,

You seem to be the wizard of excel and I appreceate the help. I have tried
to type "ws.Range("A1").Value" exactly into the header box with no success.
This line just appears as the header. Can you tell me what I'm doing wrong or
what exact steps I need to take to make this work. Thanks again.

Dave

"Gord Dibben" wrote:

Sub CellInHeader()
With ActiveSheet
.PageSetup.CenterHeader = .Range("A1").Value
End With
End Sub

Sub CellInHeaderr22()
With ActiveSheet.PageSetup
.LeftHeader = "&""Algerian,Regular""&16" & Range("A1").Value
End With
End Sub

Sub Cell_In_All_Headers()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Sheets
ws.PageSetup.CenterHeader = ws.Range("A1").Value
Next
End Sub


Gord Dibben MS Excel MVP

On Wed, 17 Jun 2009 12:53:01 -0700, DaveC
wrote:

any help on this? I would like to use the contents of a cell to auto-populate
the header. Thanks for any assit.

Gord,

You are the man. I used this and it is working perfect. I really appreceate
your time. Thanks a million.

DaveC

"Gord Dibben" wrote:

You have a couple of typos in your code.

Private Sub Workbook_Open()
ActiveSheet.PageSetup.CenterHeader = "&""Arial,Bold""&18" & _
Range("E1").Value
End Sub

This attempted revision you made is not a macro to be run manually and must
be stored in Thisworkbook module, not a standard module or worksheet module.

It is event code which runs when you open the workbook.

Is the Activesheet always the same sheet?

The event code runs on whatever sheet is active when the workbook opens.

Are you sure you want to do this?

Maybe Before_Print event would be better and designate a specific sheet?


Gord


On Fri, 19 Jun 2009 06:40:01 -0700, DaveC
wrote:

Gord,

OK I found the "sub cellinheader22, when I enter as a marco should it look
lkie this?

Private Sub Workbook_Open()
ActiveSheet.PageSetup.CenterHeader = "&""arial,bold""&18& rANGE("e1").VALUE
ActiveSheet.PageSetup.CenterHeader = Range("e1").Value
End Sub

This is how I have it now and it does not chnage the font when I run the
marco. It does populate the header with the cell content from E1, that is
great. Thanks for any help

Dave

"DaveC" wrote:

Gord,

Sorry for my dumbness. What is "sub Cellinheader22"?

Thanks

"Gord Dibben" wrote:

You cannot change and save the default settings for a larger font.

Sub Cellinheader22 gives you a method of changing font type and font size.

Did you not notice that?


Gord

On Thu, 18 Jun 2009 10:56:01 -0700, DaveC
wrote:

Thanks a million, I actually made this work. Feeling good. another question
if I may. Is there a way to change and save the default font settings in the
header and footer? I can manually do this but want it to be automatic in a
larger font when it is populated from a cell referance that I just ran the
macro for. Thanks for any help.

"Gord Dibben" wrote:

You don't type anything into the header.

I provided macros. You choose which one you want to use and run it.

If you're not familiar with VBA and macros, see David McRitchie's site for
more on "getting started".

http://www.mvps.org/dmcritchie/excel/getstarted.htm

or Ron de De Bruin's site on where to store macros.

http://www.rondebruin.nl/code.htm

In the meantime..........

First...create a backup copy of your original workbook.

To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

Hit CRTL + r to open Project Explorer.

Find your workbook/project and select it.

Right-click and InsertModule. Paste the code in there. Save the
workbook and hit ALT + Q to return to your workbook.

Run or edit the macro by going to ToolMacroMacros.

You can also assign this macro to a button or a shortcut key combo.


Gord

On Thu, 18 Jun 2009 06:45:01 -0700, DaveC
wrote:

Gord,

You seem to be the wizard of excel and I appreceate the help. I have tried
to type "ws.Range("A1").Value" exactly into the header box with no success.
This line just appears as the header. Can you tell me what I'm doing wrong or
what exact steps I need to take to make this work. Thanks again.

Dave

"Gord Dibben" wrote:

Sub CellInHeader()
With ActiveSheet
.PageSetup.CenterHeader = .Range("A1").Value
End With
End Sub

Sub CellInHeaderr22()
With ActiveSheet.PageSetup
.LeftHeader = "&""Algerian,Regular""&16" & Range("A1").Value
End With
End Sub

Sub Cell_In_All_Headers()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Sheets
ws.PageSetup.CenterHeader = ws.Range("A1").Value
Next
End Sub


Gord Dibben MS Excel MVP

On Wed, 17 Jun 2009 12:53:01 -0700, DaveC
wrote:

any help on this? I would like to use the contents of a cell to auto-populate
the header. Thanks for any assit.