Live-Forum - Die aktuellen Beiträge
Datum
Titel
28.03.2024 21:12:36
28.03.2024 18:31:49
Anzeige
Archiv - Navigation
1176to1180
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

nur Formel schützen

nur Formel schützen
Bernd
Hallo,
welchen Code muß ich verwenden, damit alle Formeln einer Exceldatei (mehrere Tabellenblätter) auf "Knopdruck" gegen evtl. Ändern geschützt sind? Das Entsperren sollte analog passieren. Das Passwort kann im Code eingegeben sein. Der Code sollte in der personal.xla angesiedelt sein.
Gruß
Bernd
AW: nur Formel schützen
27.09.2010 12:08:40
Klaus
Hallo Bernd,
probiers mal damit.
Sub ProtectMe()
Dim r As Range
Dim myPass As String
myPass = InputBox("Passwort für Blattschutz eingeben")
With ActiveSheet
On Error GoTo myErr
.Unprotect myPass
On Error GoTo 0
For Each r In .UsedRange
r.Locked = r.HasFormula
Next r
.Protect myPass
End With
End
myErr:
MsgBox ("Abbruch - Passwort falsch")
End Sub
Grüße,
Klaus M.vdT.
AW: nur Formel schützen
27.09.2010 13:56:32
Bernd
Hallo,
leider gibt es noch Probleme:
1. Der Code funktioniert nur für das aktive Tabellenblatt, ich benötige aber den Schutz für alle Tabellenblätter
2. Die Zellen des Tabellenblattes müssen über den Excel-Befehl: Format/Zelle/Schutz/Gesperrt generell
wohl einmalig entsperrt werden, sonst sperrt der Code auch Zellen ohne Formel.
3. Das Passwort sollte im Code hinterlegt sein.
Hintergrund :
Ich muß die Prozedur auf mindestens hundert Excel-Dateien anwenden, deshalb sollte die Aktivierung (ohne Passworteingabe) funktionieren!
Es wäre schön, wenn das noch verbesserbar wäre!
Danke und Gruß
Bernd
Anzeige
AW: nur Formel schützen
27.09.2010 14:07:47
Klaus
Hallo Bernd,
1) Jo, hattest du geschrieben. Hab ich mal eingebaut (for each wks ...)
2) Nö, macht der Code nicht. Vielmehr ist es so, dass Zellen standardmäßig gesperrt sind wenn du ein neues Blatt anlegst. Der Code entsperrt nur die nicht-Formelzellen in der "used range".
Egal, das ist ein rein akademisches Problem. Ich entsperre einfach alle Zellen per Code.
3) Hab ich gemacht. Ich hatte deine Forderung "im Code eingegeben" Fehlinterpretiert, dass du das Passwort während des Code eingeben möchtest ... Das Passwort lautet jetzt "Passwort123", kannst du beliebig ändern.
Hier der neue Code:
Sub ProtectMe()
Dim r As Range
Dim myPass As String
Dim wKs As Worksheet
myPass = "Passwort123"
For Each wKs In ActiveWorkbook.Worksheets
With wKs
On Error GoTo myErr
.Unprotect myPass
On Error GoTo 0
.Cells.Locked = False
For Each r In .UsedRange
r.Locked = r.HasFormula
Next r
.Protect myPass
End With
Next wKs
End
myErr:
MsgBox ("Abbruch - Passwort falsch")
End Sub

