Worksheet_Calculate randomly causing issues

[Jdevil]

Hello,

I made a code on Worksheet_Calculate that only serves for the sheet called "Input". I started with a condition:

If ActiveSheet.Name = "Input" Then
stuff
End If

So I have no issues when in another sheet, at least. However, sometimes, when I edit something on the sheet "Input", I get an error, although nothing is wrong. (e.g. it redirects me to an "else" statement in the code and there's nothing wrong with the code...) If I put it in a button instead, it does nothing wrong, but I need it to trigger everytime someone edit the page.

So here's what I'm doing:

I want a code that triggers when someone a cell in the following range: F10:I17. It makes buttons appear or disappear, depending on the total in that range. Example:

problem.png

As you can see, every row has 2 button associated to it; if the total is at least one for the row, they appear. Here's my code (it works):

Please Login or Register  to view this content.

(EDIT: Yeah forget about the If 1 = 2, that was just me trying to stop the code from working for some time, it's just a comment anyway)

With the On error clause, every now and then when I do anything on the file, the msgbox appears and everything freezes, excel crashes. What's going on? all those objects exist and both those sheets exist, so there's no worries to have about that. I therefore am thinking it is caused by Worksheet_Calculate doing weird things I don't understand. Therefore..

1- Does anyone have a way to fix this? OR
2- Does anyone see another way to exactly what I'm trying to do, without using Worksheet_calculate?

Hope it's clear enough!

Thanks

[natefarm]

The Calculate event isn't the place for what you're trying to do. I'm assuming that your code is triggering the event, creating a recursive loop that is sure to blow up. Put it in the Change event of the Input sheet, which fires only when a change is made on that sheet, with no need to check the sheet name, or to reference it. You can also eliminate all the Sheets("Champs") reference with a single With statement. Use the Intersect statement to determine if the entry was in the desired range. Here's the starter code:

Please Login or Register  to view this content.

[Jdevil]

I can't really use "With" that simply because the code actually uses two sheets for some time, I could probably name things to make it simplier, but that's minor considering..

You entirely solved my problem with the "Not intersect" thing in Worksheet_Change. Works wonder.

Thanks a lot!

[natefarm]

You're welcome. If you group your Ifs by applicable sheet, and then use separate Withs to apply to each group, it should still work. It's more efficient, easier to read, and easier to maintain.

Glad I could help.