Tags: current, display, excel, header, headers, microsoft, msdn, software, vba

VBA Headers

On Microsoft » Microsoft Excel

24,115 words with 7 Comments; publish: Fri, 23 May 2008 19:56:00 GMT; (306125.00, « »)

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.

All Comments

Leave a comment...

  • 7 Comments
    • 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.

      >--Original Message--

      >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.

      >

      >.

      >

      #1; Fri, 23 May 2008 19:58:00 GMT
    • 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 Jacobson" <anonymous.excel.todaysummary.com.discussions.microsoft.com> wrote in message

      news:093f01c3bf4d$486bd7c0$a001280a.excel.todaysummary.com.phx.gbl...

      > 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.

      > >--Original Message--

      > >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.

      > >

      > >

      > >.

      > >

      #2; Fri, 23 May 2008 19:59:00 GMT
    • 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 **

      "Wercs" <learn2live.excel.todaysummary.com.THISblueyonder.co.uk> wrote in message

      news:vXJBb.1900$Xa1.22843563.excel.todaysummary.com.news-text.cableinet.net...

      > 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 Jacobson" <anonymous.excel.todaysummary.com.discussions.microsoft.com> wrote in message

      > news:093f01c3bf4d$486bd7c0$a001280a.excel.todaysummary.com.phx.gbl...

      > > 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.

      > >

      > > >--Original Message--

      > > >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.

      > > >

      > > >

      > > >.

      > > >

      >

      #3; Fri, 23 May 2008 20:00:00 GMT
    • 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.

      --

      Wercs.

      "Paul B" <pbridgesnews.excel.todaysummary.com.uga.edu> wrote in message

      news:eORrZm1vDHA.1788.excel.todaysummary.com.tk2msftngp13.phx.gbl...

      > 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 **

      > "Wercs" <learn2live.excel.todaysummary.com.THISblueyonder.co.uk> wrote in message

      > news:vXJBb.1900$Xa1.22843563.excel.todaysummary.com.news-text.cableinet.net...

      > > 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 Jacobson" <anonymous.excel.todaysummary.com.discussions.microsoft.com> wrote in message

      > > news:093f01c3bf4d$486bd7c0$a001280a.excel.todaysummary.com.phx.gbl...

      > > > 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.

      > > >

      > > > >--Original Message--

      > > > >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.

      > > > >

      > > > >

      > > > >.

      > > > >

      > >

      > >

      >

      #4; Fri, 23 May 2008 20:01:00 GMT
    • 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 **

      "Wercs" <learn2live.excel.todaysummary.com.THISblueyonder.co.uk> wrote in message

      news:_VLBb.2048$dK2.24525962.excel.todaysummary.com.news-text.cableinet.net...

      > 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.

      > --

      > Wercs.

      >

      > "Paul B" <pbridgesnews.excel.todaysummary.com.uga.edu> wrote in message

      > news:eORrZm1vDHA.1788.excel.todaysummary.com.tk2msftngp13.phx.gbl...

      > > 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 **

      > >

      > > "Wercs" <learn2live.excel.todaysummary.com.THISblueyonder.co.uk> wrote in message

      > > news:vXJBb.1900$Xa1.22843563.excel.todaysummary.com.news-text.cableinet.net...

      > > > 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 Jacobson" <anonymous.excel.todaysummary.com.discussions.microsoft.com> wrote in message

      > > > news:093f01c3bf4d$486bd7c0$a001280a.excel.todaysummary.com.phx.gbl...

      > > > > 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.

      > > > >

      > > > > >--Original Message--

      > > > > >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.

      > > > > >

      > > > > >

      > > > > >.

      > > > > >

      > > >

      > > >

      > >

      > >

      >

      #5; Fri, 23 May 2008 20:02:00 GMT
    • 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.

      "Paul B" <pbridgesnews.excel.todaysummary.com.uga.edu> wrote in message

      news:ucM0YL2vDHA.2372.excel.todaysummary.com.TK2MSFTNGP09.phx.gbl...

      > 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 **

      >

      > "Wercs" <learn2live.excel.todaysummary.com.THISblueyonder.co.uk> wrote in message

      > news:_VLBb.2048$dK2.24525962.excel.todaysummary.com.news-text.cableinet.net...

      > > 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.

      > > --

      > > Wercs.

      > >

      > >

      > > "Paul B" <pbridgesnews.excel.todaysummary.com.uga.edu> wrote in message

      > > news:eORrZm1vDHA.1788.excel.todaysummary.com.tk2msftngp13.phx.gbl...

      > > > 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 **

      > > >

      > > > "Wercs" <learn2live.excel.todaysummary.com.THISblueyonder.co.uk> wrote in message

      > > > news:vXJBb.1900$Xa1.22843563.excel.todaysummary.com.news-text.cableinet.net...

      > > > > 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 Jacobson" <anonymous.excel.todaysummary.com.discussions.microsoft.com> wrote in

      message

      > > > > news:093f01c3bf4d$486bd7c0$a001280a.excel.todaysummary.com.phx.gbl...

      > > > > > 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.

      > > > > >

      > > > > > >--Original Message--

      > > > > > >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.

      > > > > > >

      > > > > > >

      > > > > > >.

      > > > > > >

      > > > >

      > > > >

      > > >

      > > >

      > >

      > >

      >

      #6; Fri, 23 May 2008 20:03:00 GMT
    • Your welcome, thanks for the feedback

      Paul

      "Wercs" <learn2live.excel.todaysummary.com.THISblueyonder.co.uk> wrote in message

      news:nINBb.2258$%s3.25302087.excel.todaysummary.com.news-text.cableinet.net...

      > 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.

      >

      > "Paul B" <pbridgesnews.excel.todaysummary.com.uga.edu> wrote in message

      > news:ucM0YL2vDHA.2372.excel.todaysummary.com.TK2MSFTNGP09.phx.gbl...

      > > 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 **

      > >

      > >

      > >

      > > "Wercs" <learn2live.excel.todaysummary.com.THISblueyonder.co.uk> wrote in message

      > > news:_VLBb.2048$dK2.24525962.excel.todaysummary.com.news-text.cableinet.net...

      > > > 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.

      > > > --

      > > > Wercs.

      > > >

      > > >

      > > > "Paul B" <pbridgesnews.excel.todaysummary.com.uga.edu> wrote in message

      > > > news:eORrZm1vDHA.1788.excel.todaysummary.com.tk2msftngp13.phx.gbl...

      > > > > 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 **

      > > > >

      > > > > "Wercs" <learn2live.excel.todaysummary.com.THISblueyonder.co.uk> wrote in message

      > > > > news:vXJBb.1900$Xa1.22843563.excel.todaysummary.com.news-text.cableinet.net...

      > > > > > 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 Jacobson" <anonymous.excel.todaysummary.com.discussions.microsoft.com> wrote in

      > message

      > > > > > news:093f01c3bf4d$486bd7c0$a001280a.excel.todaysummary.com.phx.gbl...

      > > > > > > 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.

      > > > > > >

      > > > > > > >--Original Message--

      > > > > > > >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.

      > > > > > > >

      > > > > > > >

      > > > > > > >.

      > > > > > > >

      > > > > >

      > > > > >

      > > > >

      > > > >

      > > >

      > > >

      > >

      > >

      >

      #7; Fri, 23 May 2008 20:04:00 GMT