Worksheet Change Event that affects cells on another worksheet

Chao§pawN

New Member
Joined
Jul 2, 2015
Messages
10
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!
 

Some videos you may like

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,232
Office Version
  1. 365
Platform
  1. Windows
Should be straightforward, just make sure that when you are locking the cells you specify the sheet they are on.
 

Puertorekinsam

Active Member
Joined
Oct 8, 2005
Messages
287
Code:
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

New Member
Joined
Jul 2, 2015
Messages
10
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

Active Member
Joined
Oct 8, 2005
Messages
287

ADVERTISEMENT

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

Code:
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

New Member
Joined
Jul 2, 2015
Messages
10
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

New Member
Joined
Jul 2, 2015
Messages
10

ADVERTISEMENT

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:

Code:
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

Well-known Member
Joined
Jun 10, 2015
Messages
898
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

Legend
Joined
May 15, 2003
Messages
29,536
Welcome to the Board!

Set a reference to the sheet you want to unlock:

Code:
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

New Member
Joined
Jul 2, 2015
Messages
10
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:

Code:
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? :LOL:
 

Watch MrExcel Video

Forum statistics

Threads
1,123,399
Messages
5,601,459
Members
414,451
Latest member
jrose7

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top