Microsoft Excel

Herbers Excel/VBA-Archiv

Zellen sperren wenn Bedingung in anderer Zelle erf


Betrifft: Zellen sperren wenn Bedingung in anderer Zelle erf von: Max
Geschrieben am: 13.09.2019 08:16:30

Guten Morgen allerseits

In einer Datei möchte ich gerne Zellen in Abhängigkeit von anderen Zellen Sperren.

Beispiel:
Wenn I9 = 1, sollen die Zellen I14:I25 gesperrt sein.
Wenn I9 = 0, sollen die Zellen I14:I25 "offen" sein.

So weit so gut, das habe ich auch mit diesem Code hingekriegt (sobald der Blattschutz aktiviert _
ist):

          
          
          
          
          
          Private Sub Worksheet_Change(ByVal Target As Range)
              If Range("I9") = "0" Then
                  Range("I14:I25").Locked = False
              Else
              If Range("I9") = "1" Then
                  Range("I14:I25").Locked = True
              End If
          End Sub

Mein eigentliches Problem liegt jedoch darin, dass ich diesen Code auf die Spalten F:NG erweitern muss und ich hoffe natürlich, den Code nicht 365 mal anpassen zu müssen:)

Wie würde der Befehl "Wenn in Zeile 9 eine 1, dann die darunterliegenden Zellen 14-25 in der selben Spalte sperren, ansonsten entsperrt lassen" in einem VBA-Code aussehen?

Danke schon im Voraus für eure Hilfe! :)

Gruss und einen schönen Tag
Max
  

Betrifft: AW: Zellen sperren wenn Bedingung in anderer Zelle erf von: 1712800.html
Geschrieben am: 13.09.2019 08:51:17

Hallo Max,

versuchs mal so:

   Private Sub Worksheet_Change(ByVal Target As Range)
   
   Dim rngLock As Range, Zelle As Range
   Set rngLock = Range("F9:NG9")
   
   For Each Zelle In rngLock
   
       If Zelle = "0" Then
           Range(Cells(Zelle.Row.Offset(5, 0), Zelle.Column), Cells(Zelle.Row.Offset(16, 0), Zelle. _
   Column)).Locked = False
       Else
       If Zelle = "1" Then
           Range(Cells(Zelle.Row.Offset(5, 0), Zelle.Column), Cells(Zelle.Row.Offset(16, 0), Zelle. _
   Column)).Locked = False
       End If
       End If
       
   Next
   
   End Sub
   

Gruss Torsten
  

Betrifft: AW: Zellen sperren wenn Bedingung in anderer Zelle erf von: 1712803.html
Geschrieben am: 13.09.2019 08:55:14

Sorry, kleiner Fehler drin, den ich vergessen habe aus dem Code zu nehmen.

Bitte loeseche jeweils das .Row und lass nur das .Offset.... nach Zelle

  

Betrifft: AW: Zellen sperren wenn Bedingung in anderer Zelle erf von: 1712805.html
Geschrieben am: 13.09.2019 08:57:32

nochmal,

und natuerlich True in der 2. If

  

Betrifft: AW: Zellen sperren wenn Bedingung in anderer Zelle erf von: 1712867.html
Geschrieben am: 13.09.2019 11:49:07

Also nochmal der komplette Code korrigiert:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim rngLock As Range, Zelle As Range
Set rngLock = Range("F9:NG9")

For Each Zelle In rngLock

    If Zelle = "0" Then
        Range(Cells(Zelle.Offset(5, 0), Zelle.Column), Cells(Zelle.Offset(16, 0), Zelle.Column)) _
.Locked = False
    Else
    If Zelle = "1" Then
        Range(Cells(Zelle.Offset(5, 0), Zelle.Column), Cells(Zelle.Offset(16, 0), Zelle.Column)) _
.Locked = True
    End If
    End If
    
Next

End Sub

  

Betrifft: AW: Zellen sperren wenn Bedingung in anderer Zelle erf von: 1713236.html
Geschrieben am: 16.09.2019 08:06:34

Guten Morgen Torsten

Vielen herzlichen Dank für deine schnelle Message, hab sie leider erst heute gesehen.

