VBA to make shape appear based on cell value works but wont trigger with count if formula

[FgureIToutTrout]

I am using this formula to make a shape appear:

Please Login or Register  to view this content.

It works when I manually input the cell value, but when I use a countif formula to calculate the cell value the VBA won't run. Auto calculate is activated in the options menu.

Please help!

[AliGW]

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however you need to include code tags around your code.

Please take a moment to add the tags. Posting code between tags makes your code much easier to read and copy for testing, and it also maintains VBA formatting.

Please see Forum Rule #2 about code tags and adjust accordingly. Click on Edit to open your post, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

(Note: this change is not optional. I have added these for you, as you are new here.)

Please change your forum profile and specify exactly which 'latest' version of Excel you have: there is more than one possibility! Thanks.

[Marc L]

As a formula does not fire the Change event, only user input …

[FgureIToutTrout]

Thanks, can I do anything about that? I would like the formula to trigger the event or find an alternative workaround

[Marc L]

You may work with the Worksheet_Calculate event, see the samples within this forum …

[FgureIToutTrout]

What a wonderful forum! Thank you for all your help. I must say I am overwhelmed by the feeling of collaboration and mutual support

[FgureIToutTrout]

Thanks, but no thanks will be heading to another forum.

[AliGW]

Hopefully one of our VBA specialists will give you a more detailed answer than MarcL did in post #5, for which I apologise on behalf of the forum. If I knew about these things, I would gladly help you myself. I am sure someone will help in due course - it hasn't been too long since you first posted, so just give it a while longer, please.

[bakerman2]

There are only 2 ways to workaroud this issue.

Either you store the value of A1 in another cell and monitor that in the Calculate-event wether it changed.

Please Login or Register  to view this content.

Or you monitor one of the dependents of the formula in A1 in the Change-event for a change in value.

[Andy Pope]

You can use a UDF.

Please Login or Register  to view this content.

In the attached example change the value of A3. This will cause the COUNTIF formula in A1 to update which in turn will cause the 2 UDF formula to run and hide each shape if condition is false.