Worksheet Change Not Firing

[Scoti]

I am trying to run the following code and I can't get it to fire. It watches 3 different cells for them to turn to "0" and when one of them does it should enter the current time into one of three different ranges based on which cell turned to "0". It's basically a stop clock for 3 different systems. Can anyone tell me why this isn't working please?

Private Sub Worksheet_Change5(ByVal Target5 As Range)
Application.EnableEvents
Application.Calculation = xlCalculationAutomatic


If Intersect(Target, rangeN13toN15) Is Nothing Then


Set N13 = Cells.Find(What:="0", LookIn:=xlValues)
Set N14 = Cells.Find(What:="0", LookIn:=xlValues)
Set N15 = Cells.Find(What:="0", LookIn:=xlValues)


Dim wksht1 As Worksheet


Dim On5 As Range


Dim stp5 As Range


Set wksht1 = Sheets("Nucomat_Dashboard")






Set stp5 = wksht1.Range("AB1:AB9").Find(What:="", After:=("AB1"), LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlColumns, SearchDirection:=xlNext)


If N13.Value = 0 Then


            stp5.End(xlNext).Value = Format(Now(), "hh:mm")
            Else
End If


Set stp6 = wksht1.Range("AB10:AB16").Find(What:="", After:=("AB10"), LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlColumns, SearchDirection:=xlNext)


If N14.Value = 0 Then


            stp5.End(xlNext).Value = Format(Now(), "hh:mm")
            Else
End If


Set stp7 = wksht1.Range("AB18:AB24").Find(What:="", After:=("AB18"), LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlColumns, SearchDirection:=xlNext)


If N15.Value = 0 Then


            stp7.End(xlNext).Value = Format(Now(), "hh:mm")
            Else
End If




With Application
        .EnableEvents = True
        .ScreenUpdating = True
        .Calculation = xlCalculationAutomatic
 
End Sub

 

[Scoti]

I added that and called if from within the Sub cmdOutAXN_Click() just after the "Loop" line. It placed the current time however it was in the 1st empty cell in the stp7 range instead of the stp6 range. Any idea why it would go to the next range instead of staying in the correct range?

 

[NoSparks]

if you run it as a stand alone sub does it write to the first empty cell in the proper range based on N13:N15 being zero or not zero.
Step through the code with the F8 key and check after each code line is executed.

 

[Scoti]

Yes. That worked stepping through it. N13 was at zero and it inserted the time in the 1st empty cell in the stp5 range.

 

[NoSparks]

OK, I'm guessing the mix up of the writing into stp7 range instead of stp6 range is an illusion caused by the call to the sub being ahead of code putting a formula into N14 so what the sub worked with and what you see afterwards may not be the same.

question:
You only have 8, 6 and 6 cells available for writing the times to.
What should happen if there are no more blank cells in any of those ranges ?

 

[Scoti]

It wouldn't be possible to have that many starts & stops during the course of the day. Typical is one or two however if I find it necessary I could modify this and extend those ranges. Using the codes already in place, I'm not sure where to call the code for this. I am fairly new to this and assumed that if I put it at the end that it would go in order but for some reason it isn't in this case. Any ideas?

 

[Scoti]

OK, I'm guessing the mix up of the writing into stp7 range instead of stp6 range is an illusion caused by the call to the sub being ahead of code putting a formula into N14 so what the sub worked with and what you see afterwards may not be the same.

question:
You only have 8, 6 and 6 cells available for writing the times to.
What should happen if there are no more blank cells in any of those ranges ?

Ok, I figured out a workaround by having it insert the current unit count value in cells "A10,A20,A30". From those I am running a "Timer" code which checks if those cells. If one of those cells is a zero, then it should check if there is a starting time in the corresponding range in column AA. If it finds a value there it should insert the stop time offset 1 column to the right. As you can see below, the code is working for the most part, however it is not seeing that there isn't a time in column AA. In the screenshot below you can see that there was no time in the red outlined AA cell but it inserted a time anyway. Can you tell me how I having it check for a time incorrectly to fix this?

 

[NoSparks]

Good Morning,
here's what I've come up with to make things trigger automatically, (adapted from and credit due to Hui for this)