Der Code funktioniert so leider nicht. :( Kann in den Zeilen 14-25 jeweils trotzdem etwas eintragen, ob das Blatt gesperrt ist oder nicht...

Hast du vl noch eine andere Idee wie das klappen könnte?

Gruss
Max

  

Betrifft: AW: Zellen sperren wenn Bedingung in anderer Zelle erf von: 1713282.html
Geschrieben am: 16.09.2019 12:37:36

Hallo Max,

ich hatte da wohl auch einen kompletten Denkfehler drin. Habe es jetzt mal an einer kleinen Datei ausprobiert, und so sollte es funktionieren. Das Blatt muss geschuetzt sein. Passwort kannst du im Code aendern oder auch entfernen, falls du keins brauchst.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim rngLock As Range, Zelle As Range
Set rngLock = Range("F9:NG9")

For Each Zelle In rngLock

    If Zelle = "0" Then
        ActiveSheet.Unprotect "XXX"
        ActiveSheet.Range(Cells(Zelle.Row + 5, Zelle.Column), Cells(Zelle.Row + 16, Zelle. _
Column)).Locked = False
        ActiveSheet.Protect "XXX"
    Else
    If Zelle = "1" Then
        ActiveSheet.Unprotect "XXX"
        ActiveSheet.Range(Cells(Zelle.Row + 5, Zelle.Column), Cells(Zelle.Row + 16, Zelle. _
Column)).Locked = True
        ActiveSheet.Protect "XXX"
    End If
    End If
    
Next

End Sub
Gruss Torsten
  

Betrifft: AW: Zellen sperren wenn Bedingung in anderer Zelle erf von: 1713329.html
Geschrieben am: 16.09.2019 15:22:51

Hallo Torsten

Das funktioniert nun schon vieeeel besser! :)
Vielen herzlichen Dank schon mal!

Nun hab eich noch eine weitere Frage/Problemstellung:
nun funktioniert es prima mit dem Sperren der definierten Zellen, wenn ich nun aber in eine nicht gesperrte Zelle einen Eintrag mache, kommt immer

"Laufzeitfehler '1004':
Die Locked-Eigenschaft des Range-Objektes kann nicht festgelegt werden."

Das ist natürlich ein wenig Mühsam, dieses Fenster nach jeder Eintragung zu "Beenden", da ca. 1'500 Eintragungen gemacht werden müssen. Bringt man das noch irgendwie weg?


Ps.: Hast Du mir einen Tipp, wie bzw. wo ich all diese Bedeutungen dieser VBA-Codes bzw. Befehle lernen oder nachschlagen kann? :)


Vielen Dank für deine Mühe und einen schönen Nachmittag noch! :)

Gruss
Max

  

Betrifft: AW: Zellen sperren wenn Bedingung in anderer Zelle erf von: 1713402.html
Geschrieben am: 17.09.2019 09:05:50

Hallo Max,

versteh ich nicht. Ich bekomme diesen Fehler in meiner Testtabelle nicht. Bitte versuch mal zuerst alle Zellen zu entsperren. Heisst, das komplette Blatt markieren mit dem kleinen Dreieck ganz oben links in der Ecke. Dann Rechtsklick irgendwo im Blatt. Formatierung, Schutz und dort den Haken raus bei (in english ist das "Locked"). Dann das Blatt schuetzen und die Zahlen 1 und 0 in die Zeile 9 eintragen. Dann versuchs nochmal. Hoffe, dass der Fehler dann nicht mehr kommt.

Ein Tip, die Grundlagen von VBA zu lernen waere dieser:
https://www.vba-tutorial.de/

Gruss Torsten

  

Betrifft: AW: Zellen sperren wenn Bedingung in anderer Zelle erf von: 1713653.html
Geschrieben am: 18.09.2019 13:53:34

Hat top funktioniert!
Vielen herzlichen Dank!!

Gruss
Max

  

Betrifft: gerne... von: 1713657.html
Geschrieben am: 18.09.2019 13:57:59

viel Spass beim Lernen.

Beiträge aus dem Excel-Forum zum Thema "Zellen sperren wenn Bedingung in anderer Zelle erf"