Tutorials

Tutorial: Formeln schützen, aber Eingaben erlauben mit Excel VBA

Melden

Hier ist ein ausführliches Tutorial, wie du in Excel mithilfe eines VBA-Makros alle Formeln sperrst, während Eingabefelder (Konstanten) weiterhin bearbeitbar bleiben.

In Excel ist es oft wichtig, dass Benutzer Daten eingeben können, aber die dahinterliegenden Berechnungen (Formeln) nicht versehentlich löschen oder verändern. Manuell ist dies über die Zellformatierung mühsam. Mit VBA lässt sich dieser Prozess automatisieren.

Warum VBA verwenden?

Anstatt jede Zelle manuell zu prüfen, erkennt das Makro automatisch alle Zellen, die Formeln enthalten, sperrt diese und gibt den Rest des Tabellenblatts für Eingaben frei.

Schritt 1: Die Entwicklertools aktivieren

Um VBA zu nutzen, musst du den Reiter Entwicklertools in Excel sehen.

  1. Klicke mit der rechten Maustaste auf ein beliebiges Menüband (z. B. "Start").
  2. Wähle Menüband anpassen.
  3. Setze rechts den Haken bei Entwicklertools und bestätige mit OK.

Schritt 2: Den VBA-Editor öffnen

  1. Gehe zum Reiter Entwicklertools.
  2. Klicke auf Visual Basic (oder drücke ALT + F11).
  3. Klicke im Menü auf Einfügen -> Modul. Ein leeres weißes Fenster erscheint.

Schritt 3: Der VBA-Code

Kopiere den folgenden Code und füge ihn in das neue Modul ein:

Sub FormelnSperrenEingabeErlauben()
    Dim ws As Worksheet
    Dim passwort As String

    ' Hier kannst du dein Passwort festlegen
    passwort = "meinPasswort123"

    Set ws = ActiveSheet

    On Error Resume Next

    ' 1. Blattschutz aufheben, um Änderungen vorzunehmen
    ws.Unprotect Password:=passwort

    ' 2. Zuerst die Sperre für ALLE Zellen aufheben
    ws.Cells.Locked = False

    ' 3. Nur Zellen mit Formeln finden und sperren
    ' xlCellTypeFormulas identifiziert alle Zellen mit Formeln
    ws.Cells.SpecialCells(xlCellTypeFormulas).Locked = True

    ' 4. Blattschutz wieder aktivieren
    ' UserInterfaceOnly:=True erlaubt Makros weiterhin Änderungen, dem User aber nicht
    ws.Protect Password:=passwort, _
               DrawingObjects:=True, _
               Contents:=True, _
               Scenarios:=True

    On Error GoTo 0

    MsgBox "Alle Formeln sind nun geschützt. Eingabefelder sind frei!", vbInformation, "Schutz aktiviert"
End Sub

Schritt 4: Das Makro ausführen

  1. Schließe das VBA-Fenster (einfach oben rechts auf das X klicken).
  2. Gehe zurück zu deinem Excel-Sheet.
  3. Drücke ALT + F8, wähle das Makro FormelnSperrenEingabeErlauben aus und klicke auf Ausführen.

Erläuterung der Funktionsweise

  1. ws.Unprotect: Das Makro hebt zuerst den bestehenden Schutz auf, da es sonst keine Zell-Eigenschaften (wie "Gesperrt") ändern darf.
  2. ws.Cells.Locked = False: Standardmäßig sind alle Zellen in Excel als "Gesperrt" markiert (was aber erst bei aktivem Blattschutz wirkt). Wir setzen hier erst einmal alles auf "Offen".
  3. SpecialCells(xlCellTypeFormulas): Dies ist das Herzstück des Codes. Excel sucht automatisch nach allen Zellen, die eine Formel enthalten. Nur für diese wird die Eigenschaft .Locked = True gesetzt.
  4. ws.Protect: Zum Schluss wird der Blattschutz aktiviert. Erst jetzt greift die Sperre für die Formelzellen. Alle anderen Zellen (Zahlen, Texte, leere Zellen) bleiben beschreibbar.

Bonus: Schutz automatisch beim Öffnen aktivieren

Wenn du möchtest, dass der Schutz jedes Mal automatisch gesetzt wird, wenn du die Datei öffnest:

  1. Öffne wieder den VBA-Editor (ALT + F11).
  2. Doppelklicke links im Projekt-Explorer auf DieseArbeitsmappe.
  3. Füge diesen Code ein:
Private Sub Workbook_Open()
    Call FormelnSperrenEingabeErlauben
End Sub

Wichtiger Hinweis zum Speichern!

Da deine Datei nun ein Makro enthält, musst du sie als Excel-Arbeitsmappe mit Makros (.xlsm) speichern. Wenn du sie als normale .xlsx speicherst, geht das Makro verloren.

Zusammenfassung der Vorteile:

  • Dynamisch: Wenn du neue Formeln hinzufügst und das Makro erneut startest, werden auch die neuen Formeln sofort geschützt.
  • Sicherheit: Nutzer können keine Logik zerstören, aber dennoch ihre Daten in die vorgesehenen Felder eintragen.
  • Zeitersparnis: Kein manuelles Markieren von Bereichen mehr nötig.
0