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!

 

[Norie]

Should be straightforward, just make sure that when you are locking the cells you specify the sheet they are on.

 

[Puertorekinsam]

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

TestRow = Range(Target.Address).Row
testcolumn = Range(Target.Address).Column

If testcolumn = 9 And TestRow >= 4 Then
    Sheet2.Cells(6 + TestRow, 3).Locked = (Target.Value = 0)
    Sheet2.Cells(6 + TestRow, 9).Locked = (Target.Value = 0)
    Sheet2.Cells(6 + TestRow, 15).Locked = (Target.Value = 0)
    Sheet2.Cells(6 + TestRow, 21).Locked = (Target.Value = 0)
    Sheet2.Cells(6 + TestRow, 27).Locked = (Target.Value = 0)
End If

End Sub

 

[Chao§pawN]

Thank you both for the lightning fast reply.

However I am a complete noob when it comes to VBA, most of my very limited knowledge comes from googling specific solutions.
@Norie: How do I specify the sheet they are on? I've tried this before within a Worksheet Change Event but it didn't work. I have other macros that are in a Module that lock cells on another sheet that do work fine, but using the same method in a Worksheet Change Event does not work.

Example of a portion of a macro located in a Module, linked to a droplist on Sheet1 (Sheet1 is called "Setup", Sheet2 is called "Planning" btw):

ThisWorkbook.Sheets("Planning").Activate
ActiveSheet.Unprotect
Range("C18").Select
Selection.ClearContents
Range("C18").Locked = True
ActiveSheet.Protect

However this kind of code does not work when used in a Worksheet Change Event

@Puertorekinsam
I tried that code but it didn't work. Probably because Sheet2 is called "Planning". How do I define that correctly?

Thank you all very much!!!

 

[Puertorekinsam]

be sure to put this code in the first page where they are making the change.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

TestRow = Range(Target.Address).Row
testcolumn = Range(Target.Address).Column

If testcolumn = 9 And TestRow >= 4 Then
    Sheets("Planning").Cells(6 + TestRow, 3).Locked = (Target.Value = 0)
    Sheets("Planning").Cells(6 + TestRow, 9).Locked = (Target.Value = 0)
    Sheets("Planning").Cells(6 + TestRow, 15).Locked = (Target.Value = 0)
    Sheets("Planning").Cells(6 + TestRow, 21).Locked = (Target.Value = 0)
    Sheets("Planning").Cells(6 + TestRow, 27).Locked = (Target.Value = 0)
End If

End Sub

 

[Chao§pawN]

Thanks again for your reply.

I made very sure to put this in the right page, the one for the "Setup" sheet, but it does not work either. A number of things happen...

* Upon making any input in the I-column I get an error stating "Property Locked of class Range can not be set"

So I unprotected the "Planning" sheet for testing purposes (to clarify, the "Planning" sheet SHOULD be protected however) and tried again

* Upon making any input in the I-column I do not get an error, but neither does anything on the "Planning" sheet happen. None of the cells that should be locked are being locked. What does happen however is that, on the Setup sheet, a persistent selection box appears around the cell below the one I edited. This selection box remains even when I select other cells.
So upon entering a value in I4, a selection box appears around I5 and remains there even when selecting another cell (at which point I have 2 selection boxes). I hope I'm still making sense, it's a bit of a weird thing to happen.

In any case the code not only doesn't work, it also does weird stuff it's not supposed to.

It would be helpful if anyone could give me an example of a code that works for 1 specific cell rather than a code that applies to the entire column. Not that the latter wouldn't be simpler and more efficient, but a code for a single cell would make it a lot easier to understand everything step by step.

Thanks again for your help!

 

[Chao§pawN]

For example I have this simple code in my "Setup" sheet that works just fine, it locks a few cells on the "Setup" sheet upon entering the value 0 in cell D29, and unlocks them again when anything else than 0 is entered:

Private Sub Worksheet_Change(ByVal Target As Range)


If Target.Address = "$D$29" Then
    ActiveSheet.Unprotect
    If Range("D29").Value = 0 Then
    
        Range("I27:I29").Locked = True
        
    Else
    
        Range("I27:I29").Locked = False
        
    End If
    ActiveSheet.Protect
End If


End Sub

So what I need to find out is how I can do the same thing for e.g. cell I4 on the "Setup" sheet, but make it lock cells on the "Planning" sheet.

Thanks again!

 

[V_Malkoti]

In your existing code, try qualifying Range objects with Sheets object like

Sheets("Planning").Range("I27:I29")

and similarly replace ActiveSheet with Sheets("Planning") if that's the sheet your need to unprotect.

 

[Smitty]

Welcome to the Board!

Set a reference to the sheet you want to unlock:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim ws As Worksheet
    Set ws = Sheets("Planning")

    If Target.Address = "$D$29" Then
        If Target.Value = 0 Then
            With ws
                .Unprotect
                    .Range("I27:I29").Locked = True
                .Protect
            End With
        Else
            With ws
                    .Unprotect
                        .Range("I27:I29").Locked = False
                    .Protect
                End With
        End If
    End If
    
End Sub

HTH,

 

[Chao§pawN]

Thank you so much, it's getting quite close. This works in the sense that it does lock the cells on the "Planning" sheet, but it also locks a whole bunch of other cells on the "Planning" sheet for no apparent reason.
Here's the code I used:

Private Sub Worksheet_Change(ByVal Target As Range)


Dim ws As Worksheet
    Set ws = Sheets("Planning")


    If Target.Address = "$I$3" Then
        If Target.Value = 0 Then
            With ws
                .Unprotect
                    .Range("$C$9").Locked = True
                .Protect
            End With
        Else
            With ws
                .Unprotect
                    .Range("$C$9").Locked = False
                .Protect
            End With
        End If
    End If


End Sub

Now when I enter 0 in I3 it doesn't only, on the "Planning" sheet, lock C9. It also locks C10:C21, C24 and C29. It does the same for Columns I,O,U,AA. Probably does so for all the other columns as well but those are already locked anyway.
How does that even make sense?? The only cell I'm defining as range is C9...

I even tried deleting the other code I mentioned above so this is absolutely the only code that is on the "Setup" sheet, and it still does this. What the hell?