If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
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. |
#12
|
|||
|
|||
can I use a cell content to auto-populate to a header in excel
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. |
|
Thread Tools | |
Display Modes | |
|
|