Office: (Office 365) Zellen unterschiedlich schützen

Hallo liebe Excel-Experten,
ich habe folgendes Problem, für das ich weder im Netz noch hier im Forum einen Beitrag gefunden habe:
Ich würde in einer Excel-Datei gerne einige Zellen komplett vor Zugriff schützen, andere Zellen aber zur Bearbeitung zulassen. Soweit kein Problem. ABER ich möchte gerne die Formatierung der gesamten Datei (auch in den ausfüllbaren Zellen) vor Manipulation schützen.
Wenn ich über den Zellschutz/ Blattschutz gehe, kann ich ja verschiedene Ebenen von Schutz an-/abschalten, aber eben immer nur für alle Zellen mit Schutz. Gibt es irgendwie die Möglichkeit alle Zellen für Änderungen in der Formatierung zu schützen und zusätzlich z.B. alle Zellen, die eine Formel beinhalten, komplett vor dem Zugriff zu schützen?
Ich bin gespannt auf eure Ideen! :-)
LG, berthi

 

Halo mberthi,

Formel schützen mit VBA
http://hajo-excel.de/gepackt/vba/formel_schuetzen.zip

 

Theoretisch kann mensch bei eingeschalteten Blattschutz die Formatierung nicht ändern, außer .. bei ungeschützten Zellen mit Copy & Paste. Da kann mensch dann entweder jeden Paste-Befehl auf "Paste as values" umbiegen (Was ziemlich nervig ist, da es für alle offenen Worksheets gilt), oder die Eingabefelder mit VBA nach der Änderung wieder in den alten Format-Zustand bringen. Das zweite mache ich ständig und nenne es anwenderfreundlich "Selbstreparierend", ist aber bei komplizierten Formatierungen gerne mal viel Arbeit.

Dir sollte auch klar sein das dieser Schutz nichts wert ist, sondern nur dazu dient reine Anwender:innen vom Rumfummeln abzuhalten. Das Passwort zu entfernen, dauert keine Minute.

Pan

 

Danke Hajo und Pan für eure schnellen Antworten. Ich bin in VBA noch nicht sonderlich bewandert. Aber mit deiner Beispieldatei Hajo werde ich mal "weiter rumprobieren". :-) Wenn ich das richtig verstehe, könnte ich über VBA bestimmte Zellen komplett vor dem Zugriff schützen und über den "Standard-Blattschutz" gleichzeitig alle Zellen vor Formatänderungen schützen?
Mir ist klar, dass es kein wirklicher Schutz vor mutwilliger Manipulation ist. Es geht mehr darum, dass unachtsam hantierende Kollegen (für die es keinen Unterschied zwischen "Spalte ausblenden" und "Spalte löschen" gibt, "ich habs eingegeben, aber es steht nicht in der Zelle! Huch, wo ist denn überhaupt mein Cursor...") davon abzuhalten, aus Versehen lange Formeln zu überschreiben und die Formatierung der Tabellen durch Copy/Paste ständig zu zerschießen. Wenn etwas passwortgeschützt ist, werden sie gar nicht erst versuchen, das Passwort rauszufinden... ;-)
Lieben Gruß, berthi

 

Hallo Berthi,

damit die Arbeit nur mit aktvierten Makros möglich ist schaue hier.
VBA nur mit Makros

Gruß Hajo

 

Hallo Hajo, hallo Pan,
vielen Dank nochmal für eure Rückmeldungen. Das hilft mir sehr! Schon vorab sorry für den langen Text...

