CODEWITHSUNDEEP
excelvbaexcel-formula
Sibbie88
Sibbie88

Reputation: 1

How Can I lock cells in Excel based on the value of another?

For instance, I have a Cell G4 with a drop down list of 1,2, and 4. I want to lock out cells in row 9 based on the choice in G4. For example, if the user selects “1” as the value for G4, I want them to only be able to change the value of cell G9, and lock cells H9, I9, and J9, setting them to a value of “0” if possible. And then were you to change the value of G4 to “2” it would then unlock cell H9 but leave I9 and J9 locked, with those two only unlocking if the value “4” is selected in cell G4.

I have tried different methods using data validation that have not achieved the desired result. I have also tried a few different VBA codes that have also not yielded the desired result.

Upvotes: 0

Views: 294

Answers (1)

JB-007
JB-007

Reputation: 2525

Here/screenshot/links refer:

1. Set up

  • All cells except G4 are locked - see here
  • Sheet protected pw=1234, 'select locked and unlocked cells' - see here
  • Cell G4 with validation list '0,1,2,3' see here

2. VBA code

  • Open VB, in project explorer select sheet 1 and paste following code

Sheet1 - Project explorer

Private Sub Worksheet_Change(ByVal Target As Range)
'https://www.teachexcel.com/excel-tutorial/run-a-macro-when-a-specific-cell-changes-in-excel_1592.html#method1
    If Not Intersect(Target, Range("G4")) Is Nothing Then
        Call locks
    End If
End Sub


Sub locks()
'
' Macro2 Macro
'

'
    ActiveSheet.Unprotect Password:="1234"
    Range("G9:J9").Select
    Selection.Locked = True
    Selection.FormulaHidden = False
    which = Range("g4").Value
    Select Case which
        Case 1
            Range("G9").Select
        Case 2
            Range("H9").Select
        Case 3
            Range("I9:J9").Select
    End Select
    
    Selection.Locked = False
    Selection.FormulaHidden = False
    'ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:="1234"

End Sub

Sample GIF

Upvotes: 1

Related Questions