Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

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

  1. #1
    Board Regular
    Join Date
    May 2016
    Posts
    95
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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 by dsheard2015; Jun 15th, 2017 at 01:37 AM.

  2. #2
    Board Regular
    Join Date
    Jul 2012
    Location
    Hampshire, UK
    Posts
    3,764
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

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

    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

  3. #3
    Board Regular
    Join Date
    May 2016
    Posts
    95
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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

  4. #4
    Board Regular
    Join Date
    Jul 2012
    Location
    Hampshire, UK
    Posts
    3,764
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

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

    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

  5. #5
    Board Regular
    Join Date
    May 2016
    Posts
    95
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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

  6. #6
    Board Regular
    Join Date
    May 2016
    Posts
    95
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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

  7. #7
    Board Regular
    Join Date
    May 2016
    Posts
    95
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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

    Quote Originally Posted by dmt32 View Post
    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

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com