I want to block some cells when another cell value is true

Question

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!

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
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

Fadi · Accepted Answer · 2015-12-21T03:08:27.540

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

edited Dec 21 '15 at 03:08

answered Dec 21 '15 at 02:06

Fadi

3,302
3
18
41

and if i want to add a msgbox yesno when the value is true and if the user click yes, the cell are protect, and if the user click no, it will delete the cells and will not be protected... how i add that ? – Jean-François Meehan Dec 21 '15 at 02:35
@Jean-FrançoisMeehan , you want to clear the contents of Range("R29:AA38") if user click no? – Fadi Dec 21 '15 at 02:42
@Jean-FrançoisMeehan , ok I will change the code now – Fadi Dec 21 '15 at 02:49
that would be great!! – Jean-François Meehan Dec 21 '15 at 03:06
thx, Fadi... it is normal that every time i enter a value in another cell, it keep asking me the msgbox? if yes, how can it ask me just once when i answer yes... – Jean-François Meehan Dec 21 '15 at 16:38

I want to block some cells when another cell value is true

1 Answers1