Results 1 to 7 of 7
  1. #1
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post

    Date format in Header

    Hi Loungers,

    I'm using the code below to put a date into the header which is working, however the date format in Y2 is "Dec 2013" but when the date is inserted in the header using the code below is comes across as 12/1/2013. Any suggestions on how to amend the code to change the date format also need to make the date bold, change the font type and increase the font size?


    Sub UpdateHeader()
    ActiveSheet.PageSetup.RightHeader = Range("y2").Value
    End Sub


    Any suggestions would very much appreciated.

    Regards

  2. #2
    New Lounger
    Join Date
    Dec 2013
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi verada,
    This will work for you - I have tested this in the left footer along with yours in the right header to see the difference:

    Add a format statement to your range - below I have told it to just do month and year but you can follow the standard excel formatting for date ranges if your want to change this.

    Code:
    ActiveSheet.PageSetup.LeftFooter = Format(Range("y2").Value, "mm/yyyy")
    Last edited by bugmonsta2; 2013-12-19 at 05:34. Reason: I was using a2 so changed this to match the original post to y2

  3. #3
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Code:
    Sub UpdateHeader()
    ActiveSheet.PageSetup.RightHeader = "&""Arial,Bold""&12" & Range("y2").Text
    End Sub
    for example, for 12 point bold Arial.
    Regards,
    Rory

    Microsoft MVP - Excel

  4. #4
    New Lounger
    Join Date
    Dec 2013
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by rory View Post
    Code:
    Sub UpdateHeader()
    ActiveSheet.PageSetup.RightHeader = "&""Arial,Bold""&12" & Range("y2").Text
    End Sub
    for example, for 12 point bold Arial.
    Hi rory,

    with your example my font would increase to some huge size, so I have used your formula and added a space as I think excel was including the first part of the date as the font size.

    On my test sheet in my footer it now looks like this for size 16, Arial, bold:

    Code:
    ActiveSheet.PageSetup.LeftFooter = "&""Arial,Bold""&16" & " " & Format(Range("y2").Value, "mm/yyyy")

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Yes it would - because you're using a month number, not name. You can include the space in the initial string directly:
    Code:
    Sub UpdateHeader()
    ActiveSheet.PageSetup.RightHeader = "&""Arial,Bold""&12 " & Range("y2").Text
    End Sub
    Regards,
    Rory

    Microsoft MVP - Excel

  6. #6
    New Lounger
    Join Date
    Dec 2013
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Ah yes - thats a better solution. I have added it to my help document to remind me in the future that I can do it that way.

  7. #7
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post
    Thanks to all for your help - problem solved
    Best wishes to all for the festive season

    Regards

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •