Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    San Jose, California, USA
    Posts
    290
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Formatting (2002 SP1)-MULTI-Formatting (2002 SP1)-MULTI-Fo

    I have two problems. First, I want to format cells to display a date in the format mm/yy, i.e., just month and year, with the month showing a leading zero. I created a custom format, as the built-in one does not have the leading zero. The problem is that I have to enter the full date for any year other than the current year. If I enter 12/01, Excel interprets that as 12/1/02, and displays 12/02. Is there a way around this?

    The other problem is that I want to put a border round a block of cells. If the block goes onto more than one page, the border does not appear at the bottom of the first page. It appears at the top of the second page if I set the print options to repeat the row with the border at the top of each page. Is there a way to get the border at the bottom of each page?

  2. #2
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Formatting (2002 SP1)-MULTI-Formatting (2002 SP1)-MULTI-Fo

    Hi Andrew,
    Regarding your first problem, have a look at Post 150567, for links to a recent discussion about the same issue.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formatting (2002 SP1)-MULTI-Formatting (2002 SP1)-MULTI-Fo

    You could put the code below in the Worksheet change event routine. This code expects the month and year to be entered into column A:A

    <pre>Private Sub Worksheet_Change(ByVal Target As Range)
    Dim oCell As Range
    If Intersect(Target, ActiveSheet.Range("A:A")) Is Nothing Then Exit Sub
    Application.EnableEvents = False
    For Each oCell In Intersect(Target, ActiveSheet.Range("A:A"))
    If IsDate(oCell.Value) Then
    oCell.Value = DateSerial(Day(oCell.Value), Month(oCell.Value), 1)
    End If
    Next oCell
    Application.EnableEvents = True
    End Sub
    </pre>


    If you use this code, you will not be able to enter a full date in the range you specify, only month and year.

    The only way to get the border at the bottom of each page that I know of is to put a border on the row that that will print at the bottom of each page.
    Legare Coleman

Posting Permissions

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