Cell Changing due to Calculation

I have some cells that change due to calculations via the worksheet_calculate sub. What happens is that a checkbox is check, which changes a cell from false to true. Once 3 checkboxes in a certain range are all checked (true), another cell changes.

How do I trigger an event to happen when these cells change? I have tried using the worksheet_change sub, but it doesn't work.

Any advice would e very much appreciated

Originally Posted by :) Sixthsense :)

Why don't you use ActiveX checkbox instead of Form Checkbox?

Would that make it easier? Given I've got over 100 checkboxes on the sheet, all linked to different cells, calculations etc, I was hoping to find another solution!

Originally Posted by :) Sixthsense :)

Refer the attached file to know how to do it

Thanks for that. What I need though is something that runs when cell A1 in your example changes. i.e. when the value in cell A1 changes a message box appears, rather than when the boxes are checked

Originally Posted by :) Sixthsense :)

Wrongly Posted

What is wrongly posted?

Originally Posted by :) Sixthsense :)

Before you see this post I wrongly posted a solution which I arrived for another question.

Coming back to your question, you may think it is an onclick event running with that check boxes, but actually it is an calculate event which runs whenever any change occurs in formula. In the given example the A1 cell countif formula will trigger the calculate event whenever any change occurs in the linked cells of the check box.

Hope its clear now

Yeah, but the message box is appearing as a result of clicking the checkbox, not as a result of a change of value in A1. I need the message box to appear as a result of A1 changing value

Originally Posted by LJMetzger

Hi dark,

1. If a cell is changed manually or by VBA, Workseet_Change() is needed.

2. If a cell is changed manually or by VBA and the old value is needed:
a. A global variable is needed to save the old value.
b. Worksheet_SelectionChange() is needed to identify the value of the old value.
c. Workseet_Change() is needed to identify the new value, and possibly take further action.

3. If a range of values is changed by Formula (including a range containing only one cell):
a. A global array of variables is needed to store the old values.
b. The global array needes to be initialized by Workbook_Open().
c. Worksheet_Calculate() is needed to identify which cells in the range changed value.

See post #3 in the following link for posted code and a sample file:
http://www.excelforum.com/excel-prog...ionchange.html

Lewis

I think I have this working to a certain extent, however does anyone know how to set it for a rnage of single cells? i.e. it currently has this code:

Please Login or Register  to view this content.

I would want to include it for "AC6", "AC9", "AC12", "AC15", AC18", "AC21", "AC24"

Any ideas?

Originally Posted by :) Sixthsense :)

Please re-read my quoted sentence once again for getting clarity

Ok, so how do I change this so that the calculate event only runs when cell A1 is changed, rather than the check boxes?

See the attached file which simulates your condition. Please do not cross post as it does nothing but cause confusion.

Lewis

ThisWorkbook module:

Please Login or Register  to view this content.

Sheet1 module:

Please Login or Register  to view this content.

An ordinary module:

Please Login or Register  to view this content.