View Single Post
  #2 (permalink)  
Old August 5th, 2007, 07:02 PM
FullSquat FullSquat is offline
Registered User
Join Date: Jul 2007
Location: , , .
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts

In the worksheet‘s column to be used for the dates highlight the rows to be used for the dates. Create a range name for the area. Then create the custom date format dd-mmm-yy for the range.
In the worksheet’s SelectionChange event use the following code:

    Dim rcell As Range
    Dim dateRange As Range
    Dim msg As String
    Set dateRange = Range("TheDates")
    Set rcell = ActiveCell

    msg = "Row contents not a date" & vbCrLf & "or the row is empty"

    If Union(dateRange, rcell).Address = dateRange.Address And _
    Union(Range("A6"), rcell).Address <> Range("A6").Address Then

        If Not IsDate(rcell.Offset(-1, 0)) Or IsEmpty(rcell.Offset(-1, 0)) Then
            MsgBox msg, vbCritical, "Enter a Date"
            rcell.Offset(-1, 0).Select
        End If

  End If

A6 is the first cell of the date range.
If the person does not enter a date or the cell is empty, when the person moves to the next row a message dialog box will appear. It’s not perfect but it should work in most cases.