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,
--
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)

"Jock" <(E-Mail Removed)> wrote in message
news:50628E88-E7BE-4142-9B8D-(E-Mail Removed)...
> 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,
> --
> Traa Dy Liooar
>
> Jock

Great, thanks Bob.
--
Traa Dy Liooar

Jock


"Bob Phillips" wrote:

> 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)
>
> "Jock" <(E-Mail Removed)> wrote in message
> news:50628E88-E7BE-4142-9B8D-(E-Mail Removed)...
> > 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,
> > --
> > Traa Dy Liooar
> >
> > Jock
>
>
>