CODEWITHSUNDEEP
excelvba
Anu
Anu

Reputation: 21

Need to disable the range of cells based on the drop down input from another cell

Is it possible to lock a particular range of cells based on the input from a dropdown in a row of data? For example, The row C5 contains Yes or No (which is selected/entered via a dropdown). If I choose No, the cells F11:F28 should be disabled also protect input fields so nothing can be entered.

ActiveSheet.Protect Contents:=True

after locking cells

Private Sub Worksheet_SelectionChange(ByVal Target As Range)  
    If Target.Count > 1 Then Exit Sub  
    Set myRng = Range("F11:F28")  
    For Each C In myRng
        If C.Address = Target.Address And ActiveSheet.Name = ActiveSheet.Range("C5").Value ="Yes" Then
            ActiveSheet.Unprotect Password:= "xyz"  
            Range("F11:F28").Locked = False  
            ActiveSheet.Protect Password:= "xyz"  
            Exit For  
        Else  
            ActiveSheet.Unprotect Password:= "xyz"  
            Range("F11:F28").Locked = True
            ActiveSheet.Protect Password:= "xyz"  
        End If  
    Next C  
End Sub

Expected Results:

When I choose No in cell C5 the cell range from F11:F28 should be disabled/protected so nothing can be entered.

Upvotes: 0

Views: 498

Answers (1)

Pᴇʜ
Pᴇʜ

Reputation: 57683

  1. Use the Worksheet_Change.
  2. Define your password as constant so you only have to change it once (in case you need to change it.
  3. Use the Application.Intersect method to check if Target and Me.Range("C5") intersect (that means C5 was changed).

The followinig should work.

Option Explicit

Private Const SHEET_PASSWORD As String = "xyz"

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Me.Range("C5")) Is Nothing Then
        Dim MyRng As Range
        Set MyRng = Me.Range("F11:F28")

        Me.Unprotect Password:=SHEET_PASSWORD

        If Me.Range("C5").Value = "Yes" Then
            MyRng.Locked = False
        Else
            MyRng.Locked = True
        End If

        Me.Protect Password:=SHEET_PASSWORD
    End If
End Sub

Note that this is case sensitive. If you want to allow both Yes and yes then use

If LCase(Me.Range("C5").Value) = "yes" Then

Upvotes: 0

Related Questions