Wie sperre oder entsperre ich Zellen basierend auf Werten in einer anderen Zelle in Excel?

In einigen Fällen müssen Sie möglicherweise Zellen basierend auf Werten in einer anderen Zelle sperren oder entsperren. Beispielsweise muss der Bereich B1: B4 entsperrt werden, wenn die Zelle A1 den Wert "Akzeptieren" enthält. und zu sperren, wenn Zelle A1 den Wert "Ablehnen" enthält. Wie können Sie dies erreichen? Dieser Artikel kann Ihnen helfen.

Sperren oder Entsperren von Zellen basierend auf Werten in einer anderen Zelle mit VBA-Code

Sperren oder Entsperren von Zellen basierend auf Werten in einer anderen Zelle mit VBA-Code

Mit dem folgenden VBA-Code können Sie Zellen basierend auf dem Wert in einer anderen Zelle in Excel sperren oder entsperren.

1. Klicken Sie mit der rechten Maustaste auf die Registerkarte Blatt (das Blatt mit Zellen, die Sie basierend auf Werten in einer anderen Zelle sperren oder entsperren müssen), und klicken Sie auf Code anzeigen aus dem Kontextmenü.

2. Kopieren Sie anschließend den folgenden VBA-Code und fügen Sie ihn in das Codefenster ein.

VBA-Code: Sperren oder Entsperren von Zellen basierend auf Werten in einer anderen Zelle

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. Drücken Sie die Taste Andere + Q Tasten gleichzeitig zum Schließen der Microsoft Visual Basic für Applikationen Fenster.

Wenn Sie von nun an den Wert "Akzeptieren" in Zelle A1 eingeben, wird der Bereich B1: B4 entsperrt.

Bei Eingabe des Wertes „Ablehnen“ in Zelle A1 wird der angegebene Bereich B1: B4 automatisch gesperrt.

In Verbindung stehende Artikel:

Die besten Tools für die Office-Produktivität

Kutools für Excel löst die meisten Ihrer Probleme und erhöht Ihre Produktivität um 80%

Wiederverwendung: Schnell einfügen komplexe Formeln, Diagramme und alles, was du vorher benutzt hast; Zellen verschlüsseln mit Passwort; Mailingliste erstellen und E-Mails senden ...
Super Formula Bar (leicht mehrere Textzeilen und Formeln bearbeiten); Layout lesen (leichtes Lesen und Bearbeiten einer großen Anzahl von Zellen); In gefilterten Bereich einfügen...
Zellen / Zeilen / Spalten zusammenführen ohne Daten zu verlieren; Inhalt geteilter Zellen; Kombinieren Sie doppelte Zeilen / Spalten... doppelte Zellen verhindern; Bereiche vergleichen...
Wählen Sie Duplizieren oder Eindeutig Reihen; Wählen Sie Leere Zeilen (alle Zellen sind leer); Super Find und Fuzzy Find in vielen Arbeitsmappen; Zufällige Auswahl ...
Exakte Kopie Mehrere Zellen ohne Änderung der Formelreferenz; Referenzen automatisch erstellen zu mehreren Blättern; Aufzählungszeichen einfügen, Kontrollkästchen und mehr ...
Text extrahieren, Text hinzufügen, Nach Position entfernen, Leerzeichen entfernen;; Paging-Zwischensummen erstellen und drucken; Inhalt und Kommentare zwischen Zellen konvertieren...
Superfilter (Speichern und Anwenden von Filterschemata auf andere Blätter); Erweiterte Sortierung nach Monat / Woche / Tag, Häufigkeit und mehr; Spezialfilter fett, kursiv ...
Kombinieren Sie Arbeitsmappen und Arbeitsblätter;; Tabellen basierend auf Schlüsselspalten zusammenführen; Daten in mehrere Blätter aufteilen; Batch-Konvertierung von xls, xlsx und PDF...
Mehr als 300 leistungsstarke Funktionen. Unterstützt Office / Excel 2007-2019 und 365. Unterstützt alle Sprachen. Einfache Bereitstellung in Ihrem Unternehmen oder Ihrer Organisation. Vollständige Funktionen 30 Tage kostenlose Testversion. 60 Tage Geld-zurück-Garantie.

