Worksheet Change Event that affects cells on another worksheet

[Chao§pawN]

I've googled my *** off on this but I can't find anything that works for me. What I want to do:

When a user changes the value of a specific cell on Sheet1 to 0, lock a specific range of cells on Sheet2. If the user changes the value of that specific cell to anything else than 0, unlock that specific range of cells on Sheet2
I know how to set up a worksheet change event and lock cells on the same sheet, but I can't find how to lock cells on other sheets.

For example:

User changes the value of Range("I4") on Sheet1 to 0 --> the Range("C10,I10,O10,U10,AA10") on Sheet2 becomes Locked
User changes the value of Range("I4") on Sheet1 to anything else than 0 --> the Range("C10,I10,O10,U10,AA10") on Sheet2 becomes Unlocked

User changes the value of Range("I5") on Sheet1 to 0 --> the Range("C11,I11,O11,U11,AA11") on Sheet2 becomes Locked
User changes the value of Range("I5") on Sheet1 to anything else than 0 --> the Range("C11,I11,O11,U11,AA11") on Sheet2 becomes Unlocked

...and so on

So simply put, an input of 0 in a specific cell on Sheet1 means that a correspending range of cells on Sheet2 will not be available for input. A very simple concept but I can't get it to work and I can't find a solution anywhere. Any help would be very much appreciated!

 

[Smitty]

The code is only locking/unlocking the cells noted. But by default all cells on a sheet are Locked, so you're just seeing normal behavior. If you don't want other cells locked when the sheet is protected, you'll need to unprotect and unlock those cells (or all of them).

 

[Chao§pawN]

Yes I'm aware of that. The cells I'm talking about are unlocked before running the macro. Basically the entire sheet is locked and protected, except for the columns C,I,O,U and AA. Depending on user input on the "Setup" sheet, some of the cells in those colums are locked. This already works fine for a droplist I have on the "Setup" sheet, it locks/unlocks certain ranges of cells in the aformentioned columns depending on the user's choice. There's 4 choices in the droplist, 3 of which lock certain ranges of cells, the 4th one makes it so every single cell in C,I,O,U and AA is unlocked.

Starting off with the droplist on option 4, I can easily double check this by clicking on some cells in C,I,O,U and AA, they are definately unlocked.
Now with the addition of the aforementioned code in the Worksheet Change Event, and upon changing I3 on the "Setup" sheet (and not changing anything else), even though I'm only defining one cell on the "Planning" sheet (C9) to be locked, it locks that whole range of seemingly random cells. It's completely nonsensical that it randomly locks a bunch of cells that I have not defined, while others stay unlocked as they should.

Simply put I am absolutely sure that all those cells are unlocked and unprotected before running the Worksheet Change Event since I have a bunch of other macros that can be applied and which do work correctly for locking/unlocking the correct ranges of cells on the "Planning" sheet. Everything I have up to now works perfectly fine for locking/unlocking.

 

[Smitty]

Have you stepped through the code? I can't see any way that it's not doing exactly what it's being told to do, so some other process must be interfering.

 

[Chao§pawN]

Yeah I can't see why it's doing this either . The code can't be much simpler and there's nothing wrong with it, I can't very well have accidently defined a ton of cells by making a typo. Like you say there must be something else interfering, but it's still very strange because most of the cells that are randomly being locked are also not referred to by any of the other macros. They simply never need to change, and up to now nothing has ever affected them. It just makes no sense whatsoever.

I'll keep looking though, perhaps make a new workbook and start without any macros except for the Worksheet Change Event, see if it works correctly or still magically affects random cells it has no business to, and then add the other macros one by one to deduce where the problem comes from.

In any case thank you very much for all your assistance!

 

[Smitty]

I'll keep looking though, perhaps make a new workbook and start without any macros except for the Worksheet Change Event, see if it works correctly or still magically affects random cells it has no business to, and then add the other macros one by one to deduce where the problem comes from.

That's probably the best place to start. I've caught myself several times where one procedure unexpectedly affected another and it took a while to figure out.

One thing to look at, especially with Event code, is if you need to turn off Events to prevent recursion and other issues. E.G. changing a cell can cause an event to fire, which causes the cell to change and the event to fire again, and so on...

Application.EnableEvents = False

Just make sure to set it back to True at the end of the code, because Excel won't until you exit/reopen.

 

[Chao§pawN]

So after all the headaches from not having a clue why all that random locking was going on, I gave up on trying to figure it out. Mainly because I had already built a very extensive workbook and didn't have the motivation to start building a new one from scratch, and also because of that **** heatwave here in Belgium .

Today I decided to give it another look, so I just c/p-ed the exact same code from this forum thread into the exact same version of my workbook, a code that was in itself a c/p from my earlier attempt that resulted in all the random locking, and for some reason it works perfectly now, even though I basically did exactly the same thing without changing anything at all. So my conclusion is that it was probably a freak event. Or perhaps I did do something silly that I am completely unaware of.
In any case it all works perfectly fine now.

Also thank you for the advice concerning the Application.EnableEvents. For the moment it seems everything works without having to add it, no recursion or other issues. I'm sure it'll come in handy at some point though, the more I know the better!

Your advice has been a massive help and I'd like to properly thank you for it. Can I perhaps 'buy you a drink' via a paypal transfer, or gift you some game on steam if you're into that? Or anything else that can serve as such?

 

[Smitty]

Your advice has been a massive help and I'd like to properly thank you for it. Can I perhaps 'buy you a drink' via a paypal transfer, or gift you some game on steam if you're into that? Or anything else that can serve as such?

No worries, that's why we're all here!

 

[Chao§pawN]

Alrighty then, many thanks to you and everyone else moderating/maintaining this awesome forum!