Help to solve VBA Script for Excel - Programmers Heaven

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

Welcome to the new platform of Programmer's Heaven! We apologize for the inconvenience caused, if you visited us from a broken link of the previous version. The main reason to move to a new platform is to provide more effective and collaborative experience to you all. Please feel free to experience the new platform and use its exciting features. Contact us for any issue that you need to get clarified. We are more than happy to help you.

Help to solve VBA Script for Excel

mwvirkmwvirk Posts: 1Member
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

Sign In or Register to comment.