Note: The other languages of the website are Google-translated. Back to English
Se connecter  \/ 
x
or
x
Inscrivez-vous  \/ 
x

or

Comment verrouiller ou déverrouiller des cellules en fonction des valeurs d'une autre cellule dans Excel?

Dans certains cas, vous devrez peut-être verrouiller ou déverrouiller des cellules en fonction des valeurs d'une autre cellule. Par exemple, vous devez déverrouiller la plage B1: B4 si la cellule A1 contient la valeur «Accepting»; et à verrouiller si la cellule A1 contient la valeur «Refus». Comment pouvez-vous y parvenir? Cet article peut vous aider.

Verrouiller ou déverrouiller des cellules en fonction des valeurs d'une autre cellule avec le code VBA


Verrouiller ou déverrouiller des cellules en fonction des valeurs d'une autre cellule avec le code VBA

Le code VBA suivant peut vous aider à verrouiller ou déverrouiller des cellules en fonction de la valeur d'une autre cellule dans Excel.

1. Cliquez avec le bouton droit sur l'onglet de la feuille (la feuille contenant les cellules que vous devez verrouiller ou déverrouiller en fonction des valeurs d'une autre cellule), puis cliquez sur Voir le code dans le menu contextuel.

2. Puis copiez et collez le code VBA suivant dans la fenêtre Code.

Code VBA: verrouiller ou déverrouiller les cellules en fonction des valeurs d'une autre cellule

Private Sub Worksheet_Change(ByVal Target As Range)
    If Range("A1") = "Accepting" Then
        Range("B1:B4").Locked = False
    ElseIf Range("A1") = "Refusing" Then
        Range("B1:B4").Locked = True
    End If
End Sub

3. appuie sur le autre + Q touches simultanément pour fermer le Microsoft Visual Basic pour applications fenêtre.

Désormais, lorsque vous entrez la valeur «Accepting» dans la cellule A1, la plage B1: B4 est déverrouillée.

Lors de la saisie de la valeur «Refus» dans la cellule A1, la plage spécifiée B1: B4 est verrouillée automatiquement.


Articles connexes:


Les meilleurs outils de productivité de bureau

