I need to timestamp a cell when the value of another cell changes from blank
to non-blank.
I need to timestamp a cell when the value of another cell changes from blank
to non-blank.
You need VBA else the time will chnage frequently
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ws_exit:
Application.EnableEvents = False
If Target.Address = "$A$1" Then
If Target.Value = "" Then
Range("B1").Value <> Format(Time, "hh:mm:ss")
End If
End If
ws_exit:
Application.EnableEvents = True
End Sub
'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.
--
HTH
Bob Phillips
"billservit" <[email protected]> wrote in message
news:[email protected]...
> I need to timestamp a cell when the value of another cell changes from
blank
> to non-blank.
If you wanted to time, date, and user stamp every change made in the first
300 rows you can do something like this. The stamps for A1:Z300 would
appear in AA1:AZ300 off creen to the right or you could hide that area so
only you would know it's there. Just change the offset if you want it
somewhere else. If the currently active Offset and Application.Username
lines don't produce the name you expect, comment them out and activate the
offset and Environ("username") lines by changing the apostrophes at the
beginning of those two lines. Paste this into the worksheet by right mouse
clicking the worksheet tab and selecting View Code.
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A1:Z300")) Is Nothing Then
With Target
.Offset(0, 26).Value = Format(Now, "dd mmm yyyy hh:mm ") _
& Application.UserName
'.Offset(0, 26).Value = Format(Now, "dd mmm yyyy hh:mm ") _
'& Environ("username")
End With
End If
ws_exit:
Application.EnableEvents = True
End Sub
"billservit" wrote:
> I need to timestamp a cell when the value of another cell changes from blank
> to non-blank.
I think Bob meant line 5 and 6 to be:
If Target.Value <> "" Then
Range("B1").Value = Format(Time, "hh:mm:ss")
End If
HTH
"Bob Phillips" <[email protected]> wrote in message
news:[email protected]...
> You need VBA else the time will chnage frequently
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> > On Error GoTo ws_exit:
> Application.EnableEvents = False
> If Target.Address = "$A$1" Then
> If Target.Value = "" Then
> Range("B1").Value <> Format(Time, "hh:mm:ss")
> End If
> End If
>
> ws_exit:
> Application.EnableEvents = True
> End Sub
>
> 'This is worksheet event code, which means that it needs to be
> 'placed in the appropriate worksheet code module, not a standard
> 'code module. To do this, right-click on the sheet tab, select
> 'the View Code option from the menu, and paste the code in.
>
>
>
> --
> HTH
>
> Bob Phillips
>
> "billservit" <[email protected]> wrote in message
> news:[email protected]...
> > I need to timestamp a cell when the value of another cell changes from
> blank
> > to non-blank.
>
>
and I thought I tested that :-(
Bob
"Jim May" <[email protected]> wrote in message
news:pgVee.1323$It1.1230@lakeread02...
> I think Bob meant line 5 and 6 to be:
>
> If Target.Value <> "" Then
> Range("B1").Value = Format(Time, "hh:mm:ss")
> End If
>
> HTH
>
> "Bob Phillips" <[email protected]> wrote in message
> news:[email protected]...
> > You need VBA else the time will chnage frequently
> >
> > Private Sub Worksheet_Change(ByVal Target As Range)
> > > On Error GoTo ws_exit:
> > Application.EnableEvents = False
> > If Target.Address = "$A$1" Then
> > If Target.Value = "" Then
> > Range("B1").Value <> Format(Time, "hh:mm:ss")
> > End If
> > End If
> >
> > ws_exit:
> > Application.EnableEvents = True
> > End Sub
> >
> > 'This is worksheet event code, which means that it needs to be
> > 'placed in the appropriate worksheet code module, not a standard
> > 'code module. To do this, right-click on the sheet tab, select
> > 'the View Code option from the menu, and paste the code in.
> >
> >
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > "billservit" <[email protected]> wrote in message
> > news:[email protected]...
> > > I need to timestamp a cell when the value of another cell changes from
> > blank
> > > to non-blank.
> >
> >
>
>
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks