View original (French)

Macro to prevent modification of filled cells

Solved
Torre -  
 Torre -
Hello,

I would like to program a macro so that we cannot modify filled cells, but we can write in empty cells of the same table, and I would like to be able to modify the filled cells only with a password.

Could you please help me?

2 answers

  1.
    Mike-31 Posted messages 18405 Registration date   Status Contributor Last intervention   5 147
     
    Hello,

    If you want to make the range A2:G30 accessible and lock the cells in this range after entry but editable with a password
    start by selecting the range/right-click/cell format/protection/uncheck locked

    still selected range/tab Review/changes module/Allow editing ranges/New/in refers to cells you should have the boundaries of the selected range/enter a password to access modification after protection, for example toto

    in the sheet properties, right-click on your sheet tab/View code/paste this code

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect([A2:G30], Target) Is Nothing And Target.Count = 1 Then
    ActiveSheet.Unprotect Password:="titi"
    Target.Locked = True
    ActiveSheet.Protect Password:="titi"
    End If
    End Sub

    go back to your sheet, protect it with a password that may be different from the one for modification, in the example I chose titi make sure that access to locked and unlocked cells is checked in your sheet's protection options
    --
    A+
    Mike-31

    A period of failure is a perfect time to sow the seeds of knowledge.
    0
    1. Torre
       
      It's great, thank you, but I would like to use this password system only to prevent modifications to already filled cells. Let me explain, these tables will contain information written by different teams, and I do not want one team to modify the information of other teams. I would like the only one able to modify the information to be the manager, so the system should only protect filled cells and not empty cells.
      In short, when we click on an empty cell, there should be no password protection, but when we fill the cell, we should be required to enter a password to modify it.
      0
    2. Torre
       
      ```vb Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim plage As Range Set plage = Range("A1:H1") If Not Application.Intersect(Target, plage) Is Nothing Then If Not IsEmpty(Target) Then Dim password As String password = InputBox("Veuillez entrer le mot de passe :") If password = "votre_mot_de_passe" Then ' Autoriser les modifications Else MsgBox "Mot de passe incorrect. Impossible de sélectionner cette cellule." Target.Offset(1, 0).Select End If End If End If End Sub ```
      0