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

DMO123

Board Regular
Joined
Aug 16, 2018
Messages
99
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.

Code:
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
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
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.

Code:
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
 
Upvote 0
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?
 
Last edited:
Upvote 0
This is always true:

Code:
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?
 
Upvote 0
@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?
 
Last edited:
Upvote 0
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.
 
Upvote 0
What are the columns you want to check? You say 11 and 13 and then I and M. These arent the same.
 
Upvote 0
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?
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,223,702
Messages
6,173,959
Members
452,539
Latest member
delvey

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top