I have a sheet that has some data validation set up so that you can'
enter the same thing twice withing a certain range. I did notice tha
you can get around this by typing in what you want, select the cell
then grab the lower-right corner of the highlighted selection and dra
which then creates multiple entries of the same thing. I want to avoi
that, but I can't seem to get something that looks "pleasing". Wha
I've managed to scrap together is shown in the code below. (I'm no
too fond of the UNDO part).

Here's the code that I have...

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim TargetParts() As String
Dim i As Integer

On Error GoTo ErrMsg
If Target.Value = "" Then
TargetParts() = Split(Target.Address, "$")
If Left(Target.Address, 2) = "$A" Then
Range("B" & TargetParts(2)).Select
ActiveCell.FormulaR1C1 = ""
Range("C" & TargetParts(2)).Select
ActiveCell.FormulaR1C1 = ""
Range("A" & TargetParts(2) + 1).Select
End If
End If

If Target.Value < "" Then
TargetParts() = Split(Target.Address, "$")
If Left(Target.Address, 2) = "$A" Then
Range("B" & TargetParts(2)).Select
ActiveCell.FormulaR1C1 = UCase$(Application.UserName)
Range("C" & TargetParts(2)).Select
ActiveCell.FormulaR1C1 = Format(Now, "MM/DD/YY hh:mm:s
AMPM")
Range("A" & TargetParts(2) + 1).Select
End If
End If
Exit Sub

ErrMsg:
If Err.Number = "13" Then
Application.Undo
Exit Sub
End If
End Sub

Thanks in advance!

~ Matt

--
Message posted from http://www.ExcelForum.com