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

Topic

New Reply

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?

Reply | Quote

Replies

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
[Fmr MS MVP - Word]

Reply | Quote

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

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

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.

Reply | Quote