Thread: 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

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" <daolb.1qyyak_1119344725.8483@excelforum-nospam.com> wrote in
message news:daolb.1qyyak_1119344725.8483@excelforum-nospam.com...
>
> 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.

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.

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,

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

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

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

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