dynamically lock/unlock cells based on the value of another cell

dsheard2015

Board Regular
Joined
May 10, 2016
Messages
134
Hello again!

I have a protected sheet, "Form 34-4 (1)", with all data entry cells unlocked, cells A12:U31. When a student logs in to the workbook a "1" is placed in cell Z1. What I need to happen is when cell Z12="1" then cells A12:S31 and U12:U31 will become locked, giving the student access only to cells T12:T31 where they can enter their initials. Also, just the opposite, when cell Z12="" then cells A12:S31 and U12:U31 will become unlocked giving full access to the instructor for data entry but locking cells T12:T31.

The sheet is password protected. The password is "22068"

I have found numerous ways on the forum to do something similar to what I need however, I haven't found a solution to work exactly how I need it to.

Thanks again for the help, you guys are great!

dave
 
Last edited:

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Hi,
I assume that the value in range Z12 is changed by formula? If this is so, you will need to use the calculate event. However, this event has no range parameter so a bit of trickery will need to be applied to ensure code is not run each time sheet calculates.

You can either use a helper cell in your sheet or the Range.ID property as a place holder to test if the value in the Target range has changed & if so, run the code.

Not fully tested but see if code below does what you want:


Place in you worksheets code page:

Code:
Private Sub Worksheet_Calculate()


    Dim Target As Range
    
    Set Target = Me.Range("Z12")
    
    If CStr(Target.Value) <> Target.ID Then
        Me.Unprotect Password:="22068"
        Target.ID = CStr(Target.Value)
        Me.Range("A12:S31, U12:U31").Locked = CBool(Target.Value = 1)
        Me.EnableSelection = xlUnlockedCells
        Me.Protect Password:="22068"
    End If
End Sub

Hope Helpful

Dave
 
Upvote 0
Hello again Dave, and thanks again for your help.

Ok, I tried that code and it did not work exactly as needed. After the code was installed, I logged in as a student. When the worksheet opened, cell Z1 showed "1" like it should have but cells A12:U31 were all accessible. I entered data into cell K14 to see what would happen and at that time all cells except for T12:T31 were locked. I closed that sheet and logged in again but this time as an instructor and went back into the worksheet. Now Z1 was blank as it should have been but still all cells, A12:S31 and U12:U31, were locked and cells T12:T31 were unlocked.

It was a step forward but not quite what I need. What should have happened was when the student logs in and opens the worksheet, then cell Z1 should show a "1", like it did. At that time with a "1" in Z1 then all cells, A12:U31 and U12:U31 should have been locked and not accessible by the student. The student should only be able to access and enter data into cells U12:U31. Once the student closes that worksheet and an instructor logs in and opens up the worksheet, cell Z1 should show blank, which it does, but because Z1 is blank then all cells, A12:S31 and U12:U31, should be unlocked and editable by the instructor. The only locked cells at this point should be T12:T31.

And, of course, this process of locking/unlocking of those cells will change depending on whether a student or an instructor are logged in.

Thanks again for your efforts and I look forward to any updates you can give,

Dave
 
Upvote 0
Hi,

I set the Target range based on your first post

What I need to happen is when cell Z12="1" then cells A12:S31 and U12:U31 will become locked

Also, I guessed that the value in the range was being changed by formula? If this is not correct then Target should adjusted as required.

If however, value in range is being changed by a code process then worksheet_change event should be able to manage your requirement, also, it is always helpful if you share any code you have.

Dave
 
Upvote 0
Dmt32,

Dave, I have made a small change to the formula in cell Z1. Instead of a "1" when the student logs in it now shows the student's login username which can be found in worksheet "User List" and cell A56. This username will change as the class changes so I will need this new code to search for the contents of 'User List'!$A$56 instead of "1".

I'm sure this is most likely a very easy change for you.

Thanks again,

Dave
 
Upvote 0
Hi Dave,

I just noticed what my original post stated and that was an error on my part with the Z12, it should read Z1. You are also correct, there is a formula in cell Z1 that determines which cells are locked/unlocked. This is the formula in Z1: =IF('MASTER INDEX'!$AD$7='User List'!$A$56,'User List'!$A$56,"").

As far as other codes being used on this sheet, I only have one. Here are the codes on that worksheet now:

Private Sub Worksheet_Change(ByVal Target As Range)


Dim rLockable As Range
Dim cl As Range

Set rLockable = Range("T12:T31, T47:T66")
'If target is within the range then do nothing
If Intersect(rLockable, Target) Is Nothing Then Exit Sub
ActiveSheet.Unprotect Password:="22068"
For Each cl In Target
If cl.Value <> "" Then
check = MsgBox("Is this entry correct? This cell cannot be changed after entering a value.", vbYesNo, "Cell Lock Notification")
If check = vbYes Then
cl.Locked = True
Else
cl.Value = ""
End If
End If
Next cl
ActiveSheet.Protect Password:="22068"
End Sub


Private Sub Worksheet_Calculate()




Dim Target As Range

Set Target = Me.Range("Z1")

If CStr(Target.Value) <> Target.ID Then
Me.Unprotect Password:="22068"
Target.ID = CStr(Target.Value)
Me.Range("A12:S31, U12:U31").Locked = CBool(Target.Value = 1)
Me.EnableSelection = xlUnlockedCells
Me.Protect Password:="22068"
End If
End Sub


Thank you,

Dave
 
Upvote 0
Hi Dave,

I just noticed what my original post stated and that was an error on my part with the Z12, it should read Z1. You are also correct, there is a formula in cell Z1 that determines which cells are locked/unlocked. This is the formula in Z1: =IF('MASTER INDEX'!$AD$7='User List'!$A$56,'User List'!$A$56,"").

As far as other codes being used on this sheet, I only have one. Here are the codes on that worksheet now:

Private Sub Worksheet_Change(ByVal Target As Range)


Dim rLockable As Range
Dim cl As Range

Set rLockable = Range("T12:T31, T47:T66")
'If target is within the range then do nothing
If Intersect(rLockable, Target) Is Nothing Then Exit Sub
ActiveSheet.Unprotect Password:="22068"
For Each cl In Target
If cl.Value <> "" Then
check = MsgBox("Is this entry correct? This cell cannot be changed after entering a value.", vbYesNo, "Cell Lock Notification")
If check = vbYes Then
cl.Locked = True
Else
cl.Value = ""
End If
End If
Next cl
ActiveSheet.Protect Password:="22068"
End Sub


Private Sub Worksheet_Calculate()




Dim Target As Range

Set Target = Me.Range("Z1")

If CStr(Target.Value) <> Target.ID Then
Me.Unprotect Password:="22068"
Target.ID = CStr(Target.Value)
Me.Range("A12:S31, U12:U31").Locked = CBool(Target.Value = 1)
Me.EnableSelection = xlUnlockedCells
Me.Protect Password:="22068"
End If
End Sub


Thank you,

I have made a small change to the formula in cell Z1. Instead of a "1" when the student logs in it now shows the student's login username which can be found in worksheet "User List" and cell A56. This username will change as the class changes so I will need this new code to search for the contents of 'User List'!$A$56 instead of "1".

I'm sure this is most likely a very easy change for you.

Thanks again,

Dave

Dave

Hi,

I set the Target range based on your first post



Also, I guessed that the value in the range was being changed by formula? If this is not correct then Target should adjusted as required.

If however, value in range is being changed by a code process then worksheet_change event should be able to manage your requirement, also, it is always helpful if you share any code you have.

Dave
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,198
Members
448,554
Latest member
Gleisner2

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
Back
Top