Ich habe nun an meiner Datei weitergebastelt. Die Einstellung, "VBA nur mit Makros" von Hajo habe ich problemlos umsetzen können. Um die Formel zu schützen habe ich mir die "Abfrage mit Passwort" rausgesucht. Das funktioniert auch, wenn ich es einem Tabellenblatt zuordne. Nun hätte ich dazu noch 3 Fragen:
1. Kann ich den Code so ändern, dass nach Abfrage des Passwortes nur die Zelle freigegeben wird, die gerade angewählt wurde? Im Moment gibt man einmal das Passwort ein und dann sind alle Zellen mit Formel freigeschaltet bis die Datei wieder abgespeichert wird. Ich hätte aber gerne in jeder Zelle wieder Hinweis und die Passwortabfrage, wenn der Nutzer ein Feld mit Formel anwählt.
2. Ich habe versucht diesen VBA Code mit in den bestehenden "VBA nur mit Makros" für die ganze Arbeitsmappe zu integrieren, um ihn nicht auf jedes Arbeitsblatt setzen zu müssen. Mangels Verständnis für den Aufbau des Codes war mein Copy/ Paste allerdings wenig erfolgreich... Könntest du mir korrigieren, was in meinem Aufbau fehlt/falsch ist? Ich hänge meine Testdatei und eine Textdatei mit dem ausprobierten Code an.
3. Wenn man das Passwort nicht weiß, landet man ggf. in einer langen Schleife Paswortabfragen. Mir scheint, dass Excel bei fehlendem Passwort dann in das nächste rechte Feld springt. Ist dort aber auch eine Formel hinterlegt, wird natürlich gleich wieder die Abfrage gestartet usw. Kann ich im Code angeben, dass nach Beendigung der Passwortabfrage ohne korrektes Passwort der Cursor in das nächste freie Feld ohne Formel springt? Oder meinetwegen immer auf A1 oder so?

Dann habe ich noch eine andere Frage zu meiner Datei. In Spalte U (Grund Aussendung) wird entweder der Wert aus der ausgeblendeten Hilfsspalte T wiedergegeben oder falls nichts vorhanden, soll der Anwender aus einer Liste einen Eintrag auswählen. Die Hilfsspalte benötige ich, da diese den Eintrag aufgrund der Auswahl in Spalte S (Name Firma) mit einer Formel ermittelt. Die Datenüberprüfung in Spalte U wiederum lässt keine Wenn-Formel mehr zu, sondern eben nur die vordefinierten Einträge, bzw. einen Verweis auf eine andere Zelle mit einem passenden Eintrag.
Nun zum Problemchen: Wenn ich die Datei neu öffne, wird in der Hilfsspalte T die Formel nicht sofort umgesetzt, sondern #WERT! angezeigt. Wenn man einmal eine der Zellen auswählt und die Formel neu mit Enter bestätigt, wird alles korrekt angezeigt. Das ist allerdings ungünstig, denn ich will ja gar nicht, dass der Anwender unnötigerweise eine Zelle mit Formel anwählt. Woran kommt diese Fehlermeldung und kann ich sie irgendwie vermeiden?

Und noch eine letzte Frage an Pan: Ich glaube, für meine Zwecke wäre es prima, wenn ich jeden Paste-Befehl in einen Paste-As-Value-Befehl umbiegen würde. Für die betreffenden Anwender wäre es wahrscheinlich sogar auch für andere Tabellen mehr hilfreich als nervig... ;-) Ich habe versucht, das ganze über "Makro aufzeichnen" hinzukriegen, allerdings ohne funktionierendes Ergebnis. Ich habe auch einen Vorschlag im Netz gefunden und ausprobiert, aber leider auch ohne Erfolg. (in meiner Testdatei für Tabelle 2 eingegeben). Kannst du mir sagen, wie ich das in VBA umsetzen müsste? Und natürlich sollte es auf die ganze Arbeitsmappe angewendet werden und nicht nur auf Arbeitsblatt 2.

Sorry für meine dilettantischen Nachfragen. Leider kenne ich mich in VBA bislang zu wenig aus.
Vielen Dank für eure Hilfe!
Lieben Gruß,
berthi

 

Ich wollte nicht einfach nicht antworten, deswegen:
Ich bin raus, weil ich dir wahrscheinlich nicht erklären warum der Aufbau der Datei das Problem ist.

Die Kurse sind eine 1:n Relation in einem Datenfeld, die Werte dazu sind auch nicht berechnet sondern manuell.
Dann berechnest du aus dem Kurs-String einzelne Marker. Der bessere Weg wäre andersrum. Einzelwerte -> Gruppenanzeige, sonst kriegst du nur Probleme wenn Menschen damit arbeiten, Menschen verschreiben sich ständig.

Wenn du es andersrum baust. kannst du die Werte für Gebühren, Zubehör und Anmeldung aus einer Referenztabelle (schaust du dir bitte mal "Als Tabelle formatieren" an) ziehen und vermeidest auch hier menschliche Fehler. Denk an der Stelle bitte daran die Preistabelle mit einem Gültigkeitsdatum zu versehen, sonst kannst du keine Preisanpassungen über die Zeit machen.

Neuer Kunde ist auch kein manuelles Datenfeld, sondern nur die Auswertung eines Counters. (Bisher kein Auftrag = "Neuer Kunde") . Dito für Monat, das ist eine Berechnung aus Vertragsdatum.