Kutools for Excel résout la plupart de vos problèmes et augmente votre productivité de 80%

  • Réutilisation: Insérer rapidement formules complexes, graphiques et tout ce que vous avez utilisé auparavant; Crypter les cellules avec mot de passe; Créer une liste de diffusion et envoyer des e-mails ...
  • Barre Super Formula (modifiez facilement plusieurs lignes de texte et de formule); Disposition de lecture (lire et modifier facilement un grand nombre de cellules); Coller dans la plage filtrée...
  • Fusionner les cellules / lignes / colonnes sans perdre de données; Contenu des cellules divisées; Combiner des lignes / colonnes en double... Empêcher les cellules en double; Comparer les gammes...
  • Sélectionnez Dupliquer ou Unique Lignes; Sélectionnez les lignes vides (toutes les cellules sont vides); Super Find et Fuzzy Find dans de nombreux classeurs; Sélection aléatoire ...
  • Copie exacte Plusieurs cellules sans changer la référence de formule; Créer automatiquement des références à plusieurs feuilles; Insérer des puces, Cases à cocher et plus encore ...
  • Extrait du texte, Ajouter du texte, Supprimer par position, Supprimer l'espace; Créer et imprimer des sous-totaux de pagination; Conversion entre le contenu et les commentaires des cellules...
  • Super filtre (enregistrer et appliquer des schémas de filtrage à d'autres feuilles); Tri avancé par mois / semaine / jour, fréquence et plus; Filtre spécial par gras, italique ...
  • Combiner des classeurs et des feuilles de travail; Fusionner les tableaux en fonction des colonnes clés; Diviser les données en plusieurs feuilles; Conversion par lots xls, xlsx et PDF...
  • Plus de 300 fonctionnalités puissantes. Prend en charge Office / Excel 2007-2019 et 365. Prend en charge toutes les langues. Déploiement facile dans votre entreprise ou organisation. Essai gratuit de 30 jours. Garantie de remboursement de 60 jours.
onglet kte 201905

Office Tab apporte une interface à onglets à Office et simplifie considérablement votre travail

  • Activer l'édition et la lecture par onglets dans Word, Excel, PowerPoint, Publisher, Access, Visio et Project.
  • Ouvrez et créez plusieurs documents dans de nouveaux onglets de la même fenêtre, plutôt que dans de nouvelles fenêtres.
  • Augmente votre productivité de 50% et réduit des centaines de clics de souris chaque jour!
bas de cabine
Say something here...
symbols left.
You are guest
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Belcrai · 4 months ago
    I'm using this fine to lock Cells B-D when the value in A is entered. I need this to be replicated for almost 500 rows in 8 sheets. is there a way to do this without having to type 400 lines on code? i.e. if the value in a = "accepted" then columns b-D are locked for that row only
  • To post as a guest, your comment is unpublished.
    Maz · 6 months ago
    The code does not do anything when I tried it. Does this work on MS Office Professional 2010?

    Thanks.
    • To post as a guest, your comment is unpublished.
      crystal · 5 months ago
      Hi Maz,
      The code has been successfully tested in Excel 2010.
  • To post as a guest, your comment is unpublished.
    Jeffrey Scott · 8 months ago
    If my first column has strings can I lock that row specifically to the first column, where when I do a sort the full row will stay together on the sort?
    • To post as a guest, your comment is unpublished.
      crystal · 8 months ago
      Good day,
      Sorry can't help you for that.
  • To post as a guest, your comment is unpublished.
    Steffi · 1 years ago
    Hello,

    I need your favour. when i select No option from a cell i want the columns below to be locked/greyed out.

    Is that possible? I tried this formula but dint work

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Range("D90") = "Yes" Then
    Range("C94:F104").Locked = False
    ElseIf Range("D90") = "No" Then
    Range("C94:F104").Locked = True
    End If
    End Sub
    • To post as a guest, your comment is unpublished.
      crystal · 1 years ago
      Hi Steffi,
      The code works well.
      If you want to lock the range of cells to prevent editing, you need to protect the worksheet manually after selecting No option in D90.
      And as D90 is in the range C94:F104, after protecting the worksheet, D90 can't be edited either.
  • To post as a guest, your comment is unpublished.
    Kyle · 1 years ago
    I copied everything from above into a sheet. It worked for a second and now I'm getting the error 'Unable to set the Locked property of the Range class'. I even opened a whole new sheet and copied your example verbatim. Any idea whats going on?
  • To post as a guest, your comment is unpublished.
    satya · 1 years ago
    Hi please any one help me for VBA code .
    if i select from drop down in cell Range(A1:A1000) "abc" then not lock Range(D1:D1000) to (F1:F1000)

    if i select from drop down in cell Range(A1:A1000) "abc" then not lock Range(D1:D1000) to (F1:F1000)

    if i select from drop down in cell Range(A1:A1000) "abc" then lock Range(D1:D1000) to (F1:F1000)

    i.e respective cell A1 for D1 to F1

    A2 for for D2 to F2
  • To post as a guest, your comment is unpublished.
    benthe · 1 years ago
    hi

    if I need to lock a cell on sheet 2 (cell C4) based on a claim from sheet 1 (cell C1),
    if "no" on sheet 1 C4; sheet 2 must be locked and it must transfer the value from sheet 1 C4,
    to sheet 2 C4.

    if "yes" on sheet 1, I must be able to type in the cell on sheet 2

    Thanks Benthe
  • To post as a guest, your comment is unpublished.
    Chandrakanth · 1 years ago
    Hi
    I have just tried using the code above
    and it says type 13 error when I try to use it.
    could you help me out with this?

    thank you
    • To post as a guest, your comment is unpublished.
      crystal · 1 years ago
      Hi,
      Which Excel version are you using?
  • To post as a guest, your comment is unpublished.
    cricontidj@gmail.com · 2 years ago
    Hi, could you kindly check the reason why it doesn't work?

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Range("A3:A37").Value <> "" Then
    Range("B3:B37").Locked = True
    ElseIf Range("A3:A37") = "" Then
    Range("B3:B37").Locked = False
    End If

    If Range("B3:B37").Value <> "" Then
    Range("A3:A37").Locked = True
    ElseIf Range("B3:B37") = "" Then
    Range("A3:A37").Locked = False
    End If

    End Sub


    Thank you very much in advance!!!
  • To post as a guest, your comment is unpublished.
    Zk · 2 years ago
    Is it possible to lock a cell, when it reaches a certain value?
  • To post as a guest, your comment is unpublished.
    mfuller73 · 2 years ago
    How would the code be if I wanted to lock a block of cells (Rows 6, 7, and 8/Letters D through U as well as cells F5 and J5) and have them unlock when I put an "X" in cell E5? Thanks in advance!
    • To post as a guest, your comment is unpublished.
      crystal · 2 years ago
      Hi MitchyII,
      Do you mean the specified block of cells have been locked manually in advance and just want to unlock them by typing an "X" in cell E5?
      If remove "X" from cell E5, you do want to lock the ranges again?
      I need more details of the problem.
      Thank you for your comment.
      • To post as a guest, your comment is unpublished.
        Jennifer Cornell · 1 years ago
        Hi Mitchyll (or anyone), building off of Crystal. I have manually locked all cells and based on the information in column G, I would like them to remain locked or unlock. Example cells in column H should ONLY be unlocked if "c/p" is placed in the preceding cell in column G
  • To post as a guest, your comment is unpublished.
    sarcotec · 3 years ago
    Would you kindly advice me on how to correct this? Thank you in advance.

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Range("A:A") = "SLOW MOVING" Then
    Range("B:B").Locked = True
    ElseIf Range("A:A") = "OVER STOCK" Then
    Range("B:B").Locked = True
    ElseIf Range("A:A") = "NORMAL" Then
    Range("B:B").Locked = False
    End If
    End Sub
    • To post as a guest, your comment is unpublished.
      Noexpert · 3 years ago
      Not being a VB expert i would say you have too many "Elseif" - if you change them all to just IF except for the last one then hopefully that will work.
      Basically If X do this, If Y do this, if Z do this, if none of those - do this.
  • To post as a guest, your comment is unpublished.
    Joe · 3 years ago
    Can you guide me on what's wrong here please? Thank you in advance.

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Range("K:K") = "OVER STOCK" Then
    Range("S:S").Locked = True
    ElseIf Range("K:K") = "SLOW MOVING" Then
    Range("S:S").Locked = True
    ElseIf Range("K:K") = "NORMAL" Then
    Range("S:S").Locked = False
    ElseIf Range("K:K") = "SHORTAGE" Then
    Range("S:S").Locked = False
    End If
    End Sub
  • To post as a guest, your comment is unpublished.
    KB · 3 years ago
    I prepare a warehouse stock management in excel template.To deliver a stock i have to issue a gate pass.I want to each gate pass,corresponding data will be updated in daily stock take page.With the change of gate pass serial no,the row will be locked and next will be filled up.
    • To post as a guest, your comment is unpublished.
      crystal · 3 years ago
      Good Day,
      Would be nice if you can upload your workbook here. Thank you for your comment.
  • To post as a guest, your comment is unpublished.
    veandor@gmail.com · 3 years ago
    Hi,
    I am really new to this.
    I have been trying to put an invoicing system together in excel.
    I created 3 sheets.
    1. Invoice template (Invoice)- Just a generic invoice that is sent to my agents weekly.

    2. A data sheet (Data sheet) to be exact - where the invoice can read the company name adress etc, so if anything changes the invoice will be automatically updated.

    3. A calendar tab (Calendar 2018) to be exact - that is referenced in the invoice template, and puts the corresponding date and invoice number on the actual invoices.

    What I want to do.
    The calendar tab would be my main page, I added a status drop down cell for each week with options "Active" and "Closed". I would like to Lock the whole "Invoice" tab if the corresponding cell is set to "Closed".

    I hope you guys understand what I am trying to do.
    Thanks in advance.
    • To post as a guest, your comment is unpublished.
      crystal · 3 years ago
      Dear Ando Veres.
      The below VBA code can help you. Please place the code into the sheet code window of Calendar 2018 change A1 to your drop down cell. Thank you.

      Private Sub Worksheet_Change(ByVal Target As Range)
      Dim xRg As Range
      On Error Resume Next
      Set xRg = Intersect(Target, Range("A1"))
      If xRg Is Nothing Then Exit Sub
      If Target.Validation.Type >= 0 Then
      If Target.Value = "Closed" Then
      Sheets("Data Sheet").Protect
      ElseIf xRg.Value = "Active" Then
      Sheets("Data Sheet").Unprotect
      End If
      End If
      End Sub
  • To post as a guest, your comment is unpublished.
    Mira · 3 years ago
    Hi! Can someone help me? I have to lock/freeze a cell. That cell is linked to another one and has a value which is changing every minute. What I what to do is to keep the value for a certain minute/ hour. How can I do that without copying it and paste it as a value?
    • To post as a guest, your comment is unpublished.
      crystal · 3 years ago
      Dear Mira,
      Sorry can’t help with this, you can post your question in our forum: https://www.extendoffice.com/forum.html to get more Excel supports from our professional.
  • To post as a guest, your comment is unpublished.
    Horace · 3 years ago
    can some one correct this pls>>>

    Private Sub Worksheet_Change(ByVal Target As Range)
    For i = 7 To 100
    If Range("Cells(D, i)") = "Loan" Then
    Range("Cells(V, i):Cells(X, i)").Locked = True
    ElseIf Range("Cells(D, i)") = "Savings" Then
    Range("Cells(Q, i):Cells(U, i)").Locked = True
    Range("Cells(W, i):Cells(X, i)").Locked = True
    ElseIf Range("Cells(D, i)") = "ShareCap" Then
    Range("Cells(Q, i):Cells(U, i)").Locked = True
    Range("Cells(V, i)").Locked = True
    End If
    Next i
    End Sub
  • To post as a guest, your comment is unpublished.
    margie · 3 years ago
    Hi! need some advise.
    Is there a way not to allow a cell to be updated unless it has satisfied a condition on another cell?
    Sample: if the cell A is not updated it will not allow me to change the value of cell B to complete.

    Appreciate the feedback.
    Thanks!
    • To post as a guest, your comment is unpublished.
      crystal · 3 years ago
      Dear Margie,
      Please try below VBA code.

      Dim PreVal As String
      Dim NextVal As String
      Private Sub Worksheet_Activate()
      PreVal = Range("A1")
      NextVal = Range("A1")
      End Sub
      Private Sub Worksheet_Change(ByVal Target As Range)
      If (Target.Count = 1) And (Target.Address = "$A$1") Then
      NextVal = Range("A1")
      End If
      End Sub
      Private Sub Worksheet_SelectionChange(ByVal Target As Range)
      If Target.Count = 1 Then
      If Target.Address = "$A$1" Then
      PreVal = Range("A1")
      ElseIf (Target.Address = "$B$1") Then
      If PreVal = NextVal Then
      Application.EnableEvents = False
      Range("A1").Select
      Application.EnableEvents = True
      End If
      End If
      End If
      End Sub
  • To post as a guest, your comment is unpublished.
    SHAINA · 3 years ago
    WHAT WILL BE THE CODE IF I WANT TO LOCK CELL E1, E2, E3 .............. FOR SPECIFIC TEXT (LETS SAY "P") ON CELL B1, B2, B3.................RESPECTIVELY.

    THANKS IN ADVANCE
    • To post as a guest, your comment is unpublished.
      crystal · 3 years ago
      Good Day
      Please try below VBA script.

      Private Sub Worksheet_Change(ByVal Target As Range)
      If Target.Count = 1 Then
      If Target.Address = Range("A1").Address And Target.Value = "A" Then
      Range("B1").Locked = True
      ElseIf Target.Address = Range("A2").Address And Target.Value = "A" Then
      Range("B2").Locked = True
      ElseIf Target.Address = Range("A3").Address And Target.Value = "A" Then
      Range("B3").Locked = True
      End If
      End If
      End Sub
  • To post as a guest, your comment is unpublished.
    Kristoffer · 3 years ago
    Hello,

    I have tried your code and edit a little bit, but i can't work out what i do wrong here?

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Range("A40") <> "" Then
    Range("D40:E40").Locked = False
    ElseIf Range("A40") = "" Then
    Range("D40:E40").Locked = True
    End If
    End Sub


    My though about it was if there is nothing in it (A40). Then i want to locked by VBA. If A40 contain something, then i want it to be unlocked. I hope you can see the sense of it.


    Regards Kristoffer
    • To post as a guest, your comment is unpublished.
      crystal · 3 years ago
      Good Day,
      There is nothing wrong with your code. It works well for me.
      • To post as a guest, your comment is unpublished.
        KeeranB · 3 years ago
        Hi. I too cannot get this code to work. It does absolutely nothing. As if the code isn't even there?? I'm very new to VBA and have a basic understanding on it. Is this code being run as-is, or does it have to have stuff added to it as well for it to run? Or turned into a Macro (which I don't really get why because that's a recording of instructions, on my understanding of them)
  • To post as a guest, your comment is unpublished.
    leo · 3 years ago
    please can someone help me with the following.
    I want to insert pictures of student in one sheet, appears in another sheet based on their names
    To create a navigating plane to assist users
    To assign a particular sheet(s) to a user
    To create an interface for the workbook
    To create a login page
    • To post as a guest, your comment is unpublished.
      crystal · 3 years ago
      Dear leo,
      Any question about Excel, please don’t hesitate to post in our forum: https://www.extendoffice.com/forum.html.
      You will get more supports about Excel from our Excel professional.
  • To post as a guest, your comment is unpublished.
    Ant · 4 years ago
    Hi, I'm trying to achieve this, but I get an error that VBA is unable to set the Locked property of the Range class if the sheet has been protected. Unprotecting the sheet will then negate the cell being locked.

    How to get around this?

    Thanks for any help.
    • To post as a guest, your comment is unpublished.
      crystal · 3 years ago
      Dear Ant,
      The below VBA code can help you solve this probem. Thank you for your comment.

      Private Sub Worksheet_Activate()
      If Not ActiveSheet.ProtectContents Then
      Range("A1").Locked = False
      Range("B1:B4").Locked = False
      End If
      End Sub
      Private Sub Worksheet_SelectionChange(ByVal Target As Range)
      Dim xRg As Range, xRgA As Range
      On Error Resume Next
      Application.EnableEvents = False
      Set xRg = Range("B1:B4")
      Set xRgA = Range("A1")
      If Intersect(Target, xRg).Address <> Target.Address _
      Or xRgA = "Accepting" Then
      Application.EnableEvents = True
      Exit Sub
      ElseIf ActiveSheet.ProtectContents _
      And Intersect(Target, xRg) = Target _
      And xRgA.Value = "Refusing" Then
      xRgA.Select
      End If
      Application.EnableEvents = True
      End Sub
      • To post as a guest, your comment is unpublished.
        Christian · 3 years ago
        Hi,

        Is it possible to have this VBA lock one set of cells/unlock another based on this? For example Range B1:B4 is unlocked and C1:C4 is locked for "accepting" and then B1:B4 is locked and C1:C4 is unlocked for "refusing"?


        Thanks,
        Christian
        • To post as a guest, your comment is unpublished.
          crystal · 3 years ago
          Dear Christian,
          Is your worksheet protected?
    • To post as a guest, your comment is unpublished.
      Henry · 3 years ago
      You'll want to use the interface line in the workbook so when you open the file, it protects the sheets but allow macros to make changes anyway;

      Private Sub Workbook_Open() 'This goes into "ThisWorkbook"

      Worksheets("Order Tool").Protect Password:="Pwd", UserInterFaceOnly:=True

      End Sub
    • To post as a guest, your comment is unpublished.
      Memo · 3 years ago
      Did you resolved? I have the same problem
      • To post as a guest, your comment is unpublished.
        crystal · 3 years ago
        Dear Memo,
        Please try the below VBA code.

        Private Sub Worksheet_Activate()
        If Not ActiveSheet.ProtectContents Then
        Range("A1").Locked = False
        Range("B1:B4").Locked = False
        End If
        End Sub
        Private Sub Worksheet_SelectionChange(ByVal Target As Range)
        Dim xRg As Range, xRgA As Range
        On Error Resume Next
        Application.EnableEvents = False
        Set xRg = Range("B1:B4")
        Set xRgA = Range("A1")
        If Intersect(Target, xRg).Address <> Target.Address _
        Or xRgA = "Accepting" Then
        Application.EnableEvents = True
        Exit Sub
        ElseIf ActiveSheet.ProtectContents _
        And Intersect(Target, xRg) = Target _
        And xRgA.Value = "Refusing" Then
        xRgA.Select
        End If
        Application.EnableEvents = True
        End Sub
  • To post as a guest, your comment is unpublished.
    Sheetal Rao · 4 years ago
    How can lock/unlock a variable cell e.g. when cell is [=INDEX(A16:L35,MATCH(W5,A16:A35,0),MATCH("PAY",A16:L16,0))]
    • To post as a guest, your comment is unpublished.
      Mohammed Mandlaywala · 3 years ago
      I want a simple VBA command which I am unable to figure out please help
      If cell A1 is Balnk then Cell A2 is locked and If Cell A1 contains any Value then Cell A2 is unlocked
      Similarly if cell A2 is blank then Cell A3 is locked and if Cell A2 contains any value then Cell A3 is unlocked
      and so on as many cells as per requirement in any part of the sheet.