Live-Forum - Die aktuellen Beiträge
Datum
Titel
28.03.2024 21:12:36
28.03.2024 18:31:49
Anzeige
Archiv - Navigation
1332to1336
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

Spalten auttomatisch ausblenden und sperren

Spalten auttomatisch ausblenden und sperren
07.10.2013 07:41:30
Chris
Hallo,
ich habe wieder mal ein Problem und bitte euch um Unterstützung:
Ich habe eine Excel-Tabelle auf die meherere Personen zugreifen können. Darin ist die Spalte H, die nur bestimmte Personen sehen dürfen. Deshalb möchte ich diese Spalte automatisch ausblenden und sperren/mit einem PW schützen.
Beim Öffnen soll die Spalte ausgeblendet und gesperrt sein. Per Schaltfläche - oder noch besser mit einem Eintrag in der Ribbon Bar - kann ich die Spalte mit einem PW wieder einblenden, entsperren und Eintragungen vornehmen. Wenn die Datei gespeichert wird, dann soll diese Spalte H wieder automatisch ausgeblendet und gesperrt werden.
Habt ihr dafür eine einfache Lösung?
Danke schon mal vorab.

13
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Spalten auttomatisch ausblenden und sperren
07.10.2013 09:11:05
Bastian
Hallo Chris,
das folgende Makro schaltet beim Speichern der Arbeitsmappe den Blattschutz von Tabelle1 ein mit dem Passwort 1234, und blendet Spalte H aus. Die Zellen der Spalte H werden geschützt, bei allen anderen Zellen wird der Schutz aufgehoben (damit diese auch nach Aktivierung des Blattschutzes bearbeitet werden können).
Den Code musst Du ins Codefenster der Arbeitsmappe kopieren.
Option Explicit
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
With Worksheets("Tabelle1")
.Cells.Locked = False
.Columns("H:H").Locked = True
.Columns("H:H").EntireColumn.Hidden = True
.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:="1234"
End With
End Sub

Zum Einblenden von Spalte H in und Aufheben des Blattschutzes (Tabellenblatt1) kannst Du folgendes Makro verwenden (in ein Modul kopieren):
Option Explicit
Sub EntsperrenUndEinblenden()
With Worksheets("Tabelle1")
.Unprotect
.Columns("H:H").EntireColumn.Hidden = False
End With
End Sub

Dieses kannst Du dann z.B. mit einem Button verknüpfen.
Gruß, Bastian

Anzeige
AW: Spalten auttomatisch ausblenden und sperren
07.10.2013 09:29:17
Chris
Danke Bastian,
aber es funktioniert irgendwie nicht.
Ich habe die Datei als *.xlsm abegespeichert, bin unten auf das Sheet "Tabelle1" gegangen und mit rechter Maustaste "Code anzeigen" angeklickt, den Code reinkopiert und gespeichert.
Aber die Spalte H wird nicht ausgeblendet und gesperrt.
Mache ich da noch was falsch?

AW: Spalten auttomatisch ausblenden und sperren
07.10.2013 09:34:39
Bastian
Hallo Chris,
der Code muss in das Codefenster der Arbeitsmappe (nicht in das des Tabellenblattes).
Nur in der Arbeitsmappe gibt es das Ereignis "bevore Save".
Du kannst z.B. F11 drücken, damit kommst Du in die Entwicklungsumgebung. Dann wählst Du im Projekt Explorer "Diese Arbeitsmappe" aus. In dieses Codefenster kopierst Du den Code.
Gruß, Bastian

