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

 

[bensmith1987]

Hi There,

From what I read, you want any change (either manual or formula generated) to run a piece of VBA code?

If so try the below under the worksheet code - you have to set the range of cells you want it to run on under the ChkRng.. but it works on both manual and formula driven changes.

Private Sub Worksheet_Calculate()

Dim ChkRng As Range
Set ChkRng = Range("A2:A5")
If Not Intersect(ChkRng, ChkRng) Is Nothing Then
[COLOR=#33cc00]'Add Code Here[/COLOR]
End If

End Sub

Let me know if this works or if i have misunderstood your requirement.

Thanks
Ben

 

[DMO123]

Hi Ben

thank you. can i set multiple ranges? for example it could be column K to remove data in column J and column P to remove data in column O etc.

i have also tested this and get the error "Bock If Without End If" not sure if you can help here?

 

[steve the fish]

This is always true:

If Not Intersect(ChkRng, ChkRng) Is Nothing Then

so wont do anything useful. A range is always going to intersect itself. I personally dont like worksheet calculate events at all. They constantly run often causing slow workbooks. Can you not do a workbook change event on the cells that cause the formulas to recalculate?

 

[DMO123]

@stevethefish i have tried a Worksheet_Change which didn't work for automatic populated fields i had to select each filed marked as yes for the code to trigger! Workbook_Change also didn't work nothing happens when the values are entered.

sorry to answer your question as well, there is a mixture of columns some formula population and other manual input, how would i make sure i trigger all?

 

[bensmith1987]

Yes, you're right... Monday morning and no cup of tea yet - brain clearly not engaged!

So apologies - but yes, agree with Steve. Presumably the only reason formula based cells should change is based on a change from another cell.
I can see the issue could be if the formula refers to a value on another workbook or outside a named check range, which changes while this workbook is open. This doesnt cause the workbook change event.

 

[steve the fish]

What are the columns you want to check? You say 11 and 13 and then I and M. These arent the same.

 

[DMO123]

@stevethefish sorry i haven't updated the lettering, columns i need to check is: 11 (K), 16 (P), 17 (Q), 19 (S), 20 (T)

 

[steve the fish]

So you want to clear the cells in P if Q contains "yes"? But you also want to check P also? Is that right because it seems a little odd if you have formulas in there?

 

[DMO123]

Yes the formula i have there (In P) is: =IFERROR(IF(A8-TODAY()<30,"Yes",""),"") so there inst always a "yes" in there only if it meets the criteria, but as this move down the line to Q which is: =IFERROR(IF(A8-TODAY()<8,"Yes",""),"") it would need to wipe P so it displays the correct conditional formatting. i hope this makes sense!