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

 

[steve the fish]

You have to use Worksheet_Change not add a 5 to it.

 

[jasonb75]

First observation is the error in the first line, Worksheet_Change5 is not a valid name, it must be Worksheet_Change. Target5 is also invalid unless you change Target in the rest of the code to Target5 as well.

Second observation, rangeN13toN15 is not valid unless is is declared as a range variable and set correctly, which would appear not to be the case. It should be Range("N13:N15")

There may be other errors in later lines, that's as far as I got.

 

[NoSparks]

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".

Hello Scoti / Sam261

Do any of these 3 cells contain formula ?

 

[Scoti]

Made the changes suggested by Jasonb and moved it to the bottom of another Worksheet_Change event since I got the "ambiguous name" error. It still doesn't fire. Yes, there are formulas in each of the "N13:N15" cells. Is it not seeing the calculated values of those cells due to the formula?

 

[NoSparks]

Yes, there are formulas in each of the "N13:N15" cells.

That's why it doesn't fire.
What's in the cell (ie: the formula) doesn't change, only the display of it changes.
You'll need to look into maybe using the Calculate event.

 

[Scoti]

Ok, I moved it to a Calculate event but am getting a type mismatch error, highlighted on the line.
Set stp5 = wksht1.Range("AB1:AB9").Find(What:="", After:=("AB1"), LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlColumns, SearchDirection:=xlNext)

Private Sub Worksheet_Calculate()
With Application
        .EnableEvents = True
        .ScreenUpdating = True
        .Calculation = xlCalculationAutomatic
    End With


Dim wksht1 As Worksheet
Dim N13 As Range, N14 As Range, N15 As Range
Dim stp5 As Range, stp6 As Range, stp7 As Range
Set N13 = Cells.Find(What:="0", LookIn:=xlValues)
Set N14 = Cells.Find(What:="0", LookIn:=xlValues)
Set N15 = Cells.Find(What:="0", LookIn:=xlValues)




Set wksht1 = Sheets("Nucomat_Dashboard")






Set stp5 = wksht1.Range("AB1:AB9").Find(What:="", After:=("AB1"), LookIn:=xlValues, LookAt:=xlWhole, 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:=xlWhole, 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:=xlWhole, 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 With
End Sub

 

[jasonb75]

Could you describe the whole process end to end, I don't think that you can search for a blank in the way that you are attempting to (although I've never actually attempted it) which is likely the cause of the error.

Is this section meant to be looking at cells N13, N14 and N15? Because the way you have written it, it is looking at the whole sheet so will likely end up with erroneous results.

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

Also, I would suggest using Worksheet_Change, targeting the cells that change the formulas rather than the formulas themselves. Using a calculate event will cause it to be logged not only when the value changes to zero, but also if the result of the formula stays at zero after a calculation on a different formula. You can not target formula changes on individual cells as accurately as you can value changes.

I think that this thread could have a lot of answers but we are going to need a more accurate description of the task before getting to a solution.

 

[Scoti]

Oh I see. Yes I don't want it to fire every time the worksheet calculates only when those 3 cells result in a zero. Here is what this sheet does:
First, samples are loaded on the units via the Incoming Log section on the left. This updates the values in cells N13:N15 depending on which unit they were loaded on. The currently in process samples are "housed" on the Received tab which is what the formulas in N13:N15 are looking at to determine their values. Once the samples are done on the units, they are scanned into the Outgoing Sample Log section where they are removed from the Received tab and sent to various other locations(See the attached codes which perform this). This is where I need to determine how long each of the units ran throughout the shift. I have it successfully looking at the N13:N15 values to determine if they were running at the start of the shift & inserting the appropriate time into cells in the AA column, and it successfully watches the values in N13:N15 to determine if any units were loaded after the start of shift which inserts the current time in the AA column. The stop times are what I am having trouble with. There can be multiple starts & stops throughout the day. I currently have the code that inserts the times at the start of shift in a Worksheet Change event. The stop times would need to be a macro called when one of the buttons at the top of the Outgoing Sample Log section.

Private Sub cmdOutAXN_Click()
 
   Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Application.DisplayStatusBar = False