Anzeige
AW: nur Formel schützen
27.09.2010 14:34:19
Bernd
Hallo nochmal,
jetzt sieht es wirklich gut aus, ein erster Probelauf mit einer "einfachen" Datei hat problemlos funktioniert.
Leider habe ich aber ein Problem bei einer komplizierteren Datei, wo ich folgenden Fehler erhalte:
Laufzeitfehler 1004: Die Locked-Eigenschaft des Range-Objektes kann nicht festgelegt werden.
Das Makro bleibt bei der Anweisung " r.Locked = r.HasFormula" hängen. Ich meine, auch eine Fehlermeldung bzgl. eines Array gesehen zu haben. Nach mehrmaligen Ausführen des Makros taucht diese Meldung aber nicht mehr auf.
Kann es Probleme mit bestimmten Formatierungen/Formeln geben?
Gruß
Bernd
Anzeige
AW: nur Formel schützen
27.09.2010 14:42:44
Bernd
Hallo,
konnte gerade noch feststellen, dass es auch Probleme mit verbundenen Zellen gibt. Ich meine mich auch erinnern zu können, das mir mal jemand deutlich gesagt hat, dass verbunde Zellen für VBA ziemlich problematisch sind.
Gruß
Bernd
Array v.s. "For each r in .usedrange"
27.09.2010 14:53:06
Klaus
Hallo Bernd,
beide Fehler (verbundene Zellen, Arrays) konnte ich replizieren.
verbundene Zellen in VBA sind ziemlich problematisch. Lösung: verzichte auf verbundene Zellen.
Ein Array kann nur im ganzen geändert werden. Das Makro versucht aber, jede Zelle des Array einzeln zu ändern. Lösung: keine Ahnung, sorry. Das übersteigt meinen VBA Horizont.
Ich stell die Frage mal mit einem neuen Betreff wieder auf offen, vielleicht meldet sich ja jemand mit einer Idee.
Grüße,
Klaus M.vdT.
Anzeige
AW: nur Formel schützen
27.09.2010 15:15:09
Bernd
Hallo nochmal,
ich wollte doch nochmal nachhaken,
grundsätzlich kann ich doch mit dem Makrorekorder folgenden Vorgang aufzeichnen:
Alles markieren, Format Zellen, dort "gesperrt" deaktivieren und dann auf Gehe zu / Inhalte / Formeln. Lässt sich das nicht in den Code irgendwie einbauen?
Gruß
Bernd
Super Idee!
27.09.2010 15:26:30
Klaus
Hi Bernd,
Super Idee! Das nimmt auch die Schleife raus, dadurch wird der Code richtig schnell.
Sub ProtectMe()
Dim r As Range
Dim myPass As String
Dim wKs As Worksheet
myPass = "Passwort 123"
For Each wKs In ActiveWorkbook.Worksheets
With wKs
On Error GoTo myErrPass
.Unprotect myPass
On Error Resume Next
.Cells.Locked = False
.Cells.SpecialCells(xlCellTypeFormulas, 23).Locked = True
On Error GoTo 0
.Protect myPass
End With
Next wKs
End
myErrPass:
MsgBox ("Abbruch - Passwort falsch")
End
End Sub
Das erste "on Error" fängt ein falsches Blattschutz-Passwort ab, das zweite "on Error" lässt den Code weiter laufen, falls in einem Blatt keine Formel-Zellen gefunden werden.
Grüße,
Klaus M.vdT.
Anzeige
Danke für das Kompliment!
27.09.2010 15:56:22
Bernd
Hallo,
vielen Dank für die Blumen! Wenigstens konnte ich auch einen kleinen Teil zum Gelingen beisteuern! So wie es aussieht läuft der Code wirklich stabil auch mit Array und verbundenen Zellen. Ich werde das in den nächsten Tagen noch anderen vielen anderen Dateien testen können.
Da I-Tüpfelchen aus meiner Sicht wäre dann noch, wenn man den Code auf ein ganzes Verzeichnis mit Exceldateien bzw. über so eine Art Dateimenüauswahl auf bestimmte Dateien, vollautomatisch anwenden könnte, d. h. man wählt die Dateien aus, der Code schützt die Formeln und vergibt ein einheitliches Passwort und schließt die Dateien wieder. fcs/Franz hat mir da schon mal in der Richtung was gebastelt.
Vielleicht liest es den Beitrag noch!
Auf jedem Fall schon mal besten Dank!
Gruß
Bernd
Anzeige
Danke für die Rückmeldung! m.w.T
28.09.2010 07:40:12
Klaus
Hallo Bernd,
mit "per VBA Dateien finden und öffnen" steh ich leider auf Kriegsfuss ;-) Prinzipiell sollte das aber möglich sein. Würd ich nochmal seperat nach Fragen.
Freut mich dass ich helfen konnte!
Grüße, Klaus M.vdT.
...Der dir das mal gesagt hat, hatte nur...
27.09.2010 19:20:47
Luc:-?
…zu wenig Ahnung, Bernd…!
VBA hat keinerlei Probleme mit VerbundZellen, ist sogar gut darauf eingestellt. Man muss nur die dafür eingerichteten Zelleigenschaften kennen und auch benutzen…!
Gruß Luc :-?
@Luc : Widerspruch !
27.09.2010 19:43:55
NoNet
Hey Luc,
Der dir das mal gesagt hat, hatte nur...…zu wenig Ahnung, Bernd…!

Weißt DU denn, von wem Bernd diese Info hat ?
ICH habe keine Ahnung, WER Bernd das gesagt hat, es hätte aber auch gut ICH sein können !
Klaro kann man per .MergeArea, .MergedCells und .Merge mit verbundenen Zellen arbeiten, aber es gibt in puncto VBA mehr Probleme mit verbundenen Zellen als Vorteile. Vor allem bei Standardroutinen (z.B. zeilenweises Abarbeiten in einer Schleife, Autofilter, Bereiche kopieren etc.), die keine verbundenen Zellen berücksichtigen, können hier Probleme bereiten. Aber das muss ich DIR als "altem VBA-Hasen" sicherlich nicht erzählen ;-))
Salut, NoNet
Aktuelle Infos und Anmeldung zum => Exceltreffen 2010 <= in Neubrandenburg (15.-17.10.2010) :
Treffen der Excel-Freunde und zahlreicher Helfer aus diversen Excel-Foren.
Auch DU kannst daran teilnehmen ...=> Klicke hier <=

Anzeige
AW: @Luc : Widerspruch !
28.09.2010 09:43:26
Bernd
Hallo zusammen,
ich meine auch, dass mir das damals im Zusammenhang mit Routinen ans "Herz" gelegt wurde, verbundene Zellen grundsätzlich zu meiden....
Viele Grüße nochmals
Bernd
...Dann muss man das eben auch bei...
28.09.2010 17:14:20
Luc:-?
…Routinen berücksichtigen, NoNet,
denn man kann ja nicht ausschließen, dass jemand so etwas verwendet, wenn es doch geht! Das wäre ja genauso, als wenn man Vögeln das Fliegen auf Flughäfen verbieten wollte, weil das zu Unfällen bei Start u.Landung führen könnte…! Da müssen halt geeignete Maßnahmen ergriffen wdn… Und was die genannten Eigenschaften betrifft…
Es reicht nicht, nur die Speisepilze zu kennen, man muss auch wissen, wie die Giftpilze aussehen und hier, wie sich andere übliche Befehle auf diesen Objekten verhalten — „mit dem Strom“ und „gegen den Strom“… Da gibt's auch Unterschiede…
Gruß Luc :-?
PS: „…zu wenig…“ ist übrigens relativ und bedeutet keinesfalls keine Ahnung, sondern nur zuwenig derselben in Bezug auf das spezielle Problem… ;-)
Anzeige

Links zu Excel-Dialogen

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige