Code needs tweaking :P

[Guest]

Hi
The following code will automatically insert username, date and time when
data is put into 'B' somewhere:

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Me.Range("B4:B50")) Is Nothing Then
With Target
If .Value <> "" Then
.Offset(0, 8).Value = (UserName)
.Offset(0, 10).Value = Format(Now, "hh:mm:ss")
.Offset(0, 9).Value = Format(Date, "dd/mmm")
End If
End With
End If
End Sub

However, when data is removed from 'B', a debug window appears and the
username etc isn't removed.
If poss, i'd like two versions of this code, the first:
if data is deleted from 'B' username etc is removed too, and
if data is deleted from 'B' username etc remains and is not overwritten

tia,

 

[Bob Phillips]

It does debug for me, it works fine, although it doesn't add a username
because the code is incomplete.

This clears it if B blanked

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False

If Not Intersect(Target, Me.Range("B4:B50")) Is Nothing Then
With Target
If .Value <> "" Then
.Offset(0, 8).Value = Environ("UserName")
.Offset(0, 10).Value = Format(Now, "hh:mm:ss")
.Offset(0, 9).Value = Format(Date, "dd/mmm")
Else
.Offset(0, 8).Value = ""
.Offset(0, 10).Value = ""
.Offset(0, 9).Value = ""
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub




--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

Great, thanks Bob.
--
Traa Dy Liooar

Jock

It does debug for me, it works fine, although it doesn't add a username
because the code is incomplete.

This clears it if B blanked

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False

If Not Intersect(Target, Me.Range("B4:B50")) Is Nothing Then
With Target
If .Value <> "" Then
.Offset(0, 8).Value = Environ("UserName")
.Offset(0, 10).Value = Format(Now, "hh:mm:ss")
.Offset(0, 9).Value = Format(Date, "dd/mmm")
Else
.Offset(0, 8).Value = ""
.Offset(0, 10).Value = ""
.Offset(0, 9).Value = ""
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub




--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)