Lock Cell/Cell Range based on Cell value

[shamim]

Hi,

I want to lock cell ref. B2 and cell range ref. C1 to C10 based on cell value selected in A1. Now, A1 column has validation, when Apple is selected B2 has to be locked from editing and when Mango is selected cell range C1 to C10 has to be locked from editing.

Please see the attached workbook for your ready reference.

Regards,
Uday

[shamim]

Please help!!

[Aussiebear]

How did you propose to then unlock the cell B2 and Range C1:C10?

[Artik]

It is true that not enough data about other cells of the sheet, but let's start with such a procedure (place in the sheet module, not in the standard module!)

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Me.Range("A1")) Is Nothing Then
        Me.Unprotect
        Me.Cells.Locked = False


        If Me.Range("A1").Value = "Apple" Then
            Me.Range("B2").Locked = True
            Me.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _
                       False
        ElseIf Me.Range("A1").Value = "Mango" Then
            Me.Range("C1:C10").Locked = True
            Me.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _
                       False
        End If
    End If
End Sub

Artik

[Aussiebear]

Try adding this in a sheet module along with the code suggested by Artik. The purpose here is to give you an unlock option by double clicking cell A2.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target as Range, Cancel as Boolean)
   If not Intersect(Target , Me.Range("A2")) then Exit Sub  <---- change this to suit your needs     
      If(And(Me.Range("B2" & "C1:C10").Locked = True Then
      Me.Range("B2" & "C1:C10").Locked = False
      End If
   End If
End Sub

[Artik]

Aussiebear, probably something wrong with your code.

Artik

[shamim]

Hi Artik,

B2 is not locking as per the condition set. please help.

Regards,
Uday

[shamim]

Hi Aussiebear,

Thanks for your response, however the code is not working and giving compiler error.

Regards,
Uday

[Artik]

B2 is not locking as per the condition set.

If you select "Apple" in cell A1, access to cell B2 is blocked. If you select "Mango", then B2 is unlocked and the range C1: C10 is blocked. If you clear cell A1 - all cells are unlocked.
Is this not consistent with the assumptions?

Artik

[shamim]

Hi Artik,

Thanks for your quick response. If I select the Apple in A1, access to cell B2 is not blocking.

Regards,
Uday,

[Artik]

Please attach the test file in which there is a B2 blocking problem. For me, the code shown works.

Artik

[shamim]

Hi Artik,

I have attached the file. please check it.

Regards,
Uday

[Artik]

For me, it works according to my earlier assumptions.

Artik