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

[DaveC]

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

 

[DaveC]

Shane,

I am somewhat new at this, can you provide a little more explantion? I tried
to enter the string below into the page setup, custom header field with no
success. I'm sure I am doing something wrong. Can you simplfy? When I get the
the header set-up box what should I enter? Thanks for your help it is really
appreceated.

 

[DaveC]

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]

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 Insert>Module. 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 Tool>Macro>Macros.

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


Gord

 

[DaveC]

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]

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

 

[DaveC]

Gord,

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

Thanks

 

[DaveC]

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

 

[Gord Dibben]

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

 

[DaveC]

Gord,

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

DaveC