Live-Forum - Die aktuellen Beiträge
Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender

Forumthread: Formeln mit VBA schützen?

Formeln mit VBA schützen?
03.12.2008 17:28:00
Karl
Hallo zusammen
Ich habe jede Menge Sheets in meinem Workbook. Und möchte jetzt das alle Formeln geschützt werden, so das Sie niemand verändert, oder versehentlich in den Zellen irgend was eingiebt.
Hat jemand eine Idee was ich da machen kann.
Karl
Anzeige

16
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Formeln mit VBA schützen?
03.12.2008 17:37:21
gerd
Hi,
einfach Blatt schützen(unter(extras, Schutz)
mfg gerd
AW: Formeln mit VBA schützen?
03.12.2008 17:41:09
Karl
Hallo Karl
Vielen Dank für die Antwort, aber leider habe ich in jedem Sheet ca 150 Formeln willkürlich auf dem Sheet verteilt. Und nur Diese Zellen sollen geschützt sein. Und das alles auf 200 Sheets. Das ist ja ne Lebensaufgabe jede einzelne Zelle anzuklicken und zu schützen. Geht das nicht mit VBA.
Grüße Karl
Anzeige
AW: Formeln mit VBA schützen?
03.12.2008 18:12:00
robert
hi,
probier einmal das makro
gruß

Sub formschutz()
On Error GoTo fehlerbeh
Dim wks As Worksheet
Application.ScreenUpdating = False
For Each wks In ThisWorkbook.Worksheets
ActiveSheet.Protect DrawingObjects:=False, Contents:=False, Scenarios:=False
With Cells
.Locked = False
.FormulaHidden = False
End With
With Cells.SpecialCells(xlFormulas, 23)
.Locked = True
'.FormulaHidden = True
End With
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
Next wks
Exit Sub
fehlerbeh:
If Err.Number  0 Then
MsgBox Err.Description
End If
Application.ScreenUpdating = True
End Sub


Anzeige
Formeln mit VBA schützen?
03.12.2008 17:41:00
Beate
Hallo Karl,
füge dieses Makro ins Codefenster "DieseArbeitsmappe":
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    'Es sollen nur die Zellen mit Formeln geschützt werden.
    'Bei Neueingaben von Formeln soll dies erkannt werden.
    Dim rng As Range
    For Each rng In Target.Cells
        If rng.HasFormula Then
            ActiveSheet.Protect
            Exit Sub
        Else
            ActiveSheet.Unprotect
        End If
    Next rng
End Sub


Wenn eine Formelzelle selektiert wird, wird das Blatt geschützt, ansonsten der Schutz aufgehoben.
Gruß,
Beate
Anzeige
AW: Formeln mit VBA schützen?
03.12.2008 17:54:31
Karl
Hallo Beate
Vielen Dank für die Rasche Antwort. Leider funktioniert es nicht perfekt. Ich kann in einigen Sheets trotzdem noch Zellen mit Formeln ankliken und verändern. Was mache ich falsch.
Grüß
AW: Formeln mit VBA schützen?
03.12.2008 18:00:00
gerd
Hi,
zunächst alle Zellen markieren und unter Format, Zellen, Schutz den Haken bei Schutz rausnehmen.
Dann mit strg + g, Inhalte..., Formeln wählen.
Unter Format, Zellen, Schutz den Haken bei Schutz setzen
Blattschutz setzen.
mfg gerd
Anzeige
AW: Formeln mit VBA schützen?
03.12.2008 18:41:48
Beate
Hallo Karl,
normalerweise sind standardmäßig von Excel die Zellen gesperrt. (Scheinbar sind da auf deinen Blättern teilweise schon Änderungen vorgenommen worden.) Wenn dann auch noch der Blattschutz gesetzt wird, kann man die Zellen nicht mehr verändern. Ich habe das mit der Zellsperrung ins Makro miteingebunden, nun müsste es aber auch bei dir funktionieren:
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    'Es sollen nur die Zellen mit Formeln geschützt werden.
    'Bei Neueingaben von Formeln soll dies erkannt werden.
    Dim rng As Range
    For Each rng In Target.Cells
        If rng.HasFormula Then
            ActiveSheet.Unprotect 'Blattschutz aufheben
            rng.Locked = True 'Zellsperrung
            ActiveSheet.Protect 'Blattschutz setzen
            Exit Sub
        Else
            ActiveSheet.Unprotect 'Blattschutz aufheben
            rng.Locked = False 'keine Zellsperrung
        End If
    Next rng
End Sub

Gruß,
Beate
Anzeige
etwas kürzer
03.12.2008 19:23:54
Beate
Hallo Karl,
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    'Es sollen nur die Zellen mit Formeln geschützt werden.
    'Bei Neueingaben von Formeln soll dies erkannt werden.
    Dim rng As Range
    ActiveSheet.Unprotect 'Blattschutz aufheben
    For Each rng In Target.Cells
        If rng.HasFormula Then
            rng.Locked = True 'Zellsperrung
            ActiveSheet.Protect 'Blattschutz setzen
            Exit Sub
        End If
    Next rng
End Sub


Gruß,
Beate
Anzeige
AW: Formeln mit VBA schützen?
03.12.2008 19:48:45
robert
hi,
karl will doch alle Blätter schützen
die meisten beiträge gehen von einem blatt aus, bzw müsste man in alle blätter
das change ereignis einfügen- oder?
gruß
Anzeige
AW: Formeln mit VBA schützen?
03.12.2008 20:09:35
Hajo_Zi
Hallo Robert,
der Code muss nur in das Change Ereignis unter DieseArbeitsmappe kopiert werden.

AW: Formeln mit VBA schützen?
04.12.2008 06:58:30
robert
Hallo Hajo,
ich habe mich auf dein beispiel bezogen-da ist nichts in - dieser Arbeitsmappe-
deshalb mein einwand-nichts für ungut
gruß
Robert
Anzeige
AW: Formeln mit VBA schützen?
04.12.2008 08:46:47
Hajo_Zi
Hallo Robert,
Du hattest geschrieben das sich der Code nur auf eine Tabelle bezieht. Da ist es klar das der Code unter der Tabelle ist und dieser Code muss nur unter dem enttrsprechenbden Ereignis unter DieserArbeitsmappe eingefügt werden.

Anzeige
AW: Formeln mit VBA schützen?
04.12.2008 09:01:21
Hajo_Zi
Hallo Robert,
Du hattest geschrieben das sich der Code nur auf eine Tabelle bezieht. Da ist es klar das der Code unter der Tabelle ist und dieser Code muss nur unter dem enttrsprechenbden Ereignis unter DieserArbeitsmappe eingefügt werden.

Anzeige
Danke Hajo- owT
04.12.2008 10:36:09
robert
AW: Formeln mit VBA schützen?
04.12.2008 00:04:00
Daniel
Hallo
mit diesem Code im Modul "DieseArbeitsmappe" kannst du verhindern, daß der Anwender eine Zelle mit Formel selektiert.

Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel  _
As Boolean)
Call KeinFormelSelektieren(Target)
End Sub



Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Call KeinFormelSelektieren(Target)
End Sub