Anzeige
AW: Spalten auttomatisch ausblenden und sperren
07.10.2013 10:04:58
Chris
Super hat soweit funktioniert.
Da das Sheet aber trotzdem normal bearbeitet werden soll, außer halt die Spalte H, hab ich das Ganze wie folgt erweitert (es dürfen Spalten und Zeilen eingefügt und gelöscht werden etc.
With Worksheets("Tabelle1")
.Cells.Locked = False
.Columns("H:H").Locked = True
.Columns("H:H").EntireColumn.Hidden = True
.Protect DrawingObjects:=True, Contents:=True, Scenarios:=False, AllowFormattingCells:=True, AllowFormattingColumns:=True, _
AllowFormattingRows:=True, AllowInsertingColumns:=True, AllowInsertingRows:=True, _
AllowDeletingColumns:=True, AllowDeletingRows:=True, AllowSorting:=True, AllowFiltering:=True, _
AllowUsingPivotTables:=True, Password:="1234"
End With
Und jetzt kommt beim Speichern folgender Fehler:
Laufzeitfehler '1004':
Die Locked-Eigenschaft des Range-Objektes kann nicht festgelegt werden.
Hast du da ne Idee?

Anzeige
AW: Spalten auttomatisch ausblenden und sperren
07.10.2013 10:16:17
Chris
Ich habs jetzt hinbekommen, es funktioniert.
Das einzige Problem das ich noch habe ist, daß ich keine Zeilen und Spalten einfügen kann, wenn das Sheet gesprerrt ist.

AW: Spalten auttomatisch ausblenden und sperren
07.10.2013 10:20:19
Bastian
Hallo Chris,
das Einfügen von Spalten und Zeilen sollte aber möglich sein, und auch das löschen von Spalten.
Du kannst nur keine Zeilen löschen, da diese ja auch die gesperrte Spalte H kreuzen.
Gruß, Bastian

AW: Spalten auttomatisch ausblenden und sperren
07.10.2013 10:35:49
Chris
Hatte ich fast befürchtet. Ich habe aber ein bißchen rumgespielt und eine Lösung gefunden:
Wenn ich eine Zeile oder Spalte komplett markiere (also nicht nur eine Zelle) dann kann ich auch die markierte Zeile oder Spalte löschen und auch einfügen.
Jetz bin ich fast am Ende. Ich habe mir eine Schaltfläche gebastelt für das Einblenden und entsperren, funktioniert soweit. Wenn ich aber kein oder ein falsches Passwort eingebe, dann entsperrt er zwar nicht die Spalte, aber er blendet sie ein und das darf nicht sein. Kann man das Einblenden der Spalte vom richtigen Eingeben des Passwortes abhängig machen?
Und gibt es eine Möglichkeit, in der Ribbon-Bar eine Benutzerdefinierte Schaltfläche für das Entsperren und Einblenden der Spalte zu machen, die nur bei den Usern sichtbar ist, die die Spalte freischalten dürfen?

Anzeige
AW: Spalten auttomatisch ausblenden und sperren
07.10.2013 11:07:29
Bastian
Hallo Chris,
hier erstmal das angepasste Makro zum Entsperren und Einblenden von Spalte H:
Option Explicit
Sub EntsperrenUndEinblenden()
On Error GoTo ErrotHandler
With Worksheets("Tabelle1")
.Unprotect
.Columns("H:H").EntireColumn.Hidden = False
End With
Exit Sub
ErrotHandler:
MsgBox "Falsches Passwort", vbCritical
End Sub

Zu Deinem Wunsch, eine Schaltfläche in der Ribbon-Bar hinzuzufügen, mal ein kurzes "Kochrezept":
Schau mal, ob Du damit zurecht kommst (ansonsten gibt es auch im Netz gute Anleitungen)
1. Gehe in das Menü Datei > Optionen > Menüband anpassen
2. Wähle "Neue Registerkarte" (Über "Umbenennen" einen Namen vergeben)
3. Die neue Registerkarte enthält automatisch eine neue Gruppe (auch dieser über "Umbenennen" eien Namen vergeben)
4. jetzt unter "Befehle auswählen" (oben links) im Dropdown "Makros" wählen.
5. In der Liste darunter wirst Du nun auch Dein Makro finden. Dieses Auswählen, und mit "Hinzufügen" der neu erstellten Gruppe zuweisen.
Die Anpassung des Menübands gilt jeweils für das Microsoft Office-Programm, in dem man sich zum Zeitpunkt der Bearbeitung befindet. Wenn diese Anpassung also nur bei den Personen durchgeführt wird, die die Spalte freischalten sollen, dann ist die Schaltfläche auch nur dort sichtbar.
Allerdings ist das angepasste Ribbon bei diesen Personen dann immer sichtbar, wenn Excel geöffnet wird, also nicht nur bei der einen Arbeitsmappe.
Gruß, Bastian

Anzeige
AW: Spalten auttomatisch ausblenden und sperren
07.10.2013 11:29:44
Chris
Ihr seid die Besten - funktioniert alles wunderbar.
Kann man eigentlich auch noch den Zugang zum VBA-Code sperren, weil ja da das Password hinterlegt ist?

AW: Spalten auttomatisch ausblenden und sperren
07.10.2013 11:34:32
Bastian
Hallo Chris,
das VBA-Projekt kannst Du folgendermassen sperren:
1. Mit F11 in die Entwicklungsumgebung
2. Im Menü unter "Extras" "Eigenschaften von VBA Projekt" wählen
3. Dort unter dem Reiter "Schutz" die Option "Projekt für die Anzeige sperren" aktivieren und entsprechendes Passwort vergeben und mit "Ok" abschließen.
Gruß, Bastian

AW: Spalten auttomatisch ausblenden und sperren
07.10.2013 12:10:16
Chris
Funktioniert einwandfrei, Danke nochmals für die Unterstützung.

Anzeige
AW: Spalten auttomatisch ausblenden und sperren
09.10.2013 20:53:51
Chris
Nach eingehendem Test haben sich 2 Probleme ergeben, wobei das 1. Problem das größere ist:
1. Die Spalte H kann auch im gesperrten Arbeitsblatt eingeblendet werden. Ich habe beim Schützen des Blattes alle Parameter freigegeben außer dem Ersten (gesperrte Zellen auswählen) weil ja mit dem Sheet und den restlichen Daten darin normal gearbeitet werden soll und muß. Wenn ich "Spalten einfügen" rausnehme und dann nach dem Speichern die Spalten G...I markiere hängt sich Excel auf. Habt ihr da eine Lösung dafür?
2. Die Überschriften sind mit dem Autofilter versehen. Hier kann ich fast alles filtern außer die beiden Sortierfunktionen, die gehen im gesperrten Zustand nicht (hier kommt eine Meldung, daß das Arbeitsblatt geschützt ist). Habt ihr dafür eine Erklärung?
Danke schon mal vorab.

Anzeige
AW: Spalten auttomatisch ausblenden und sperren
07.10.2013 10:16:26
Bastian
Hallo Chris,
Sorry, das hatte ich übersehen.
Der Fehler tritt auf, wenn Du die Arbeitsmappe mehrfach hintereinander speicherst. Dann ist die Spalte H ja bereits gesperrt. Jetzt versucht das Makro in der ersten Zeile im With-Block alle Zellen zu entsperren, kann aber auf die Zellen in Spalte H nicht zugreifen.
Das sollte man noch abfangen, indem das Makro schaut, ob die Spalte H bereits ausgeblendet ist oder nicht.
Hier der geänderte Code:
Option Explicit
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
With Worksheets("Tabelle1")
If .Columns("H:H").EntireColumn.Hidden = True Then Exit Sub
.Cells.Locked = False
.Columns("H:H").Locked = True
.Columns("H:H").EntireColumn.Hidden = True
.Protect DrawingObjects:=True, Contents:=True, Scenarios:=False, AllowFormattingCells:= _
True, AllowFormattingColumns:=True, _
AllowFormattingRows:=True, AllowInsertingColumns:=True, AllowInsertingRows:=True, _
AllowDeletingColumns:=True, AllowDeletingRows:=True, AllowSorting:=True, AllowFiltering: _
=True, _
AllowUsingPivotTables:=True, Password:="1234"
End With
End Sub

Gruß, Bastian
Anzeige

301 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige