Locking or Unlocking validated cells depending on the previous cell

B

Biajw

New member
Joined
Jun 5, 2014
Messages
4
Reaction score
0
Points
0
View attachment 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
 
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
 
Bob Phillips said:
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
Click to expand...

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?
 
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
 
Bob Phillips said:
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
Click to expand...

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
 
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
 
Thanks again

Bob Phillips said:
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
Click to expand...

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?
 
You must log in or register to reply here.
Back
Top