VBA not picking up Formula populated cells need a new trigger?

[DMO123]

Hi All,

i have the below code which runs when a cells is populated (but only works when manually populated). i have cells that are either manually populated (which it words fine for) and some that are populated via a formula (which is does not work for). do i need to change the trigger to enable the VBA to pick up the cells populated via a formula? preferably an automatic solution would be best as these cells are hidden and is used to flag issues etc.

Private Sub Worksheet_Open(ByVal Target As Range)    If Target.Column = 11 Or Target.Column = 13 Then 'Column I or column M
        If UCase(Target) = "YES" Then Target.Offset(0, -1).ClearContents
    End If
End Sub

 

[steve the fish]

Not really no to be honest. If you do what you are asking you are going to be deleting your formulas so they arent going to work tomorrow. What is in A8 and how is that getting filled?

 

[DMO123]

Sorry hard to explain! that's fine if its deleted as each line is only used once, i have another VBA that runs to create a new line before this condition is met so an updated version will be added to the bottom. essentially its for a schedule each line is a task and once its marked as submitted the other VBA runs to generate a new line with the same task just one year later. as its in a table the formulas automatically get added to the new line so its ok if they are removed.

 

[steve the fish]

See if this works. Test on a copy of your workbook first!

Private Sub Worksheet_Calculate()

Dim lr As Long, arr, arrCol, i As Long, j As Long, rng As Range

lr = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row

arr = Range("A1:T" & lr)
arrCol = Array(11, 16, 17, 19, 20)

For i = LBound(arr) To UBound(arr)
    For j = LBound(arrCol) To UBound(arrCol)
        If LCase(arr(i, arrCol(j))) = "yes" Then
            If Not rng Is Nothing Then
                Set rng = Union(rng, Cells(i, arrCol(j) - 1))
            Else
                Set rng = Cells(i, arrCol(j) - 1)
            End If
        End If
    Next
Next

If Not rng Is Nothing Then rng.ClearContents

End Sub

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Union(Columns(11), Columns(16), Columns(17), Columns(19), Columns(20))) Is Nothing Then
    Worksheet_Calculate
End If

End Sub

 

[DMO123]

Thanks Steve, having a slight issue, the code is freezing the whole document it ran once (and looks like it works from what i can see) but after the first run the document becomes unusable. it keeps displaying a run time error. the line that throws an error is

lr = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row

 

[steve the fish]

If that line fails id think its looking at a blank worksheet.

 

[DMO123]

So the Worksheet isn't blank its running and clearing the values and not stopping searching, just keeps going.

 

[steve the fish]

Oh ok. Try changing:

If Not Rng Is Nothing Then Rng.ClearContents

to

Application.EnableEvents = False
If Not Rng Is Nothing Then Rng.ClearContents
Application.EnableEvents = True

 

[DMO123]

@stevethefish thank you so much!! it looks to be working perfectly, have tested it with a few different dates! i have been looking for this answer for so long!!!