2

I want to know my error in my VBA code in my Excel and want some cells to be blocked if a another cell value is true.

Private Sub Worksheet_Change(ByVal Target As Range)

If ActiveSheet.Cells(35, "CD").Value = True Then
    ActiveSheet.Range("R29:AA38").Locked = True
Else
    ActiveSheet.Range("R29:AA38").Locked = False
End If
End Sub

can you help me with that please!

Serjik
  • 10,543
  • 8
  • 61
  • 70
  • 2
    From what I have read in several Stack posts, you will need to protect the sheet, then UNLOCK cells that you want to allow edits to. Here is one post: http://stackoverflow.com/questions/17111648/how-to-lock-excel-cells-in-vba/18868676#18868676 – Wayne G. Dunn Dec 20 '15 at 04:58
  • 1
    You can how ever use the Data Validation to prevent the User from entering anything in the cells. – MGP Dec 20 '15 at 06:07
  • Don't use the [ActiveSheet property](https://msdn.microsoft.com/en-us/library/office/ff822753.aspx?f=255&MSPPError=-2147217396) in a [Worksheet_Change](https://msdn.microsoft.com/en-us/library/office/ff839775.aspx) event macro. The private worksheet code sheet knows which worksheet it is and you risk bad results if a value gets changed when it is not the active worksheet. –  Dec 20 '15 at 07:04

1 Answers1

0

Try this code:

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Ws As Worksheet
  Set Ws = Target.Worksheet
  Ws.Unprotect "pw" ' change to your password

  If CBool(Ws.Cells(35, "CD")) = True Then

    If MsgBox("Do you want to Lock the cells", vbYesNo) = vbYes Then
      Ws.Range("R29:AA38").Locked = True
    else
      Ws.Range("R29:AA38").Locked = False ' delete this line if you don't need it
      Application.EnableEvents = False
      Ws.Range("R29:AA38").ClearContents
      Application.EnableEvents = True
    end if

  else
    Ws.Range("R29:AA38").Locked = False
  end if

  Ws.Protect "pw" ' change to your password

End Sub
Fadi
  • 3,302
  • 3
  • 18
  • 41