Verwende statt der 1 im Quartal besser ein "x", statt sum arbeitest du dann mit count, die 1 macht auf der UserInterface Seite nur Sinn wenn es auch einen zwei gibt.

Die Anmerkung in Spalte U, beinhaltet automatisch das du hier auch wieder VBA brauchst. Würde ich vermeiden und in einer weiteren dann rein manuellen Spalte abfangen.

Dann wäre da noch das Thema wie man die Eingabe von der Darstellung und den Analytics trennt.

Alternativer Ansatz:
Für mich sehen die Daten aus wie eine Mischung aus CRM und Finance .. und ich hab nirgendwo Datenquellen gesehen.
Weißt du was ein CRM ist?

Pan

 

Hallo Pan,
danke trotzdem für deinen Erklärungsversuch. ;-) Ich verstehe durchaus deine Anmerkungen.

Das Problem ist, dass wir ein kleiner Laden sind mit einem Nischenprodukt. Bei uns werden 2 verschiedene Systeme genutzt und nicht ein CRM wo alles zusammenläuft. So haben wir ein System, wo Kundendaten, Korrespondenz, Kurse etc. abgespeichert werden und ein zweites System, in dem nochmals die Kundendaten und Vertragsdaten bei Vertragsabschluss eingegeben werden und die Rechnungen erstellt werden. Das Vertragssystem auch kein Stardardsystem, sondern wurde vor ewigen Jahren für die damaligen Bedürfnisse geschneidert. Reporting gehörte offensichtliich nicht dazu.
Beide Systeme sind aktuell in keiner Weise miteinander kompatibel... Daher erfolgt die Auswertung mehr oder weniger manuell in Excel und ich versuche diese Datei für den Anwender so handhabbar wie möglich zu gestalten. Die eigentlich Auswertung besteht noch aus 20 Reitern mehr, dies hier ist nur das 1. Datenblatt aus welchem dann die Auswertung erstellt wird.

Die Spalten A bis O auf dem ersten Datenblatt sind 1 zu 1 der Excel-Export aus dem Vertragssystem. Mit diesen Daten muss ich weiterarbeiten. Daher habe ich bei den Kursen leider zunächst keine Einzeldaten, sondern dieses Sammelsurium pro Vertrag. Auch die Gebühren und Zubehörkosten sind also Export aus dem Vertragssystem. Eine Referenzierung auf eine Kostentabelle ist an dieser Stelle auch nicht möglich, da bei unterschiedlichen Kunden unterschiedliche Rabatte angewendet werden, die Gesamtsumme hier aber nur als Endsumme aufgeführt wird. Auch die 1ser statt Xse bei den Quartalen, die Zuordnung "neuer Kunde" etc. kommt eben alles in dieser Form aus dem Vertragssystem.

Dass dies keine optimalen Voraussetzungen sind ist mir klar, aber das ist meine Vorgabe mit der ich arbeiten muss.
Dazu kommt, dass ich normalerweise nicht das Reporting monatlich erstelle, sondern Kollegen, die in der Tat noch weniger Ahnung von Excel haben als ich. ;-) Daher muss ich die Möglichkeit geben, den Export aus dem Vertragssystem mit Copy/ Paste hier in meine Datei einzufügen ohne dass der Anwender noch große Anpassungen vornehmen muss.

Daher war meine Frage an dich, wie ich Copy/Paste in Paste-as-Value erzwingen kann, damit bei eben diesem Einkopieren der Daten aus dem Vertragssystem nicht jedesmal die Formatierung zerschossen wird. Die Umsetzung dürfte ja eigentlich unabhängig von dem Aufbau der Tabelle sein, oder? :-)

Ich würde mich freuen, wenn du dazu doch noch einen Hinweis hättest. Falls nicht, trotzdem danke für deinen Input.
Herzlichen Gruß,
berthi

 

Hallo nochmal,
das Problem in Spalte U habe ich nun gelöst, indem ich direkt auf die Nebenzellen verweise. Dann wird die Zelle nach dem Öffnen der Datei auch ohne Fehler angezeigt.
Lieben Gruß,
berthi

 

Wenn du mir diesen Export zur Verfügung stellst, bau ich dir eine PowerQuery die die Datei abfragt, dann schenkst du dir schon mal 1 Copy & Paste.

Pan