VBA Headers

  • Thread starter Thread starter Wercs
  • Start date Start date
W

Wercs

Hi again,

I'm still trying to get an Excel 2000 Header to display only the current
Year.

I understand that this can be done using VBA, but I've no idea how, apart
from the fact that it reads the current year (in this case) from a cell in
the worksheet.

Any help appreciated.

TIA.
 
A very simple way to do this without VBA would be to name
the sheet with the current year, then use the 'tab' button
in the Header design window.
 
Yes Jeff, I had looked at that idea but, as I require the Year header on all
sheets within the workbook & the Year should change with the current date,
that isn't really what I need.

Tnx anyway.
 
Jeff, try this,

Sub test()
Dim sht As Worksheet
For Each sht In ThisWorkbook.Sheets
sht.PageSetup.CenterHeader = Format(Now(), "yyyy")
Next sht
End Sub

--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 97 & 2000
** remove news from my email address to reply by email **
 
Hi Paul,

Forgive me but I'm not so hot with VBA, well sub-zero actually!

I assume this has to be entered in the VB editor (Alt+F11)? Where/how do I
go about that?

Also, could it be made to insert the "Year" into the Left section of the
Header?

TIA.
 
try this, To put in this macro, from your workbook right-click the
workbook's icon and pick View Code. This icon is to the left of the "File"
menu this will open the VBA editor, in the left hand window click on your
workbook name, go to insert, module, and paste the code in the window that
opens on the right hand side, press Alt and Q to close this window and go
back to your workbook and press alt and F8, this will bring up a box to
pick the Macro from, click on the Macro name to run it. If you are using
excel 2000 or newer you may have to change the macro security settings to
get the macro to run.

Sub test()
Dim sht As Worksheet
For Each sht In ThisWorkbook.Sheets
sht.PageSetup.LeftHeader = Format(Now(), "yyyy")
Next sht
End Sub

You could also run it before the sheet prints like this, To put in this
macro, from your workbook right-click the workbook's icon and pick View
Code. This icon is to the left of the "File" menu this will open the VBA
editor, in the left hand window double click on thisworkbook, under your
workbook name, and paste the code in the window that opens on the right hand
side, press Alt and Q to close this window and go back to your workbook, now
this will run every time you print the workbook. If you are using excel
2000 or newer you may have to change the macro security settings to get the
macro to run.

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim sht As Worksheet
For Each sht In ThisWorkbook.Sheets
sht.PageSetup.LeftHeader = Format(Now(), "yyyy")
Next sht
End Sub

--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 97 & 2000
** remove news from my email address to reply by email **
 
Thanks Paul. I've gone for the 2nd option & it seems to be just what's
required.

BTW, no changes were required to security settings.

Much obliged.
 
Your welcome, thanks for the feedback
Paul
Wercs said:
Thanks Paul. I've gone for the 2nd option & it seems to be just what's
required.

BTW, no changes were required to security settings.

Much obliged.
--
Wercs.


from
Click to expand...
 
Back
Top