Eine Liebeserklärung an MS Excel
Wie man denn erkennen kann, ob ein Tabellenblatt geschützt ist, frage ich in der gestrigen Excelschulung.
Eine Teilnehmerin erklärt mir, dass sie in die Symbolleiste für den Schnellzugriff das Symbol „Blatt schützen“ eingefügt hat. Und da diese Symbolleiste bei ihr unterhalb des Menübandes liegt, zeigt das Symbol entweder „Blatt schützen“ oder „Blattschutz aufheben“ an:
Sehr clever, finde ich! So nervt Excel ein bisschen weniger.
Fatal.
Gestern war Excelstammtisch. Diana Sperber erzählte uns einige spannende Dinge über Power Query. Sehr interessant!
Beim Thema „Schutz“ musste ich schlucken.
Wenn man in Excel in Microsoft 365 eine Arbeitsmappe schützt, kann man zwar die Tabelle aktualisieren, aber nicht mehr den Code verändern und einsehen:
DOCH!
Man kann den Code kopieren und in einem Editor eingefügt anzeigen lassen:
Code einsehen geht – Code manipulieren natürlich nicht.
Danke an Diana für den hervorragenden Vortrag.
Am Montag auf dem Excelstammtisch. Wir schauen intelligente Tabellen an.
Inga stellt folgende Frage: in einer intelligenten Tabelle, die nicht erweitert wird, wird eine Spalte nicht gesperrt. Das Blatt wird geschützt, aber filtern und sortieren sind erlaubt. Während das Filtern möglich ist, geht das Sortieren nicht.
Ja, leider – sehr schade.
Greift man mit Visio auf eine Excelmappe zu, die schreibgeschützt gespeichert wurde, erhält man folgende (nicht sehr aussagekräftige) Meldung:
Die ausgewählte Datei ist keine Microsoft Excel-Datei, oder darauf kann zurzeit nicht zugegriffen werden.
Sehr geehrter Herr Martin,
[…]
Das andere Problem ist, dass ich die Tabelle davor schützen muss, dass meine Kollegen aus Versehen Eintragungen löschen können (wir arbeiten gemeinsam über eine NAS). Lediglich das Feld Doku soll geöffnet und genutzt werden können. Den Arbeitsmappenschutz habe ich auch hinbekommen, aber dann lässt sich die Doku nicht mehr öffnen.
Mit herzlichem Dank und freundlichen Grüßen
###
Hallo Frau H.,
Markieren Sie die Zellen, die alle bearbeiten dürfen. Über „Zellen formatieren“ (rechte Maustaste) und dort Registerkarte „Schutz“ heben Sie den Schutz („gesperrt“) auf.
Anschließend schützen Sie das ganze Blatt (Überprüfen / Schützen / Blatt schützen). Das Blatt ist geschützt außer den Zellen, die vorher „entsperrt“ wurden.
Verblüfft. Eine Teilnehmerin in einer Excelschulung sagt mir, dass eine intelligente Tabelle auf einem geschützten Arbeitsblatt liegt:
Sie möchte eine Pivottabelle erstellen, was mit Einfügen / Pivot-Tabelle nicht möglich ist.
Und umgekehrt:
Befindet sich der Cursor auf einem leeren, ungeschützten Tabellenblatt, klickt man dort auf Einfügen / Pivot-Tabelle , kann man den Namen der intelligenten Tabelle eintragen:
Padautz: SO kann man eine Pivottabelle erzeugen!
Guten Morgen Rene,
wenn du bei deinen Kunden eine Excel-Datei im Einsatz hast,
die dann mit PowerQuery ausgestattet ist,
wie lege ich die Schutzoptionen im Dialog fest, dass bei geschütztem Blatt die Option
„Aktualisierung“
über
– Option1: Kontextmenü, rechte Maustaste
– Option 2: Ribbon: Daten / alle aktualisieren
noch möglich ist?.
Ich darf aber an dieser Stelle keinen VBA nutzten. Das ist (m)ein Problem.
Es gibt dann wohl keine Lösung dazu, oder?
Ein Dankeschön für deine Information & guter Tagesstart
Jürgen
#####
Hi Jürgen,
gute Frage.
Meines Wissens geht das nicht: Schutz bedeutet, dass keine Änderungen möglich sind.
Also: entweder Schutz oder Aktualisierung der Power Query-Tabelle. Oder eben doch ein paar Zeilen VBA.
Andere Lösung: die Daten, die geschützt werden sollen, auf ein anderes, geschütztes Blatt
Liebe Grüße
Rene
Ich wundere mich, warum ich in eine Datei plötzlich keine Eigenschaften mehr eintragen kann:
Auch nicht per VBA.
Mir dämmert es: Wenn ein Arbeitsmappenschutz gesetzt ist, wird dies verhindert!
Ups, was ist denn das? In Excel in Microsoft 365 finde ich folgendes Symbol:
Auch im Kontextmenü des Tabellenblattes ist es vorhanden:
„Schutz pausieren“. Klingt Schweizerdeutsch. Tönt im Excelkontext merkwürdig …
Ist euch das schon einmal aufgefallen?
In der Registerkarte „Überprüfen“ gibt es in der Gruppe „Schutz“ die beiden Symbole „Blatt schützen“ und „Arbeitsmappe schützen“:
Aktiviert man den Blattschutz, ändert sich der Text:
Aktiviert man den Schutz der Arbeitsmappe, wird das Symbol grau unterlegt – der Text wird nicht geändert:
Geschmunzelt habe ich gestern schon.
Eine Schulung. Ein Grafikprogramm. Und es kommt – wie so häufig – die Frage auf: „Ach, wo ich mich gerade mit Ihnen unterhalte – ich habe da mal eine Excelfrage. Wir haben vom Kunden eine Datei erhalten, da ist ein Schutz drauf. Können Sie den wegmachen?“
Ich lasse mir die Datei zeigen:
Und dann: sehen Sie, wenn ich nun den Tippfehler in Zelle B11 ändern will, dann darf ich das nicht. Da ist ein Schutz drauf. Sie zeigt es mir:
Ich lache und bitte sie in der Datenüberprüfung nachzusehen. Wir werden fündig: die Werte werden von einem ausgeblendeten Tabellenblatt „geholt“:
Das Tabellenblatt „Daten“ kann man einblenden – tatsächlich: dort finden sich in A2:A4 die Daten:
Wir ändern den Tippfehler, blenden das Tabellenblatt wieder aus. Natürlich ist sie traurig, dass die Fehler auf dem anderen Blatt nicht sofort geändert wurden. Aber mit dem Befehl „Ersetzen“ funktioniert das schnell.
Was mich DANN sehr amüsiert hat, war ihre Erzählung (sie zeigte mit den Chatverlauf in teams), dass sie bei der IT Ihrer Firma nachfragte, ob diese nicht die Ursache des Problems kenne. Sie hatte gebeten, man möge die Originaldatei ohne Schutz kommen lassen. Oder: die Datei noch einmal erstellen!?! Ich war schon sehr irritiert und habe einen Grundkurs Excel in Ihrer Firma angeboten.
Irgendwie doof.
Ich habe eine Datei, in der sich verschiedene Werte befinden. Diese Datei wird mit Kennwortschutz gespeichert:
Einige Werte dieser Datei werden in eine andere Datei verknüpft:
Sind beide Dateien geschlossen, wird die Zieldatei geöffnet, so fragt Excel danach, ob die Daten aktualisiert werden sollen. Danach erfolgt die Frage nach dem Kennwort der kennwortgeschützten, verknüpften Datei.
Wurde allerdings die verknüpfte Datei an andere Stelle kopiert, dort bearbeitet und wieder zurückgespielt, erkennt Excel dies – aufgrund des Kennwortschutzes – nicht. Eine Neuberechnung zeigt nicht die aktuellen Daten. Man muss die Originaldatei öffnen, um eine Aktualisierung der Daten zu erreichen …
Sicherlich hätte ich sehr, sehr lange gesucht. Und mich gewundert. Zum Glück hat mir Tanja Kuhn geholfen. Beziehungsweise mich schon vorher aufmerksam gemacht.
Die Aufgabe: in einem Wordformular soll dynamisch, das heißt per VBA, die Kopfzeile (und auch die Fußzeile) ausgetauscht werden:
Ich beginne mit dem Löschen der Kopfzeile.
ActiveDocument.Sections(i).Headers(wdHeaderFooterFirstPage).Range.DeleteObwohl weder das Dokument noch einer der Abschnitte geschützt ist, erhalte ich eine Fehlermeldung:
Die Antwort: das Bild befindet sich in einem Inhaltssteuerelement und das wurde im Entwurfsmodus in den Eigenschaften geschützt. Folglich kann auch nicht die Kopfzeile gelöscht werden …
Böse! Ganz böse!
Guten Abend René,
ich grüße zur späten Abendstunde und erlaube mir um diese Uhrzeit noch eine Frage mitzusenden:
Ich möchte lediglich in Erfahrung bringen, welche Möglichkeiten es (nicht) gibt, bzgl. des EXCEL-Solvers:
(1) Funktionieren sollte …
der Einsatz „Solver“ über Ribbon „Daten“/“Solver“, bei geschütztem Tabellenblatt, wenn zusätzlich VBA genutzt wird und beim Makrolauf das Passwort (Tabellenblatt) am Anfang aufgehoben und gegen Makroende wieder gesetzt wird das kann man dem www entnehmen
(2) Nicht funktionstüchtig ist:
Aktivierter Tabellenblattschutz, keine VBA-Nutzung. „Solver“-Einsatz über Ribbon „Daten“/“Solver“
meine heutige Erfahrung
Mir geht es vor allem um Position (2). Die Argumentation beim Kunden:
Da (2) nicht funktionstüchtig ist, muss ich (1) realisieren.
Ein ok zu (1) und (2) wäre nett, sicherlich kennst du die Thematiken von deinen Kunden.
Ein Dankeschön für dein ok & Gruß
Jürgen
Moin, Jürgen,
was macht der Solver? Er liest Werte aus einem Tabellenblatt, rechnet und schreibt Werte zurück. Beziehungsweise schreibt Zwischenergebnisse zurück und prüft, ob sich die Ergebnisse dem gewünschten Ziel annähern.
Das kann auf einem geschützten Blatt nicht funktionieren.
Ich mache es (bei anderen) VBA-Programmen immer so, dass ich den Blattschutz aufheben, Werte eintrage und dann den Schutz wieder setze.
Übrigens: auch ohne zu schreiben – es gibt einige VBA-Befehle, die man auf einem geschützten Blatt nicht ausführen kann, beispielsweise CurrentRegion!?!
Liebe Grüße
Rene
„Eine üble Sache“, meint Peter.
Der „neue“ Kommentar wird auch auf gesperrten Arbeitsblättern zugelassen 🙁 Und verhindert das setzen eines alten Kommentares.
D.h. VBA Code welcher „alte“ Kommentare (Comments) setzt und dabei auf einen CommentThreaded in der Zelle trifft, hat große Probleme.
Danke an Peter für diesen Hinweis zu den Kommentaren und Notizen.
Schade. Eigentlich. Ich fand die Schlosssymbole praktisch.
https://excel.uservoice.com/forums/304921/suggestions/40858108?fbclid=
Man hat mich wieder gezwungen LibreOffice Calc zu unterrichten. In der Schulung kam die Frage, was passiert, wenn man eine Calc-Tabelle mit Kennwort schützt
und in Excel öffnet:
Die Antwort: man kann die Datei nicht öffnen!
Umgekehrt: wird ein Tabellenblatt in Excel mit Kennwort geschützt, kann man die Datei in Calc öffnen und den Schutz entfernen. Es wird nicht nach dem Kennwort gefragt!?!
Wenn man ein Tabellenblatt in Excel schützt, sieht man an dem Symbol in der Registerkarte „Überprüfen“, dass das Blatt geschützt wurde – nun kann man den Blattschutz aufheben:
In den anderen Registerkarten des Menübandes sind die Symbole „ausgegraut“, das heißt nicht aktiv – ein schwacher Hinweis auf Blattschutz. Es könnte auch andere Gründe dafür geben, dass man die Symbole nicht aktivieren kann.
Seit einer Weile spendiert Excel in Microsoft 365 ein neues (Schloss-)Symbol in der Registerkarte:
Aber – so fragt Mourad Louha – welche der Optionen zum Schutz wurden denn aktiviert?
Meine witzelnde Antwort darauf: Wie bei einem Formular: man muss das Blatt rumdrehen – auf der Rückseite befindet sich die Liste der Einstellungen.
Ich möchte gerne per VBA auf einem geschützten Excel-Formular eine Dropdownliste (Datenüberprüfung) ändern. Okay – man hätte die auch per Formeln mit zwei Dropdownlisten erzeugen können – aber mit meiner VBA-Lösung bin ich flexibler.
Der Code lautet:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim intZeilen As Integer
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim intZeilen As Integer
If Target.Value = "Für Standorte" Or Target.Value = "Für Gebäude" Then
ActiveSheet.Unprotect
If Target.Value = "Für Standorte" Then
intZeilen = ThisWorkbook.Worksheets("tbl_Basisdaten").Range("B1").CurrentRegion.Rows.Count
Target.Value = "Für Gebäude"
With Target.Offset(0, 1).Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=tbl_Basisdaten!$B$2:$B$" & intZeilen ' -- Spalte B bei "Standorten"
End With
ElseIf Target.Value = "Für Gebäude" Then
intZeilen = ThisWorkbook.Worksheets("tbl_Basisdaten").Range("D1").CurrentRegion.Rows.Count - 1
Target.Value = "Für Standorte"
With Target.Offset(0, 1).Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=tbl_Basisdaten!$D$2:$D$" & intZeilen ' -- Spalte D bei "Gebäuden"
End With
End If
With Target.Offset(0, 1).Validation
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
Target.Offset(0, 1).Value = ""
ActiveSheet.Protect
End IfEnd Sub
Ich erhalte einen Fehler:
Klaro – ich schützte das Blatt (ActiveSheet.Protect) und anschließend wirkt der Doppelklick, denn ich verwende ja das Ereignis BeforeDoubleClick. Die Lösung ist einfach: ich setze den Cursor auf eine nicht gesperrte Zelle – dann klappt es:
Target.Offset(0, 1).Activate
Witzigerweise befindet sich DANN der Cursor in keiner Zelle.
Heute in der Excelschulung schauen wir uns den Blattschutz an. Ich erkläre, dass man an den inaktiven Symbolen (beispielsweise in „Start“) erkennen kann, ob ein Tabellenblatt geschützt ist:
Eine Teilnehmerin meldet sich und sagt, dass bei ihr auch die Symbole in „Überprüfen“ ausgegraut sind:
Klar – der Cursor befindet sich in der Zelle und nicht auf der Zelle.
Und schon wieder reingefallen:
Die Visible-Eigenschaft des Worksheet-Objektes kann nicht festgelegt werden.
Ich will doch bloß die Visible-Eigenschaft im VBA-Editor ändern. Warum darf ich nicht?
GGGGRRRRR. Die Arbeitsmappe ist geschützt (Überprüfen / Schützen / Arbeitsmappe schützen).
Könnte Excel aber auch genauer sagen …
Ich programmiere ein Formular für einen Kunden. Einige Zellen sollen dynamische gesperrt oder entsperrt werden. Ich erhalte eine Fehlermeldung:
Seltsam: Der Befehl:
MsgBox Range(„K158“).Locked liefert False
Okay – noch ein Versuch:
Nutzt nichts! Ich schaue nach:
Ah! Verbundene Zellen. Ich darf nicht eine Zelle aus diesem Zellverbund sperren oder entsperren – dies funktioniert nur bei der ersten (hier: C158). Könnte mir Excel VBA ja auch sagen …
Schon blöde: Wir möchten einigen Mitarbeitern einer Firma eine Arbeitsmappe zur Verfügung stellen. Sie sollen die Mappe öffnen, drucken, ansehen, sortieren und filtern dürfen. Aber nichts ändern. Kein Problem: Man kann auf das Tabellenblatt einen Schutz legen und „sortieren“ und „filtern“ freigeben:
Filtern klappt hervorragend – jedoch: sortieren nicht!
Die Zelle oder das Diagramm, die bzw. das Sie ändern möchten, befindet sich auf einem schreibgeschützten Blatt. Um eine Änderung vorzunehmen, heben Sie den Schutz des Blatts auf. Möglicherweise werden Sie aufgefordert, ein Kennwort einzugeben.
Schon doof. Ich erstelle für einen Kunden ein dynamisches Excel-Formular. Die Daten sollen automatisiert ausgelesen werden. Deshalb muss ich wissen wie „groß“ das Formular ist, das heißt: wie viele Zeilen es enthält und wo sich bestimmte Informationen befinden.
Leider funktioniert die beiden Befehle
MsgBox Range(„A1“).CurrentRegion.Rows.Count
MsgBox Range(„A1“).SpecialCells(xlCellTypeLastCell).Row
nicht, wenn das Blatt geschützt ist. Also: Schutz aufheben!
Ja, ja, ich weiß: [Strg] + [A] und [Strg] + [Ende] funktionieren auch nicht in Excel …
Gestern in der Excelschulung fragte ein Teilnehmer, ob man die nicht gesperrten Zellen in einem geschützten Tabellenblatt hervorheben kann. Ich habe kurz überlegt: Es gibt keinen Organizer wie beispielsweise bei der Bedingten Formatierung oder bei den Namen. Mit Start / Bearbeiten / Suchen und Auswählen kann man eine Liste der gesperrten Zellen anzeigen lassen. Aber leider nicht markieren. Man kann mit der [Tab]-Taste über die nichtgesperrten Zellen springen und so herausfinden, wo offene, veränderbare Zelle liegen.
„Nein“ – lautet die Antwort, „hervorheben kann man sie nicht. Man muss sie per Hand formatieren. Beispielsweise grau.“
Schon blöde: (Intelligente, dynamische, formatierte) Tabellen lassen sich nicht erweitern, wenn das Blatt geschützt ist:
Verständlich: Man entsperrt einen festen Bereich und schützt anschließend das Tabellenblatt ohne diesen fest definierten Bereich. Er wächst leider nicht dynamisch mit.
Danke an Andreas Thehos, der dieses Problem auf den Excellent Days 2018 vorgestellt hat.
Der Teilnehmer der Excelschulung ist renitent. Er ruft mich ein drittes Mal, weil er nicht mehrere Zellen verbinden kann:
Auch hier ist der Fehler rasch gefunden: Als wir zuvor das Dokument geschützt haben, hat er „gespielt“ und die Arbeitsmappe geteilt. Bei dieser Option ist das Verbinden von Zellen verboten. Klar, oder?
Heute in der Excelschulung. Wir üben Blattschutz. Zuerst wird der Bereich ausgewählt, dessen Zellen veränderlich sind. Für sie wird im Dialog Zellen formatieren / Schutz die Option „Gesperrt“ entfernt. Anschließend das Blatt geschützt (Überprüfen / Schützen / Blatt schützen), was man am geänderten Symbol „“ erkennen kann.
Eine Teilnehmerin meldet sich und zeigt mir, dass die übrigen Zellen noch geschützt sind. Dass sie aber dennoch etwas eintragen kann:
Zuerst habe ich irritiert geschaut – aber dann bald des Rätsels gefunden: sie hatte einen Bereich freigegeben – das hat Vorrang gegenüber dem Blattschutz:
Das ist mir noch nie aufgefallen. Heute in der VBA-Schulung. Ich gebe als Übung auf zwei Makros zu erstellen: eines soll sämtliche Tabellenblätter schützen, eines soll den Blattschutz aufheben. Ein Teilnehmer testet und bemerkt ein Ruckeln:
Und hier der Code – falls jemand selbst testen möchte. Das Ruckeln erscheint beim Schutzaufheben:
Sub AlleBlätterSchützen()
Dim i As Integer
For i = 1 To ActiveWorkbook.Sheets.Count
ActiveWorkbook.Sheets(i).Protect
Next
End Sub
Sub Blattschutzaufheben()
Dim i As Integer
For i = 1 To ActiveWorkbook.Sheets.Count
ActiveWorkbook.Sheets(i).Unprotect
Next
End Sub
Ist das gewollt?
Ich erstelle eine Datenüberprüfung in der nur die Werte „Jena;Berlin;Stabstellen;H1;H2;H3;H4“ zugelassen sind.
Ich fülle eine Zelle mit H1. Ziehe herunter. Die Datenüberprüfung wird übergangen …
Wie hat denn das mein Kollege gemacht? In einer Tabelle befinden sich Formeln. Das Blatt ist nicht geschützt (die Namen darf ich beispielsweise ändern). Versuche ich jedoch die Formel zu ändern, erhalte ich eine Fehlermeldung:
ziemlich cleverer Schutz!
Die Antwort: Die Zellen, in denen sich Formeln befinden, wurden markiert und in der Datenüberprüfung die Formel <>““ eingegeben. So können sie nicht mehr geändert werden:
Übrigens: Das Löschen wird allerdings nicht verhindert!
Ich bin wirklich verblüfft. Wusste nicht, dass es geht:
Man erstellt eine Datenüberprüfung für mehrere Zellen (beispielsweise Zahlen, Datumswerte oder Listen).
Datenüberprüfung (Gültigkeit)
Diese (und einige anderen Zellen) werden über Zellen formatieren / Schutz „nicht gesperrt“. Das Blatt wird mit einem Blattschutz versehen. Nun ist es für den Anwender möglich, in die freien Zellen etwas einzutragen und diesen Inhalt in die Zellen mit Datenüberprüfung hineinzukopieren.
Das heißt: Das Einfügen von Inhalten unterläuft die Datenüberprüfung. Perfide!
Nix wird geprüft!
Danke an Julian für diesen Hinweis.
