Error proofing automatic updating a cell

[kls]

I have written a sub using a worksheet_change_event to place the date in
the first column of a row, whenever data is placed in any other cell in
that row. The following works fine:

Private Sub Worksheet_Change(ByVal Target As Range)
ActiveCell.EntireRow.Cells(0, 1).Value =
Worksheets("Client").Range("A2")
End Sub

(A2 is the source date)
The problems is if the user intends to add data to the next empty row,
but instead inadvertently changes data in a cell in a row already
containing data (and a date) thereby changing the date. What approach
is the cleanest?

Thanks,

 

[Norman Jones]

Hi Kls,

Assuming that the entry in the first cell of the row is determined by the
first entry in the row, try:

Private Sub Worksheet_Change(ByVal Target As Range)
If IsEmpty(Cells(Target.Row, 1)) Then
If Not IsEmpty(Target) Then
Cells(Target.Row, 1).Value = _
Worksheets("Client").Range("A2")
End If
End If
End Sub

 

[Tom Ogilvy]

Private Sub Worksheet_Change(ByVal Target As Range)
if Target(1).column <> 1 then
if isemtpy(cells(Target(1).row,1)) then
cells(target(1).row,1).Value = Date
End If
end If
End Sub

 

[kls]

Hi Tom, thanks for all your help and the new approach.

I inserted a new worksheet and copy/pasted your code, (I corrected the
isempty), but the change event failed to insert a date in col 1 of the
row.

I am unfamiliar w/ target as an object and it is not available in help.
I don't know if I've done something wrong there, or if I am missing
something else. I'd like to know how to use this.

If I do use this, does your code prevent a user from mistakenly getting
into a row that has data in it (and a date in col1 of the row) and
messing everything up? If I lock the cells and protect the workbook,
would the user still be able to added data in unused, subsequent rows or
do I need to do some conditional formatting? (I'm new and haven't yet
figured the ins and outs of locking,protecting etc while allowing user
access to "unused rows and columns".

 

[kls]

Tom, having corrected the isempty did fix everything when I went back to
double check after the post. (A new date was not inserted when col1 in
the row already had a date in it). I'm still not sure about using
"Target", but will workout the locking and protections issues.

 

[Tom Ogilvy]

You are correct. I misspelled isempty.