Trouble activating macro [Solved/Closed]

Report
-
 Trowa -
Hello,

I've got a macro which runs on 12 worksheets.
When the result of a formula changes I want the macro to do his job.

What I do now is manually selecting each worksheet and then copy-paste a formula in the same cell.
For the copy-paste action I've made a macro. The macro will copy-paste a formule in the same cell for each of the 12 sheets. But I still need to select the sheet before my initial macro is activated.

I've searched the internet and I found something which may clearify my issue:

How do I run a macro every time a certain cell changes its value?
There is an event called Worksheet_Change which is triggered when a value is entered (it will not fire when a formula result changes).


What's between the brackets is exactly what I want: Fire the macro when a formula result changes.
Does anybody know how to do this?

Best regards,
Trowa

2 replies

Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
791
try sheet calculate event.
Thanks for the suggestions, but I still can't make it work. Let me specify with the file at hand:

https://authentification.site/333819206.html

I have made a calender which I want to prepare for next year with the press of a button.

When I run the macro "JaartalVerhogen", the current year will rise by one. This will change the dayname of each day. According to the dayname the columns change color.

Since I have more than 3 colors, I had to use a complicated VBA code (provided by Aquarelle on this website)
which has been put under ThisWorkbook when looking at the vba codes.

Now my problem: The colors will only change when I have selected the sheet and copy paste a formula in the same cell.

I want the colors to change when the macro "JaartalVerhogen" is fired.

The worksheet calculate event seems to be the solution, but how do I use it in this situation?

Please let me know if something is still unclear.

Best regards,
Trowa
Posts
261
Registration date
Saturday April 11, 2009
Status
Member
Last seen
June 21, 2011
307
Hi Trowa,

call the macro in each sheets from the worksheet in which the user enters the data