Try this, hope column AZ is far enough off to the right to be out of the way
copy N13:N15 and paste as values into AZ13:AZ15
In AZ12 =SUMPRODUCT(--(N13:N15<>AZ13:AZ15))

In the WorkSheet Module

Private Sub Worksheet_Calculate()
If [AZ12] = 0 Then Exit Sub
    Application.EnableEvents = False
    Call StopTimes_v3
    Range("AZ13:AZ15").Value = Range("N13:N15").Value
    Application.EnableEvents = True
End Sub

In a standard module

Sub StopTimes_v3()

Dim wksht1 As Worksheet
Dim stp5 As Range, stp6 As Range, stp7 As Range

Set wksht1 = Sheets("Nucomat_Dashboard")

With wksht1
    ' set the stop cells to be written to
    On Error Resume Next    'in case no start time suppress the error notification
    Set stp5 = .Range("AA2:AA9").Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlRows).Offset(, 1)
    Set stp6 = .Range("AA11:AA16").Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlRows).Offset(, 1)
    Set stp7 = .Range("AA19:AA24").Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlRows).Offset(, 1)
    On Error GoTo 0         're-instate error notification
   
    ' when to write to those cells
    If .Range("N13").Value = 0 And .Range("N13") <> .Range("AZ13") Then
        If Not stp5 Is Nothing Then
            stp5.Value = Format(Now(), "hh:mm")
        End If
    End If
   
    If .Range("N14").Value = 0 And .Range("N14") <> .Range("AZ14") Then
        If Not stp6 Is Nothing Then
            stp6.Value = Format(Now(), "hh:mm")
        End If
    End If
   
    If .Range("N15").Value = 0 And .Range("N15") <> .Range("AZ15") Then
        If Not stp7 Is Nothing Then
            stp7.Value = Format(Now(), "hh:mm")
        End If
    End If
End With

End Sub

 

[Scoti]

Good Morning,
here's what I've come up with to make things trigger automatically, (adapted from and credit due to Hui for this)

Try this, hope column AZ is far enough off to the right to be out of the way
copy N13:N15 and paste as values into AZ13:AZ15
In AZ12 =SUMPRODUCT(--(N13:N15<>AZ13:AZ15))

In the WorkSheet Module

Private Sub Worksheet_Calculate()
If [AZ12] = 0 Then Exit Sub
    Application.EnableEvents = False
    Call StopTimes_v3
    Range("AZ13:AZ15").Value = Range("N13:N15").Value
    Application.EnableEvents = True
End Sub

In a standard module

Sub StopTimes_v3()

Dim wksht1 As Worksheet
Dim stp5 As Range, stp6 As Range, stp7 As Range

Set wksht1 = Sheets("Nucomat_Dashboard")

With wksht1
    ' set the stop cells to be written to
    On Error Resume Next    'in case no start time suppress the error notification
    Set stp5 = .Range("AA2:AA9").Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlRows).Offset(, 1)
    Set stp6 = .Range("AA11:AA16").Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlRows).Offset(, 1)
    Set stp7 = .Range("AA19:AA24").Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlRows).Offset(, 1)
    On Error GoTo 0         're-instate error notification
  
    ' when to write to those cells
    If .Range("N13").Value = 0 And .Range("N13") <> .Range("AZ13") Then
        If Not stp5 Is Nothing Then
            stp5.Value = Format(Now(), "hh:mm")
        End If
    End If
  
    If .Range("N14").Value = 0 And .Range("N14") <> .Range("AZ14") Then
        If Not stp6 Is Nothing Then
            stp6.Value = Format(Now(), "hh:mm")
        End If
    End If
  
    If .Range("N15").Value = 0 And .Range("N15") <> .Range("AZ15") Then
        If Not stp7 Is Nothing Then
            stp7.Value = Format(Now(), "hh:mm")
        End If
    End If
End With

End Sub

THIS WORKED PERFECTLY!!! Thank you so much. I am going to be spending several hours studying exactly how this code works to learn more. And thank you to Hui for the help also. Wow, I have been struggling with this for at least a month! Thanks again!!!

 

[NoSparks]

No problem Scoti, glad to have helped.
It was quite a few years ago I tucked that link away for future reference and been a few times its come in handy.

Would appreciate it if you would change the post you marked as the solution from your reply to my actual post, thanks.

Good luck with the rest of your project.