ActiveSheet.DisplayPageBreaks = False
ActiveSheet.Unprotect "Nuco123"
    Dim rngND As Range
    Dim lCurr As Long
  
    Set rngND = Sheets("Nucomat_Dashboard").Range("K4:K43")
    lCurr = WorksheetFunction.CountA(rngND)
 
    If lCurr = 0 Then
        
        MsgBox "There do not appear to be any samples to send.", vbInformation, "Insufficient Data"
        Exit Sub
    
    End If


    Do Until lCurr = 0
        
        If ProcessSendAXN(rngND.Cells(lCurr).Value, "AXN") Then
            
            rngND.Cells(lCurr).Value = ""
            lCurr = lCurr - 1
      
        Else


            Exit Sub
        
        End If
 
    Loop
Application.Calculation = xlCalculationAutomatic
    ActiveSheet.Range("K4").Select
    ActiveSheet.Range("N14").Formula = "=COUNTIF(Received!C[-12],""Unit 6"")"


    


Application.ScreenUpdating = True
Application.DisplayStatusBar = True
Application.EnableEvents = True


    
End Sub

Function ProcessSending_ND(Barcode As String, SentTo As String) As Boolean


    Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Application.DisplayStatusBar = False
Application.EnableEvents = False
ActiveSheet.DisplayPageBreaks = False


    Dim shtRec As Worksheet
     Dim ws As Worksheet
    Dim rngSrc As Range
    Dim lRwRec As Long
       Dim ws As Worksheet
    For Each ws In Worksheets
        ws.Unprotect "Nuco123"
    Next ws


    
    On Error GoTo PS_FDGB
    
    Set shtRec = Sheets("Received")
  
    Set rngSrc = shtRec.Range("$A:$A").Find( _
        What:=Barcode, _
        LookIn:=xlValues, _
        LookAt:=xlPart, _
        SearchOrder:=xlByRows)
    
    If rngSrc Is Nothing Then
        
        
        ProcessSending_ND = True
        Exit Function
    
    End If
    
    lRwRec = rngSrc.Row
  
 
    
    With shtRec
        
        lRwCmp = Application.WorksheetFunction.CountA(.Range("$A:$A"))
        .Range("A" & lRwRec & ":F" & lRwRec).ClearContents
        
        With .Sort
            
            .SortFields.Clear
            .SortFields.Add2 _
                Key:=shtRec.Range("A1:A" & lRwCmp), _
                SortOn:=xlSortOnValues, _
                Order:=xlAscending, _
                DataOption:=xlSortTextAsNumbers
            
            .SetRange shtRec.Range("A1:F" & lRwCmp)
            .Header = xlYes
            .Orientation = xlTopToBottom
            .MatchCase = False
            .Apply
      
        End With
    
    End With
    
    ProcessSending_ND = True
    
PS_Exit:
    
    Exit Function
    
PS_FDGB:
    
    MsgBox Err.Description & vbCrLf & "Please rectify this.", vbCritical, "Error " & Err.Number & " has occurred."
    ProcessSending_ND = False
    
    GoTo PS_Exit


      Dim ws As Worksheet
    For Each ws In Worksheets
        ws.Protect "Nuco123"
    Next ws


Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.DisplayStatusBar = True
Application.EnableEvents = True


    
End Function

 

[NoSparks]

Before addressing the automatic triggering part, would be best to make sure the code does what you want it to do.
If I follow correctly, I think this will do it,
Can you test this as a stand alone procedure and verify.

Sub Testing_01()

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

Set wksht1 = Sheets("Nucomat_Dashboard")

With wksht1
    
    Set stp5 = .Range("AB2:AB9").Find(What:="", After:=.Range("AB9"), LookIn:=xlValues, LookAt:=xlWhole, _
                                                SearchOrder:=xlRows, SearchDirection:=xlNext)
    Set stp6 = .Range("AB11:AB16").Find(What:="", After:=.Range("AB16"), LookIn:=xlValues, LookAt:=xlWhole, _
                                                SearchOrder:=xlRows, SearchDirection:=xlNext)
    Set stp7 = .Range("AB19:AB24").Find(What:="", After:=.Range("AB24"), LookIn:=xlValues, LookAt:=xlWhole, _
                                                SearchOrder:=xlRows, SearchDirection:=xlNext)

    If .Range("N13").Value = 0 Then stp5.Value = Format(Now(), "hh:mm")
    
    If .Range("N14").Value = 0 Then stp6.Value = Format(Now(), "hh:mm")

    If .Range("N15").Value = 0 Then stp7.Value = Format(Now(), "hh:mm")

End With

End Sub