Automatic Generation of dates.

[Guest]

I have made a excel sheet for managing the Bug Details which contains
following columns:
1. Description
2. Status : which has New, Open, close & Reopen list items.
3. New Date
4. Open Date
5. Close Date
6. Reopen Date.

When i change the status to New then the system date should automatically
get generated in

the New date column.


When i change the status to Open then the system date should automatically
get generated in

the open date column.

Same for other two columns.

Plz give me the solution for the above problem.

 

[Nick Hodge]

Sacin

You could put the following in the worksheet_change() event

(implementation
see:http://www.nickhodge.co.uk/vba/vbaimplement.htm#EventCode)

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, Columns("B:B")) Is Nothing Then
Application.EnableEvents = False
Select Case UCase(Target.Value)
Case Is = "NEW"
Target.Offset(0, 1).Value = Date
Case Is = "OPEN"
Target.Offset(0, 2).Value = Date
Case Is = "CLOSE"
Target.Offset(0, 3).Value = Date
Case Is = "REOPEN"
Target.Offset(0, 4).Value = Date
Case Else
MsgBox "Entry error", vbOKOnly + vbExclamation
Application.EnableEvents = True
Exit Sub
End Select
End If
Application.EnableEvents = True
End Sub

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)

 

[mangesh_yadav]

Use the Worksheet_Change event

Private Sub Worksheet_Change(ByVal Target As Range)

if target.column=2 then
If (Target.value = "new") Then

cells(target.row,3) = now()

elseIf (Target.value = "open") Then

cells(target.row,4) = now()

elseIf (Target.value = "close") Then

cells(target.row,5) = now()

elseIf (Target.value = "reopen") Then

cells(target.row,6) = now()

end if
end if

end sub


This code should be in the sheet module for the required sheet. I
assumes that the new date column is 3, open is 4, close is 5, an
reopen is 6. When you enter your status in column 2, then it will se
the required date in the required row and column.

- Manges