Private Sub KeinFormelSelektieren(Bereich As Range)
Dim Zelle As Range
Set Bereich = Intersect(Bereich, Bereich.Parent.UsedRange)
If Not Bereich Is Nothing Then
For Each Zelle In Bereich
If Zelle.HasFormula Then
Bereich(1).Offset(1, 0).Select
Exit For
End If
Next
End If
End Sub


selektiert der Anwender eine Zelle mit Formel oder einen Bereich, der ein einer beliebigen Stelle eine Formel enthält, so wird die Selektion aufgehoben und nächste, darunterliegende Zelle ohne Formel selektiert.
dieser Code gilt für alle Sheets und alle Zellen der Datei.
allerdings ist diese Schutzfunktion sehr leicht auszuhebeln (durch wechseln in den Entwurfsmodus), dh. er schützt nur vor Bedienfehlern und nicht vor absichtlichen veränderungen.
Gruß, Daniel

Anzeige
;

Forumthreads zu verwandten Themen

Anzeige
Anzeige
Anzeige
Anzeige
Entdecke relevante Threads

Schau dir verwandte Threads basierend auf dem aktuellen Thema an

Alle relevanten Threads mit Inhaltsvorschau entdecken
Anzeige
Anzeige

Infobox / Tutorial

Formeln in Excel mit VBA schützen


