which to use .. worksheet_change vs Worksheet_Calculate?

[Nichole09]

Hi All!

I have had my worksheet change event code work for a while without any issues, until I wanted to add this new portion:

If Not Intersect(target, range("AA63")) Is Nothing Then
If target.Value > 1 Then
range("R59") = range("AA63")
End If
End If

Once adding the above code, the rest of my if statements kept working except for this portion:

If Not Intersect(target, range("E12")) Is Nothing Then
If target.Value > 1 Then
range("e10") = range("e13")
End If
End If

If I take the portion out that targets range AA63 the worksheet change works fine again. I thought this might be, because range AA63 contains a formula, that when conditions met, the value will be a date. The goal is, if range AA63 formula is true and shows a date, I want that date to also be in range R59. I tried accomplishing this by a worksheet change event, which isn't working, than I tried adding this code to my existing worksheet calculate event since this cell contains a formula (see below.) This also, is not working.

Sub worksheet_calculate()
Dim target As range
Set target = range("AA63")

 
If ThisWorkbook.Sheets("Status of Review").range("AA37").Value = True Then
ThisWorkbook.Sheets("Status of Review").CheckBoxes("Check Box 119").Value = xlOn
Else
End If

If ThisWorkbook.Sheets("Status of Review").range("AA42").Value = True Then
ThisWorkbook.Sheets("Status of Review").CheckBoxes("Check Box 118").Value = xlOn
Else
End If
If target.Address = range("AA63") Then
If target.Value > 1 Then
range("R59") = range("AA63")
End If
End If
 
Application.EnableEvents = True

AM I missing something!? What I am trying to accomplish is very similar to this portion of my worksheet event:

If Not Intersect(target, range("E12")) Is Nothing Then
If target.Value > 1 Then
range("e10") = range("e13")
End If
End If

This portion, also contains a formula in range E13 and if conditions met reflects a date, but this portion works just fine! I have also tried putting Application.EnableEvents = True in the immediate window, than saving my worksheet and pulling it back up. In repeating this, my code worked once.... but did not again. I am not sure why. Can anyone help me figure out how to get my range AA63 code to work? does it matter which event I put it under?


Original codes that work: (I would like to add my range AA63 (corrected code) to one of my existing events below)

Private Sub Worksheet_Change(ByVal target As range)
Application.EnableEvents = False
Application.ScreenUpdating = False
If Not Intersect(target, range("E39")) Is Nothing Then
If IsDate(range("E39").Value) Then
range("I28") = "Received"
Else
If range("E39").Value = "Lot Loan" Then
range("I28") = "Lot Loan"
End If
End If
End If
 
If Not Intersect(target, range("H39")) Is Nothing Then
If target.Value > 1 Then
range("I26") = "Received"
End If
End If

If Not Intersect(target, range("L42")) Is Nothing Then
If target.Value > 1 Then
range("i30") = "Received"
End If
End If

If Not Intersect(target, range("L39")) Is Nothing Then
If target.Value = "PIW" Then
range("i30") = "PIW"
End If
End If
If Not Intersect(target, range("E12")) Is Nothing Then
If target.Value > 1 Then
range("e10") = range("e13")
End If
End If

Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
 
Sub worksheet_calculate()
Dim target As range
Set target = range("AA63")

Application.ScreenUpdating = False
If ThisWorkbook.Sheets("Status of Review").range("AA37").Value = True Then
ThisWorkbook.Sheets("Status of Review").CheckBoxes("Check Box 119").Value = xlOn
Else
End If

If ThisWorkbook.Sheets("Status of Review").range("AA42").Value = True Then
ThisWorkbook.Sheets("Status of Review").CheckBoxes("Check Box 118").Value = xlOn
Else
End If

Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

Thank you!!!

 

[RoryA]

A formula calculation will not trigger a Change event. You can monitor the input cells with a Change event - on the relevant sheet(s) - or use the Calculate event.

 

[Nichole09]

Thank you RoryA! I am using the calculate event option from your comment. I am using the code below. Which works, only when I put Application.EnableEvents = True in the immediate window. But as soon as I close the worksheet, it no longer works. I have to put Application.EnableEvents = True in the immediate window each time I open the worksheet for this code to work. Any suggestions on how to get this to work all the time?

Sub worksheet_calculate()
On Error Resume Next
Application.EnableEvents = False
If ThisWorkbook.Sheets("Status of Review").range("AA37").Value = True Then
ThisWorkbook.Sheets("Status of Review").CheckBoxes("Check Box 119").Value = xlOn
Else
End If

If ThisWorkbook.Sheets("Status of Review").range("AA42").Value = True Then
ThisWorkbook.Sheets("Status of Review").CheckBoxes("Check Box 118").Value = xlOn
Else
End If
If ThisWorkbook.Sheets("Status of Review").range("AA63").Value <> "" Then
ThisWorkbook.Sheets("Status of Review").range("R59").Value = range("AA63")
Else
End If
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

 

[RoryA]

I’d guess you have some other code that turns events off and never turns them back on.