Excel VBA: Bei Blattschutz keine Fehlermeldung, wenn eine Eingabe erfolgt.

[Excelmania]

Guten Morgen.

Ich habe ein Tabellenblatt bei dem in bestimmten Zellen eine Eingabe erlaubt ist (Zellen ungeschützt). Bei allen anderen Zellen ist der Schutz aktiviert.

Da ich möchte, dass alle Zelle markiert und kopiert werden dürfen, ist dies entsprechend im Blattschutz erlaubt.

Nun kann es aber zu Fehlermeldungen kommen, wenn versucht wird mit Doppelklick eine gesperrte Zelle zu aktivieren oder bei falscher Tastenkombination Excel es als Versuch wertet eine gesperrte Zelle soll beschrieben werden.

Um diese Fehlermeldung zu umgehen habe ich daher folgende Makros definiert. Für den Doppelklick funktioniert es. Für die Eingabe jedoch nicht.

Doppelklick (funktioniert)

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

    If Me.ProtectContents = True And Target.Locked = True Then
        Cancel = True
    End If
    
End Sub

Eingaben über Tastatur (funktioniert nicht)

Private Sub Worksheet_Change(ByVal Target As Range, Cancel As Boolean)

    If Me.ProtectContents = True And Target.Locked = True Then
        Cancel = True
    End If

End Sub

Wichtig die Eingabe in nicht gesperrten Zellen sowie Strg + C sollen weiterhin möglich sein.

Jemand eine Idee?

 

[Bonanca]

Wenn ich mich richtig erinnere hast du folgendes Problem:

Excel checkt ja nicht erst NACH einer Veränderung, ob sie geschützt ist, sondern bereits vorher.
In deinem 1. Makro hast du also folgendes Szenario:
Mensch doppelklickt Zelle -> Makro wird ausgeführt BEVOR doppelklick im Sheet umgesetzt wird -> Makro bricht Umsetzung des Doppelklicks im Sheet ab.

Das 2. Makro aktiviert sich jedoch erst nach einer Änderung:
Mensch will Zelle bearbeiten -> Excel stellt fest, dass die Zelle geschützt ist und verhindert bearbeitung.
An keiner Stelle wurde das Worksheet geändert, daher wird das Sub Worksheet_Change nicht durchgeführt.

Du bräuchtest also entweder einen Observer, der reagiert, BEVOR die Änderungen eintreten (weiß nicht, ob sowas überhaupt in Excel existiert) oder musst eine andere Lösung finden.

Wäre es für dich eine Lösung, die gesperrten Zellen mittels eines 'Worksheet_SelectionChange' Subs einfach gar nicht anklickbar zu sein (außer man wählt mit Strg+A alle gleichzeitig aus)?
Du hast dann immernoch das Problem des beschreibens, wenn alle Zellen ausgewählt sind und der Anwender was schreiben will. Aber iwo muss man auch an die Kompetenz des Anwenders vertrauen (wobei dann die Makros an sich überflüssig wären.)

 

[Scientist]

Ich denke, du willst ein Problem loesen, was nicht sinnvoll ist, es zu loesen.
Excel bietet zu viele Moeglichekeiten, als das man alles irgendwie verriegeln kann.

Ohne den Arbeitsablauf zu kennen:
Wenn immer dieselben Bereiche kopiert werden sollen, koennte man einen Button einfuegen, der diese kopiert.
Dann kann man auch den Haken bei "gesperrte Zellen auswaehlen" entfernen.

 

[cumulonimbus8]

Wichtig die Eingabe in nicht gesperrten Zellen sowie Strg + C sollen weiterhin möglich sein.

Target.Row und Target.Colum helfen mit IF (und dem Stichwort InterSect) die Bereiche mit Erlaubnis zu erfassen.

Worksheet_BeforeDoubleClick sollte neben Worksheet_Change ohne ein Before von selbst erklären was da passiert. VBA ist nicht konsequent erdacht worden…

Es ist aufwändig, und ich werfe mal den Hinweis Application.EnableEvents für weitere Basteleien in den Ring.

CN8

 

[Scientist]

Fehlermeldungen oder Events zu unterdruecken waere moeglich, halte ich aber hier fuer nicht sinnvoll.
Ich denk enicht, dass das wasserdicht definiert werden kann und es Situationen geben koennte, in denen diese nicht wieder aktiviert werden.
Und wenn schon die Meldung zu viel ist, ist es ein deaktiviertes Event erst recht.

 

[cumulonimbus8]

Äh, ja, leicht daneben formuliert.
Die Events muss man ausschalten wenn man selbst VBA im sensitiven Bereich arbeitet - anderenfalls triggert sich das Makro ewig selbst…
CN8

 

[NotNerdNotDau]

Eingaben über Tastatur

Versuche es mit Application.EnableEvents:

Private Sub Worksheet_Change(ByVal Target As Range, Cancel As Boolean)

   If Me.ProtectContents = True And Target.Locked = True Then
        Application.EnableEvents = False
        Cancel = True
        Application.EnableEvents = True
   End If

End Sub

 

[Excelmania]

Guten Morgen.

Vielen Dank für Eure Rückmeldung. Zu meinem Bedauern klappt, das leider nicht.

Ich habe jetzt gerade einmal versucht es an Worksheet_Activate zu koppeln. Leider auch ohne erfolg:

Option Explicit

Sub Worksheet_Activate()

        Application.EnableEvents = False
        Application.DisplayAlerts = False
        
End Sub

 

[cumulonimbus8]

Die Idee von NotNerdNotDau sollte aber grnau das tun um das es geht.

Die Alerts abzuschalten halte ich für… ungeschickt, um das leibevoll zu formulieren.

CN8

 

[Excelmania]

Moin.

Macht es bei mir in Excel 2019 und 2016 aber nicht.

Klicke ich eine Zelle an, die gesperrt ist und fange anzuklicken dann erscheint eine Fehlermeldung. Kalppt es bei DIr?

 

[Scientist]

Das Worksheet_Activate wird auch nur ausgeloest, wenn das Blatt aufgerufen wird.
Bei dieser Methode muessen die Events im uebrigen manuell wieder aktiviert werden oder man muss Excel wieder vollstaendig schließen.
Schließlich hast du gerade alle automatischen Ereignisse deaktviert.
Gleiches gilt fuer die Fehlermeldungen.

Zum Change-Event:
Der Blattschutz greift schon beim Versuch die Zelle zu bearbeiten ein und nicht erst nachdem eine Aenderung durchgefuehrt wurde.