Schritt-für-Schritt-Anleitung

  1. Öffne dein Excel-Dokument und gehe zu der Arbeitsmappe, in der du die Formeln schützen möchtest.

  2. Öffne den VBA-Editor:

    • Drücke ALT + F11, um den VBA-Editor zu öffnen.
    • Suche im Projektfenster nach "DieseArbeitsmappe".
  3. Füge den folgenden VBA-Code ein, um die Formeln in allen Zellen deiner Arbeitsblätter zu schützen:

    Sub formschutz()
       On Error GoTo fehlerbeh
       Dim wks As Worksheet
       Application.ScreenUpdating = False
       For Each wks In ThisWorkbook.Worksheets
           ActiveSheet.Protect DrawingObjects:=False, Contents:=False, Scenarios:=False
           With Cells
               .Locked = False
               .FormulaHidden = False
           End With
           With Cells.SpecialCells(xlFormulas, 23)
               .Locked = True
           End With
           ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
       Next wks
       Exit Sub
    fehlerbeh:
       If Err.Number <> 0 Then
           MsgBox Err.Description
       End If
       Application.ScreenUpdating = True
    End Sub
  4. Führe das Makro aus, um den Schutz für alle Zellen mit Formeln zu aktivieren.

  5. Testen: Versuche, die Zellen mit Formeln zu ändern, um sicherzustellen, dass der Schutz funktioniert.


Häufige Fehler und Lösungen

  • Problem: Die Zellen mit Formeln sind immer noch bearbeitbar.

    • Lösung: Stelle sicher, dass du alle Zellen zuerst entsperrst, bevor du den Blattschutz anwendest. Nutze dafür den Code von Gerd:
    ActiveSheet.Unprotect
    Cells.Locked = False
  • Problem: Blattschutz wird nicht korrekt angewendet.

    • Lösung: Überprüfe, dass keine anderen VBA-Skripte aktiv sind, die den Blattschutz aufheben.

Alternative Methoden

  • Manuelle Methode: Du kannst auch jede Zelle manuell markieren und unter „Format“ > „Zellen“ > „Schutz“ die Häkchen setzen. Dies ist jedoch zeitaufwendig, besonders bei vielen Blättern und Zellen.

  • VBA zur Verhinderung der Zellenauswahl: Verwende diesen Code, um zu verhindern, dass der Benutzer eine Zelle mit einer Formel auswählt:

    Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
       If Target.HasFormula Then
           Target.Offset(1, 0).Select
       End If
    End Sub

Praktische Beispiele

  1. Beispiel für den Schutz einer bestimmten Zelle: Wenn du nur eine bestimmte Zelle schützen möchtest, kannst du den Bereich anpassen:

    With Range("A1")
       .Locked = True
    End With
  2. Schutz auf mehreren Blättern anwenden: Der obige Code schützt alle Blätter in der Arbeitsmappe. Du kannst ihn anpassen, um nur bestimmte Blätter zu schützen, indem du If wks.Name = "DeinBlattName" Then verwendest.


Tipps für Profis

  • Backup machen: Bevor du mit VBA arbeitest, erstelle ein Backup deiner Excel-Datei, um Datenverlust zu vermeiden.
  • Dokumentation: Kommentiere deinen Code, damit andere (oder du selbst in der Zukunft) wissen, was jeder Abschnitt bewirken soll.
  • Testen: Teste deinen VBA-Code in einer Testumgebung, bevor du ihn auf wichtige Daten anwendest.

FAQ: Häufige Fragen

1. Wie kann ich Zellen mit Formeln schützen? Um Zellen mit Formeln zu schützen, kannst du VBA verwenden, um den Blattschutz für diese Zellen zu aktivieren, wie im obigen Code beschrieben.

2. Was passiert, wenn ich den Blattschutz aufhebe? Wenn du den Blattschutz aufhebst, kannst du alle Zellen bearbeiten, einschließlich der Zellen, die zuvor geschützt waren. Stelle sicher, dass du den Schutz wieder aktivierst, wenn du fertig bist.

3. Funktioniert das auch in Excel 365? Ja, die beschriebenen Methoden funktionieren auch in Excel 365 und anderen modernen Excel-Versionen.

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Entdecke mehr
Finde genau, was du suchst

Die erweiterte Suchfunktion hilft dir, gezielt die besten Antworten zu finden

Suche nach den besten Antworten
Unsere beliebtesten Threads

Entdecke unsere meistgeklickten Beiträge in der Google Suche

Top 100 Threads jetzt ansehen
Anzeige