Disable Cells based on Cell Selection

I've seen lots of posts about how to disable a cell based on the content of another cell, however I want to disable/re-enable cells based on the user clicking/selecting another cell. The range of affected cells are in this case, C12:C15 (these are the cells the user can select) and C18:C27 (these are the cells to be disabled/re-enabled).

For example, if the user clicks

Cell C12 it should disable Cells C19:C27
Cell C13 it should disable Cells C18 and C22:C27 and re-enable C19:C21
Cell C14 it should disable Cells C18:C21 and C25:C27 and re-enable C22:C24
Cell C15 it should disable Cells C18:C24 and re-enable C25:C27

Is this possible to do?

Ideally, I would like the disabled cells to be 'greyed out' as well, would this have to be done using 'conditional formatting' or could it be achieved as part of any code?

Many thanks

Many thanks tigeravatar... I'm a bit new to VBA, but copying and pasting your code into the worksheet module is giving be the following compile error (though I don't see why).

Ambiguous name detected: Worksheet_SelectionChange

Also, the code at the start, 'Const strPW As String = "abc123"' is added to the end of the previous code, i.e. above the line break, if that makes any sense?

Am I doing something wrong?

Okay, that works perfectly... How would I edit the code so that the worksheet isn't protected each time one of the cells is selected. I would like the user to be able to freely click on any cell within the range C12:C15 and for the relevant cells to lock/unlock without needing to unprotect the sheet each time? Or am I missing something else?

Many thanks

Okay, that makes sense... I may need to rethink my approach slightly...

Many thanks for your very quick response and thank you for all your help and patience...

Okay, my worksheet has several grouped rows and I want to be able to protect the worksheet, so to take this further, what I would like to be able to do is for the user to be able is to click a toggle button that shows/hides the groups and in doing so for the sheet to become temporarily unlocked, to allow the row range to expand or contract (as this isn't possible when the sheet is protected) and once this action is complete, for the sheet to then be locked again.

How would I go about achieving this?

Hi tigeravatar,

This works perfectly for my needs, thank you. I take on board your comments about using a userform, though in this case, that isn't going to be possible.
I applied your code to a shape using a macro and it works there as well. If I wanted the text in the shape to toggle to say, "Show", '"Hide", how would I adapt your code to achieve this?

Hi tigeravatar,

So, I amended the code to this. My shape is called Rectangle1. But it errors on .Text = "Show". Am I doing something wrong?

Please Login or Register  to view this content.

Ah, okay, that makes sense and it now works perfectly.

Many thanks for your help, it is greatly appreciated.