Results 1 to 7 of 7

Thread: Locking or Unlocking validated cells depending on the previous cell

  1. #1

    Unhappy Locking or Unlocking validated cells depending on the previous cell



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Reveal questions.xls

    Hello All,


    This is my first post here so please be kind (I am no VBA expert that's for sure)


    I have a simple protected spreadsheet that has a list of questions in rows grouped into 10 sets of 3-5 questions with yes no drop-down (validation) answers in column C.


    SIMPLY (ha!), all I need to do for each question set is have the first "Yes/No" dropdown unlocked and the remaining questions in that set locked with a colour fill. With each "No" answer, the next cell in that column (row beneath) unlocks. The user answers "Yes" or "No" in question set 1 until they can go no further (i.e. they answer yes for a question) and thenmove onto question set 2, 3, 4 etc


    e.g. C2 "Yes/No" is unlocked, C3:C5 are locked with pale yellow fill. IF C2 = "No", C3 "Yes/No" = unlocked and C4:C5 remain locked. IF C2 = "Yes", C3:C5 Remain locked and so on


    This needs to be possible for all related question sets on the same sheet e.g C7 is unlocked and C8 is locked IF C7 = "No", C8 = unlocked. IF C8 = "Yes", C8 remains locked and so on.


    Something like this but vastly expanded:


    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    If [C2] = "No" Then
    ActiveSheet.Unprotect ("password")
    [C3].Locked = True
    [C3].Interior.ColorIndex = 0
    ActiveSheet.Protect ("password")
    Else
    ActiveSheet.Unprotect ("password")
    [C3:C5].Interior.ColorIndex = 36
    [C3:C5].ClearContents
    ActiveSheet.Protect ("password")
    End If
    End Sub

    Please does anyone have a simple-to-understand answer. I have seen many similar queries, but none that fit.


    Cheers

  2. #2
    Lock all of column C except the first question in each series and use this code

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)    If Target.Column = 3 Then
        
            Me.Unprotect "password"
            
            With Target.Offset(1, 0)
            
                If Not .MergeCells Then .Locked = False
            End With
            
            Me.Protect "password"
        End If
    End Sub

  3. #3
    Quote Originally Posted by Bob Phillips View Post
    Lock all of column C except the first question in each series and use this code

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)    If Target.Column = 3 Then
        
            Me.Unprotect "password"
            
            With Target.Offset(1, 0)
            
                If Not .MergeCells Then .Locked = False
            End With
            
            Me.Protect "password"
        End If
    End Sub
    Thanks Bob,

    This is great for unlocking cells if the previous cell is completed, can it be expanded so that only "No" answers unlock the cells?

  4. #4
    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    
        If Target.Column = 3 Then
            
            Me.Unprotect "password"
            
            With Target.Offset(1, 0)
            
                If Not .MergeCells Then
                
                    If Target.Value = "No" Then .Locked = False
                End If
            End With
        
            Me.Protect "password"
        End If
    End Sub

  5. #5
    Quote Originally Posted by Bob Phillips View Post
    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    
        If Target.Column = 3 Then
            
            Me.Unprotect "password"
            
            With Target.Offset(1, 0)
            
                If Not .MergeCells Then
                
                    If Target.Value = "No" Then .Locked = False
                End If
            End With
        
            Me.Protect "password"
        End If
    End Sub
    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 3 Then
    
    Me.Unprotect "a"
    
    With Target.Offset(1, 0)
    
    If Not .MergeCells Then
    
    If Target.Value = "No" Then .Locked = False
    .Interior.ColorIndex = 0
    
    If Target.Value = "Yes" Then .Locked = True
    
    .ClearContents
    End If
    
    
    End With
    
    Me.Protect "a"
    End If
    Thank you. I added a little bit and it seems to work perfectly!


    End Sub

  6. #6
    You can tidy-up/simplify that a bit

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    
        If Target.Column = 3 Then
        
            Me.Unprotect "a"
            
            With Target
            
                If Not .Offset(1, 0).MergeCells Then
             
                    .Offset(1, 0).ClearContents
                    .Interior.ColorIndex = 0
                    .Locked = .Value = "Yes"
                End If
            End With
            
            Me.Protect "a"
        End If
    End Sub

  7. #7

    Thanks again

    Quote Originally Posted by Bob Phillips View Post
    You can tidy-up/simplify that a bit

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    
        If Target.Column = 3 Then
        
            Me.Unprotect "a"
            
            With Target
            
                If Not .Offset(1, 0).MergeCells Then
             
                    .Offset(1, 0).ClearContents
                    .Interior.ColorIndex = 0
                    .Locked = .Value = "Yes"
                End If
            End With
            
            Me.Protect "a"
        End If
    End Sub
    I combined this with protection that doesn't allow the selection of locked cells and VBA code protection and the solution is solid. The only thing that would make it perfect is a way for the locked cells to always remain a pale yellow (colour 36) and unlocked ones white and also stand up to someone going back to the first questions in a section and chosing yes (i.e. it would clear the other entries below it, lock them and colour the cells back in). Too much?

Posting Permissions

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