date
 

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


--
daolb
------------------------------------------------------------------------
daolb's Profile: http://www.excelforum.com/member.php...o&userid=24478
View this thread: http://www.excelforum.com/showthread...hreadid=380794

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.

--
HTH

Bob Phillips

"daolb" wrote in
message ...

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


--
daolb
------------------------------------------------------------------------
daolb's Profile:
http://www.excelforum.com/member.php...o&userid=24478
View this thread: http://www.excelforum.com/showthread...hreadid=380794

thanks bob, it works fine.


--
daolb
------------------------------------------------------------------------
daolb's Profile: http://www.excelforum.com/member.php...o&userid=24478
View this thread: http://www.excelforum.com/showthread...hreadid=380794

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


--
daolb
------------------------------------------------------------------------
daolb's Profile: http://www.excelforum.com/member.php...o&userid=24478
View this thread: http://www.excelforum.com/showthread...hreadid=380794

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


--
mangesh_yadav
------------------------------------------------------------------------
mangesh_yadav's Profile: http://www.excelforum.com/member.php...o&userid=10470
View this thread: http://www.excelforum.com/showthread...hreadid=380794

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!!


--
daolb
------------------------------------------------------------------------
daolb's Profile: http://www.excelforum.com/member.php...o&userid=24478
View this thread: http://www.excelforum.com/showthread...hreadid=380794

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


--
mangesh_yadav
------------------------------------------------------------------------
mangesh_yadav's Profile: http://www.excelforum.com/member.php...o&userid=10470
View this thread: http://www.excelforum.com/showthread...hreadid=380794

one additional function I need. In the first question I spoke about a
status column. And every time I would change the status, the date and
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 in
in column 2 and 3.

greetz, david


--
daolb
------------------------------------------------------------------------
daolb's Profile: http://www.excelforum.com/member.php...o&userid=24478
View this thread: http://www.excelforum.com/showthread...hreadid=380794

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 Sub


--
mangesh_yadav
------------------------------------------------------------------------
mangesh_yadav's Profile: http://www.excelforum.com/member.php...o&userid=10470
View this thread: http://www.excelforum.com/showthread...hreadid=380794