CODEWITHSUNDEEP
vbaexcel
sady
sady

Reputation: 301

Locking cell based on value on other cell

Please find the below code. I am not sure where am I going wrong in my code. My purpose is to check the value in D44 cell(drop down list) and based on the value lock/unlock D45 cell.

Values in D44 are NO,13 fracto,18 fracto,Any Other Fracto" User should be able to edit the cell D45 only if the D44 cell value is set to "Any Other Fracto".

Sub TheSelectCase()
    Select Case Range("D44").Value
          Case "Any Other Fracto"
          Range("D45").Locked = False
          Range("D45").Activate

          Case = "NO" 
           Range("D45").Locked = True
       Range("D45").Clear

      Case <> "NO" Or "Any Other Fracto"
      Range("D45").Locked = True
    End Select
End Sub

Upvotes: 2

Views: 120

Answers (1)

David Zemens
David Zemens

Reputation: 53623

Unless the dropdown is an ActiveX control, you'll need to do this via Worksheet_Change event handler, and also, you only have two cases: 1) A case where user is allowed to edit ("Any other fracto") and 2) any other value in which the user is not allowed to edit.

Assuming you're using a Validation List, do this in the worksheet's Worksheet_Change event:

Private Sub Worksheet_Change(ByVal Target As Range)

    'Exit on any other cell
    If Intersect(Target, Range("D44")) Is Nothing Then Exit Sub
    Application.EnableEvents = False
    Dim inputCell As Range
    Set inputCell = Range("D45")
        Select Case Trim(LCase(Target.Value))
            Case "any other fracto"
                inputCell.Locked = False
                inputCell.Activate
            Case Else
            'This handles **ANY** other value in the dropdown
                inputCell.Locked = True
                InputCell.Clear
        End Select

   Application.EnableEvents = True
End Sub

Upvotes: 3

Related Questions