View original (French)

VBA row locking based on the value of a cell in that row

maipouri973 Posted messages 4 Registration date   Status Member Last intervention   -  
maipouri973 Posted messages 4 Registration date   Status Member Last intervention   -
Hello,

I am looking in VBA to lock a row if the cell in column A of that row is empty, and this should apply to each row in the sheet.

Thank you for your help.

3 answers

  1.
    yclik Posted messages 3883 Registration date   Status Member Last intervention   1 613
     
    Hello
    there may be a solution with conditional formatting and two cell styles (locked and unlocked)
    0
    1. maipouri973 Posted messages 4 Registration date   Status Member Last intervention  
       
      Thank you for your response.
      But it is imperative to use VBA because I am already using data validation for certain cells in the row.
      0
  2. gyrus
     
    Hello,

    First, you need to protect the sheet.
    After selecting the entire sheet, lock all cells (right-click, Format Cells > Protection > check "Locked").
    Select column A, then unlock these cells (right-click, Format Cells > Protection > uncheck "Locked").
    Tab Review > Protect > Protect the sheet (Allow users to select unlocked cells).

    Place the following procedure in the sheet module:
    Private Sub Worksheet_Change(ByVal Target As Range)
     If Target.Count > 1 Then Exit Sub
     If Not Application.Intersect(Columns("A"), Target) Is Nothing Then
     ActiveSheet.Unprotect
     If Target = "" Then
     ActiveSheet.Range("B" & Target.Row).Resize(, Columns.Count - 1).Locked = True
     Else
     ActiveSheet.Range("B" & Target.Row).Resize(, Columns.Count - 1).Locked = False
     End If
     ActiveSheet.Protect
     End If
    End Sub


    Best regards
    0
    1. maipouri973 Posted messages 4 Registration date   Status Member Last intervention  
       
      Gyrus,

      With this procedure, I am getting the following error message:

      "The cell or chart is protected and read-only" and I am being asked to remove the protection.

      Thank you for your help.
      0
  3. gyrus
     
    When you protected the sheet, did you uncheck “Select locked cells”?
    0
    1. maipouri973 Posted messages 4 Registration date   Status Member Last intervention  
       
      Sorry for the late response. Yes, I unchecked "Select locked cells."
      0