Lesen Sie mehr... Kostenfreier Download... Kauf...

Office Tab Bringt die Oberfläche mit Registerkarten in Office und erleichtert Ihnen die Arbeit erheblich

Aktivieren Sie das Bearbeiten und Lesen von Registerkarten in Word, Excel und PowerPoint, Publisher, Access, Visio und Project.
Öffnen und erstellen Sie mehrere Dokumente in neuen Registerkarten desselben Fensters und nicht in neuen Fenstern.
Steigert Ihre Produktivität um 50% und reduziert täglich Hunderte von Mausklicks für Sie!

Lesen Sie mehr... Kostenfreier Download... Kauf...

44 Comments
Login

Sort by Newest
- Best
- Popular
- Newest
- Oldest

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.
Maz · 1 months ago
The code does not do anything when I tried it. Does this work on MS Office Professional 2010?

Thanks.
- Reply
- To post as a guest, your comment is unpublished.
  crystal · 1 months ago
  
  Hi Maz,
  The code has been successfully tested in Excel 2010.
  
  Reply
To post as a guest, your comment is unpublished.
Jeffrey Scott · 3 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?
- Reply
- To post as a guest, your comment is unpublished.
  crystal · 3 months ago
  
  Good day,
  Sorry can't help you for that.
  
  Reply
To post as a guest, your comment is unpublished.
Steffi · 7 months 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
- Reply
- To post as a guest, your comment is unpublished.
  crystal · 7 months 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.
  
  Reply
To post as a guest, your comment is unpublished.
Kyle · 10 months 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?
- Reply
To post as a guest, your comment is unpublished.
satya · 10 months 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
- Reply
To post as a guest, your comment is unpublished.
benthe · 11 months 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
- Reply
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
- Reply
- To post as a guest, your comment is unpublished.
  crystal · 1 years ago
  
  Hi,
  Which Excel version are you using?
  
  Reply
To post as a guest, your comment is unpublished.
cricontidj@gmail.com · 1 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!!!
- Reply
To post as a guest, your comment is unpublished.
Zk · 1 years ago
Is it possible to lock a cell, when it reaches a certain value?
- Reply
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!
- Reply
- 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.
  
  Reply
  
  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
  
  Reply
To post as a guest, your comment is unpublished.
sarcotec · 2 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
- Reply
- To post as a guest, your comment is unpublished.
  Noexpert · 2 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.
  
  Reply
To post as a guest, your comment is unpublished.
Joe · 2 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
- Reply
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.
- Reply
- 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.
  
  Reply
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.
- Reply
- 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
  
  Reply
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?
- Reply
- 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.
  
  Reply
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
- Reply
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!
- Reply
- 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
  
  Reply
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
- Reply
- 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
  
  Reply
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
- Reply
- 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.
  
  Reply
  
  To post as a guest, your comment is unpublished.
  
  KeeranB · 2 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)
  
  Reply
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
- Reply
- 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.
  
  Reply
To post as a guest, your comment is unpublished.
Ant · 3 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.
- Reply
- 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
  
  Reply
  
  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
  
  Reply
  
  To post as a guest, your comment is unpublished.
  
  crystal · 3 years ago
  
  Dear Christian,
  Is your worksheet protected?
  
  Reply
- 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
  
  Reply
- To post as a guest, your comment is unpublished.
  Memo · 3 years ago
  
  Did you resolved? I have the same problem
  
  Reply
  
  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
  
  Reply
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))]
- Reply
- 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.
  
  Reply

Einloggen auf Ihr Konto

Passwort zurücksetzen