Excel VBA: Wenn eine Zelle bestimmten Wert enthält, müssen bestimmte Zellen gesperrt werden

  • Excel

Es gibt 18 Antworten in diesem Thema. Der letzte Beitrag () ist von SZR2D.

    Excel VBA: Wenn eine Zelle bestimmten Wert enthält, müssen bestimmte Zellen gesperrt werden

    Hallo, ich habe folgendes Anliegen.

    Wenn in der Zelle "E4" eine 1,2,3 oder eine 4 steht, sollen bestimmte Zellen gesperrt werden. Also das Tabellenblatt soll mit PW freigegeben werden, die Zellen sollen gesperrt werden und das Blatt soll wieder gesperrt werden. Ich habe etwas geschrieben, aber es funktioniert irgendwie gar nicht(((


    Visual Basic-Quellcode

    1. Private Sub Worksheet_FormatChange(ByVal Target As Range)
    3. Application.EnableEvents = False
    5. Me.Unprotect "Lok"
    6. If InStr(Range("E4").Value, "1") > 0 Then
    7. Range("F39,F42,F93,F96").Locked = True
    8. Worksheets("7 Herstellanweisung").Protect
    9. End If
    11. If InStr(Range("E4").Value, "2") > 0 Then
    12. Range("F28,F29,F39,F42,F51,F53,F62,F63,F69,F93,F96").Locked = True
    13. Worksheets("7 Herstellanweisung").Protect
    14. End If
    16. If InStr(Range("E4").Value, "3") > 0 Then
    17. Range("F19,F20,F38").Locked = True
    18. Worksheets("7 Herstellanweisung").Protect
    19. End If
    21. If InStr(Range("E4").Value, "4") > 0 Then
    22. Range("F38,F93,F96").Locked = True
    23. Worksheets("7 Herstellanweisung").Protect
    24. End If
    26. Me.Protect "Lok"
    27. Application.EnableEvents = True
    29. End Sub


    Hoffentlich könnt ihr mir helfen, ich bin langsam am Verzweifeln...

    CodeTags korrigiert ~VaporiZed

    Dieser Beitrag wurde bereits 1 mal editiert, zuletzt von „VaporiZed“ ()

    SZR2D schrieb:

    Worksheet_FormatChange
    Welches Zellformat hat E4?
    Numerisch oder Text?

    Worksheet_FormatChange wird nur bei einer Zellformatänderung geworfen.
    Wird die Prozedur überhaupt aufgerufen?
    Setz mal einen Breakpoint in Zeile 3 und warte bis er angegangen wird.
    --
    If Not Program.isWorking Then Code.Debug Else Code.DoNotTouch
    --

    Also die Zelle "E4" ist als Standard formatiert
    Worksheet_FormatChange, als "Format" habe ich es bezeichnet, damit es nicht im Konflikt mit anderem "Worksheet_Change" steht.
    Ne, es passiert gar nichts, es gibt auch keine Fehlermeldung oder dergleichen..
    Was meinst du mit einem Breakpoint setzen, ich bin ein absoluter Noob in Sachen VBA((

    Wann hättest du denn gern, dass der Code ausgeführt werden soll?
    Nur dass er irgendwo steht, bewirkt noch gar nichts.
    Irgendeine Aktion oder ein Ereignis muss ihn schon aufrufen.


    SZR2D schrieb:

    Was meinst du mit einem Breakpoint setzen
    Zum Debuggen gibt's hier ein kleines Tutorial:
    vba-tutorial.de/fehler/debuggen.htm
    --
    If Not Program.isWorking Then Code.Debug Else Code.DoNotTouch
    --

    SZR2D schrieb:

    E4 wird automatisch per Sverweis befüllt.
    Das macht es nicht einfach.
    Sonst hättest du es einfach im Worksheet_Change-Ereignis triggern können.
    So musst du das Calculate-Ereignis (das sehr häufig geworfen wird) bemühen.

    Visual Basic-Quellcode

    1. ​Option Explicit
    2. Private PreviousValue As Variant
    3. Private Sub Worksheet_Calculate()
    4. If IsError(Range("E4")) Then Exit Sub 'formula error
    5. If Range("E4").Value = PreviousValue Then Exit Sub 'no change
    6. PreviousValue = Range("E4").Value
    7. CheckedCellChanged
    8. End Sub
    9. Private Sub CheckedCellChanged()
    10. ' hier dein Check-Code
    11. End Sub
    --
    If Not Program.isWorking Then Code.Debug Else Code.DoNotTouch
    --

    Zum einen ist es das Worksheet_Change das aufgerufen wrden muss.
    Hier ein möglicher Code:

    Visual Basic-Quellcode

    1. Private Sub Worksheet_Change(ByVal Target As Range)
    2. If Target.Address="$E$4" Then
    3. Worksheets("7 Herstellanweisung").Unprotect "Lok"
    4. Select case Range("E4").Value
    5. Case 1
    6. Range("F39,F42,F93,F96").Locked = True
    7. Case 2
    8. Range("F28,F29,F39,F42,F51,F53,F62,F63,F69,F93,F96").Locked = True
    9. Case 3
    10. Range("F19,F20,F38").Locked = True
    11. Case 4
    12. Range("F38,F93,F96").Locked = True
    13. End select
    14. Worksheets("7 Herstellanweisung").Protect "Lok"
    15. End If
    16. End Sub
    NB. Es ist doch schön, wenn man lesbare Namen vergibt. Siehe auch [VB.NET] Beispiele für guten und schlechten Code (Stil).

    Dieser Beitrag wurde bereits 3 mal editiert, zuletzt von „INOPIAE“ ()

    INOPIAE schrieb:

    Zum einen ist es das Worksheet_Change das aufgerufen wrden muss.
    Nein, das hilft nicht, wenn eine Formel den Wert bestimmt.
    Die Zelle selbst ändert sich nie, nur deren berechneter Wert.
    Und da wird das Change-Ereignis nicht geworfen.

    Edit:
    Ich hab's mal komplett auscodiert

    Visual Basic-Quellcode

    1. ​Option Explicit
    2. Private PreviousValue As Variant
    3. Private Sub Worksheet_Calculate()
    4. If IsError(Range("E4")) Then Exit Sub 'formula error
    5. If Range("E4").Value = PreviousValue Then Exit Sub 'no change
    6. PreviousValue = Range("E4").Value
    7. CheckedCellChanged
    8. End Sub
    9. Private Sub CheckedCellChanged()
    10. Me.Unprotect "Lok"
    11. Cells.Locked = False
    12. Select Case Val(Range("E4").Value)
    13. Case 1:
    14. Range("F39,F42,F93,F96").Locked = True
    15. Case 2:
    16. Range("F28,F29,F39,F42,F51,F53,F62,F63,F69,F93,F96").Locked = True
    17. Case 3:
    18. Range("F19,F20,F38").Locked = True
    19. Case 4:
    20. Range("F38,F93,F96").Locked = True
    21. End Select
    22. Me.Protect "Lok"
    23. End Sub

    Und das mit dem UnProtect und Protect kannst du dir auch sparen.
    Protect hat die Option UserInterfaceOnly
    Wenn du einmal

    Visual Basic-Quellcode

    1. ​Me.Protect "Lok", UserInterfaceOnly:=True
    aufrufst, gilt die Einschränkung nur für den User und du kannst aus VBA raus immer ändern.
    --
    If Not Program.isWorking Then Code.Debug Else Code.DoNotTouch
    --

    Dieser Beitrag wurde bereits 1 mal editiert, zuletzt von „petaod“ ()

    @petaod
    Danke schon mal für die Hilfe, leider funktioniert es noch nicht.
    So sieht jetzt mein Code aus (ich habe da noch zusätzlich einen Code drin, der die Zellen sperrt, soweit die beschrieben werden). Das Ganze steht dann im VBA vom betroffenen Tabellenblatt:

    Visual Basic-Quellcode

    1. Option Explicit
    3. Sub Worksheet_Change(ByVal Target As Range)
    4. With Target
    5. Me.Unprotect "Lok"
    6. Range("H1").Interior.ColorIndex = 9
    7. Application.EnableEvents = False
    8. .Locked = True
    9. Application.EnableEvents = True
    10. Me.Protect "Lok"
    11. End With
    12. End Sub
    14. Private PreviousValue As Variant
    15. Private Sub Worksheet_Calculate()
    16. If IsError(Range("E4")) Then Exit Sub 'formula error
    17. If Range("E4").Value = PreviousValue Then Exit Sub 'no change
    18. PreviousValue = Range("E4").Value
    19. CheckedCellChanged
    20. End Sub
    21. Private Sub CheckedCellChanged()
    22. Me.Unprotect "Lok"
    23. Cells.Locked = False
    24. Select Case Val(Range("E4").Value)
    25. Case 1:
    26. Range("F39,F42,F93,F96").Locked = True
    27. Case 2:
    28. Range("F28,F29,F39,F42,F51,F53,F62,F63,F69,F93,F96").Locked = True
    29. Case 3:
    30. Range("F19,F20,F38").Locked = True
    31. Case 4:
    32. Range("F38,F93,F96").Locked = True
    33. End Select
    34. Me.Protect "Lok"
    35. End Sub


    CodeTags korrigiert; diese bitte beachten ~VaporiZed

    Dieser Beitrag wurde bereits 1 mal editiert, zuletzt von „VaporiZed“ ()

    Naja, wenn du solche Einzel-Locks umsetzen willst, musst du Zeile 23 natürlich weglassen.
    Dann musst du dir allerdings überlegen, wann du die Zellen wieder entsperren willst.
    Sonst ist irgendwann das ganze Sheet gesperrt.

    Und nochmals:
    Lass diese Unprotect-Protect-Litanei.
    Verwende Protect mit UserInterfaceOnly:=True, dann kannst du dir das sparen.

    SZR2D schrieb:

    leider funktioniert es noch nicht
    Ist eine Meldung, mit der keiner was anfangen kann.
    Bitte mehr Details.

    Kompiliert dein Code überhaupt?
    Zeile 14 gehört nach Zeile 2 verschoben.
    --
    If Not Program.isWorking Then Code.Debug Else Code.DoNotTouch
    --

    Leider habe ich keine Berechtigung hier Screenshots zu posten. Also wenn ich es so 1zu1 übernehme, dann setzt er mir ein Fragezeichen vor Option Explicit beim 2. Code (also dem von dir). Das schaut dann so aus

    Visual Basic-Quellcode

    1. ?Option Explicit

    Wenn ich das Fragezeichen wegmache, dann kommt eine Fehlermeldung "Fehler beim Kompilieren: Variable nicht definiert" und die Zeile 3 wird gelb markiert:

    Visual Basic-Quellcode

    1. Private Sub Worksheet_Calculate()


    Wenn ich beim 2. Code "Option Explicit" weglasse, dann kommt weder eine Fehlermeldung, noch passiert irgend etwas

    petaod schrieb:

    Lass diese Unprotect-Protect-Litanei.
    Verwende Protect mit UserInterfaceOnly:=True, dann kannst du dir das sparen.

    Würde ich ja gern, aber ich weiß nicht wie))) Ich wüsste nicht an welcher Stelle und wo ich was erstezen soll))

    Visual Basic-Quellcode

    1. Option Explicit
    3. Sub Worksheet_Change(ByVal Target As Range)
    4. With Target
    5. Me.Unprotect "Lok"
    6. Range("H1").Interior.ColorIndex = 9
    7. Application.EnableEvents = False
    8. .Locked = True
    9. Application.EnableEvents = True
    10. Me.Protect "Lok"
    11. End With
    12. End Sub
    14. Option Explicit
    15. Private PreviousValue As Variant
    16. Private Sub Worksheet_Calculate()
    17. If IsError(Range("E4")) Then Exit Sub 'formula error
    18. If Range("E4").Value = PreviousValue Then Exit Sub 'no change
    19. PreviousValue = Range("E4").Value
    20. CheckedCellChanged
    21. End Sub
    22. Private Sub CheckedCellChanged()
    23. Me.Unprotect "Lok"
    24. Cells.Locked = False
    25. Select Case Val(Range("E4").Value)
    26. Case 1:
    27. Range("F39,F42,F93,F96").Locked = True
    28. Case 2:
    29. Range("F28,F29,F39,F42,F51,F53,F62,F63,F69,F93,F96").Locked = True
    30. Case 3:
    31. Range("F19,F20,F38").Locked = True
    32. Case 4:
    33. Range("F38,F93,F96").Locked = True
    34. End Select
    35. Me.Protect "Lok"
    36. End Sub

    Dieser Beitrag wurde bereits 1 mal editiert, zuletzt von „SZR2D“ ()

    Ersetze mal alles durch dies:

    Visual Basic-Quellcode

    1. Option Explicit
    3. Private PreviousValue As Variant
    5. Sub Worksheet_Change(ByVal Target As Range)
    6. With Target
    7. Me.Unprotect "Lok"
    8. Range("H1").Interior.ColorIndex = 9
    9. Application.EnableEvents = False
    10. .Locked = True
    11. Application.EnableEvents = True
    12. Me.Protect "Lok"
    13. End With
    14. End Sub
    17. Private Sub Worksheet_Calculate()
    18. If IsError(Range("E4")) Then Exit Sub 'formula error
    19. If Range("E4").Value = PreviousValue Then Exit Sub 'no change
    20. PreviousValue = Range("E4").Value
    21. CheckedCellChanged
    22. End Sub
    24. Private Sub CheckedCellChanged()
    25. Me.Unprotect "Lok"
    26. Cells.Locked = False
    27. Select Case Val(Range("E4").Value)
    28. Case 1
    29. Range("F39,F42,F93,F96").Locked = True
    30. Case 2
    31. Range("F28,F29,F39,F42,F51,F53,F62,F63,F69,F93,F96").Locked = True
    32. Case 3
    33. Range("F19,F20,F38").Locked = True
    34. Case 4
    35. Range("F38,F93,F96").Locked = True
    36. End Select
    37. Me.Protect "Lok"
    38. End Sub

    In einer Klasse (ist auch der Code hinter einer Tabelle) fängt man mit den oben mit den Option Settings an.
    Dann kommen die Variablen, di in der ganzen Klasse gelten und dann die Prozeduren und Funktionen.
    NB. Es ist doch schön, wenn man lesbare Namen vergibt. Siehe auch [VB.NET] Beispiele für guten und schlechten Code (Stil).

    @INOPIAE
    Ich musste jetzt etwas anpassen, da er mir so alle übrigen Zellen öffnet - aber nun passt es genauso, wie ich es mir vorgestellt hatte! Vielen Dank dafür :D
    Ich habe es jetzt so angepasst, dass er mir alle Zellen sperrt und alle benötigten öffnet.

    Ich habe noch eine Frage, wenn wir schon dabei sind. Bei meinem ersten Code, der mir die Zellen sperrt, wenn die beschrieben werden:

    Visual Basic-Quellcode

    1. ​Option Explicit
    3. Private PreviousValue As Variant
    5. Sub Worksheet_Change(ByVal Target As Range)
    6. With Target
    7. Me.Unprotect "Lok"
    8. Range("H1").Interior.ColorIndex = 9
    9. Application.EnableEvents = False
    10. .Locked = True
    11. Application.EnableEvents = True
    12. Me.Protect "Lok"
    13. End With
    14. End Sub


    Der Code funzt wie er sollte, bei verbundenen Zellen jedoch nicht, da wirf er mich raus und markiert die Zeile:

    Visual Basic-Quellcode

    1. ​.Locked = True

    Kann man da etwas machen?

    SZR2D schrieb:

    ich weiß nicht wie
    Diese Prozedur einmal ausführen.
    Danach kannst du sie meinetwegen wieder löschen.

    Visual Basic-Quellcode

    1. Sub SetProtection
    2. Me.Unprotect "LoK"
    3. Me.Protect "LoK", UserInterfaceOnly:=True
    4. End Sub
    Und dann alle Protect- und Unprotect-Aufrufe aus deinem Code entfernen.

    SZR2D schrieb:

    Der Code funzt wie er sollte, bei verbundenen Zellen jedoch nicht
    Mit verbundenen Zellen zu arbeiten sollte man möglichst meiden.
    Das schafft nur Probleme.

    Visual Basic-Quellcode

    1. ​Sub Worksheet_Change(ByVal Target As Range)
    2. Range("H1").Interior.ColorIndex = 9
    3. Target.MergeArea.Locked = True
    4. End Sub


    Die Events abzuschalten ist übrigens auch sehr gefährlich.
    Wenn's einmal kracht und sie werden nicht mehr angeschaltet, werden sie nicht wieder ausgeführt.
    Und notwendig ist es in deinem Fall auch nicht.
    --
    If Not Program.isWorking Then Code.Debug Else Code.DoNotTouch
    --

    Dieser Beitrag wurde bereits 2 mal editiert, zuletzt von „petaod“ ()

    INOPIAE schrieb:

    If target.MergeCells = True Then

    MergeCells musst du, glaube ich, gar nicht abfragen, weil bei einer Einzelzelle die MergeArea den Range der Zelle beinhaltet.

    Den einzigen kritischen Punkt, den ich noch sehe, ist die Auslösung des Events durch Copy&Paste mit einem großen Bereich.
    Evtl. müsste man das noch abfangen.
    Aber so weit sehe ich das Projekt noch nicht.
    --
    If Not Program.isWorking Then Code.Debug Else Code.DoNotTouch
    --

    Hallo zusammen, leider bekomme ich immer noch die Fehlermeldung mit dem Debuggen und die ".Locked=True wird dabei markiert, evtl. füge ich den Code an der falschen Stelle ein?
    Ich habe jetzt beide Varianten probiert, aber immer das gleich Problem..
    Wo genau sollte der Code eingefügt werden?

    Visual Basic-Quellcode

    1. ​Option Explicit
    3. Sub Worksheet_Change(ByVal Target As Range)
    4. With Target
    5. Me.Unprotect "Lok"
    6. Range("H1").Interior.ColorIndex = 9
    7. Application.EnableEvents = False
    8. .Locked = True
    9. Application.EnableEvents = False
    10. Me.Protect "Lok"
    11. End With
    12. End Sub


    Und wie gesagt, so funzt es auch nicht:

    Visual Basic-Quellcode

    1. ​Option Explicit
    3. Sub Worksheet_Change(ByVal Target As Range)
    4. With Target
    5. Me.Unprotect "Lok"
    6. Range("H1").Interior.ColorIndex = 9
    7. Application.EnableEvents = False
    8. .Locked = True
    9. Application.EnableEvents = False
    10. Me.Protect "Lok"
    11. End With
    12. End Sub
    14. Sub Worksheet_MergedChange(ByVal Target As Range)
    15. Range("H1").Interior.ColorIndex = 9
    16. If Target.MergeCells = True Then
    17. End Sub

    Auch bei der Variante wird .Locked markiert

    Visual Basic-Quellcode

    1. ​Sub Worksheet_MergedChange(ByVal Target As Range)
    2. Range("H1").Interior.ColorIndex = 9
    3. If Target.MergeCells = True Then
    4. Target.MergeArea.Locked = True
    5. Else
    6. Target.Locked = True
    7. End If
    8. End Sub

    Keine Ahnung, was du machst.
    Bei mir läuft folgender Code problemlos:

    Visual Basic-Quellcode

    1. Sub Worksheet_Change(ByVal Target As Range)
    2. Range("H1").Interior.ColorIndex = 9
    3. Target.MergeArea.Locked = True
    4. End Sub

    Wenn du allerdings deine Routine ​Worksheet_MergedChange nennst, wird sie natürlich nie angesprungen.
    Ist dir klar, dass die Namensgebung für das Event verantwortlich ist?
    --
    If Not Program.isWorking Then Code.Debug Else Code.DoNotTouch
    --

Facebook
Telefonspende