I have drop down menu in I4 with around 15+ different options (type of problems). based on selection there are two outputs in cell K4 (full / partial) and its so far working fine.
i need to go further more advance and as soon as i select option in I4 it should give me present date in L4 and N4 present time, (when issue was reported) (these dates and time should not change at all. even i open my sheet after months) - then in W4 a timer should start from 00:00:00
(all should done immediately after selecting the option from drop down menu in I4 - only M4 will be entered manually)
then user will be entering the actual time in M4 when problem was started.
it is 100% sure that user might noticed problem after 5 or 10 or 30 minutes. lets say user noticed it after 10 min - since timer was started from 00:00:00 and M4 was updated 10 min after the actual problem, then timer in W4 should updated like 00:10:00
now coming to resolve the problem. in U4 user will manually enter the time when problem was resolved (assuming that user was informed late - so, need manual time entry here) Once user enter U4 with time, V4 should be filled up will present date (and shouldn't be changed by closing/opening the sheet later)
and finally W4 should be updated from time when problem was started and when it was resolved. (need to take those 2 times which were manually entered by user in M4 and U4)
i got 250 types of machines so i need same script/macro for all rows. it is also possible that 1 machine is down twice or more than that, in 1 day. so user is copying/pasting(inserting) row.
see atched file... i have highlighted and put comments there also.
as per below script, when there is value in cell 'I' it will add current date in cell 'L' and time in cell 'N' - can someone pls update the following script so i can get the running clock starting from 00:00:00 in cell 'W'
but there is one problem in the script. it is going till end of the sheet. i want something like: when selecting the drop down menu in 'I' --- script should check that row range 'A' (where model of the machines are entered) is not blank - if blank then script should not fill any cell and I row shouldn't be accepted.
here is my script:
===================================================
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim irow As Long
If Not Intersect(Target, Range("I:I")) Is Nothing Then
On Error Resume Next
Application.EnableEvents = False
irow = Target.Row
'if there is a date in column L end the macro
If IsEmpty(Target) Then
Target.Offset(0, 3).ClearContents
Target.Offset(0, 5).ClearContents
GoTo EndProc
End If
If IsDate(Target.Offset(0, 3)) Then GoTo EndProc
Target.Offset(0, 3) = Date
Target.Offset(0, 5) = Time
End If
EndProc:
Application.EnableEvents = True
End Sub
i need to go further more advance and as soon as i select option in I4 it should give me present date in L4 and N4 present time, (when issue was reported) (these dates and time should not change at all. even i open my sheet after months) - then in W4 a timer should start from 00:00:00
(all should done immediately after selecting the option from drop down menu in I4 - only M4 will be entered manually)
then user will be entering the actual time in M4 when problem was started.
it is 100% sure that user might noticed problem after 5 or 10 or 30 minutes. lets say user noticed it after 10 min - since timer was started from 00:00:00 and M4 was updated 10 min after the actual problem, then timer in W4 should updated like 00:10:00
now coming to resolve the problem. in U4 user will manually enter the time when problem was resolved (assuming that user was informed late - so, need manual time entry here) Once user enter U4 with time, V4 should be filled up will present date (and shouldn't be changed by closing/opening the sheet later)
and finally W4 should be updated from time when problem was started and when it was resolved. (need to take those 2 times which were manually entered by user in M4 and U4)
i got 250 types of machines so i need same script/macro for all rows. it is also possible that 1 machine is down twice or more than that, in 1 day. so user is copying/pasting(inserting) row.
see atched file... i have highlighted and put comments there also.
as per below script, when there is value in cell 'I' it will add current date in cell 'L' and time in cell 'N' - can someone pls update the following script so i can get the running clock starting from 00:00:00 in cell 'W'
but there is one problem in the script. it is going till end of the sheet. i want something like: when selecting the drop down menu in 'I' --- script should check that row range 'A' (where model of the machines are entered) is not blank - if blank then script should not fill any cell and I row shouldn't be accepted.
here is my script:
===================================================
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim irow As Long
If Not Intersect(Target, Range("I:I")) Is Nothing Then
On Error Resume Next
Application.EnableEvents = False
irow = Target.Row
'if there is a date in column L end the macro
If IsEmpty(Target) Then
Target.Offset(0, 3).ClearContents
Target.Offset(0, 5).ClearContents
GoTo EndProc
End If
If IsDate(Target.Offset(0, 3)) Then GoTo EndProc
Target.Offset(0, 3) = Date
Target.Offset(0, 5) = Time
End If
EndProc:
Application.EnableEvents = True
End Sub
