date

[daolb]

If have three columns. One which reflect the status of the record.
OK/NOK.
Every time I change the status, excel has to adapt the date and hour in
respectively column 2 and 3.
which formula do I have to use?
note that only the date of the changed record may be adapted, and not
date of the other records.

thanks in advance.
david

 

[Bob Phillips]

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A:A")) Is Nothing Then
With Target
.Offset(0, 1).Value = Format(Date, "dd mmm yyyy")
.Offset(0, 2).Value = Format(Time, "h")
End With
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.

 

[daolb]

thanks bob, it works fine

 

[daolb]

In addition to the previous question I would also like to calculate an
show (in colomn 4) the number of times the value of the column statu
is changed

 

[mangesh_yadav]

A line added for the 4th column:

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A:A")) Is Nothing Then
With Target
..Offset(0, 1).Value = Format(Date, "dd mmm yyyy")
..Offset(0, 2).Value = Format(Time, "h")
..Offset(0, 3).Value = .Offset(0, 3).Value + 1 ' this is new line
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub


Mangesh

 

[daolb]

mangesh,

Thanks for your help. How is the weather in india?

I've a problem. allthough I didn't changed the VB code, the
functionality doesn't work anymore. Date and time aren't filled in
automatically.
Can you give a hint, what the problem could be?

ps; if you ever visit belgium, then I will give you some toeristic
tips!!

 

[mangesh_yadav]

Hi David,

The monsoons have arrived with a bang, and thanks for that lovely
suggestion about the Belgium trip.

As for your case, the macro will be triggered only for changes in
column A. And secondly, you need to enable the macros while starting
the workbook (provided the security is medium). If the security is low,
then the macros will start automatically, and if high, the macros will
be disabled automatically.

Another reason why the macro might not work is probably because the
macro given by Bob did not complete its run (probably due to exiting
pre-maturely) and hence the EnableEvents was not turned back to true.


Mangesh

 

[daolb]

one additional function I need. In the first question I spoke about
status column. And every time I would change the status, the date an
time had to be written down in column 2 and 3.

One problem occur. Its not just one column but ranges in one column.

for example

A1:A10
A20:A30
A40:A50

when I change the value of cell A11, no date or time may me filled i
in column 2 and 3.

greetz, davi

 

[mangesh_yadav]

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A:A")) Is Nothing Then
if ((target.row >=1 and target.row<=10) or _
(target.row >=20 and target.row<=30) or _
(target.row >=40 and target.row<=50) ) then
With Target
.Offset(0, 1).Value = Format(Date, "dd mmm yyyy")
.Offset(0, 2).Value = Format(Time, "h")
.Offset(0, 3).Value = .Offset(0, 3).Value + 1 ' this is new line
End With
End If
End if
ws_exit:
Application.EnableEvents = True
End Su