Problem:
In Excel Arbeitsmappen kommt es manchmal vor dass sich die benutzerdefinierten Zellenformate in einer Arbeitsmappe wie Karnickel vermehren.
Warum passiert das?
Dies passiert meistens auf Grund dessen dass eine Arbeitsmappe von verschiedenen User bearbeitet wurde, Inhalte von einer Datei in die andere kopiert wurden, und Excel dabei sämtliche Zellenformate die vom User angelegt wurden in die andere Arbeitsmappe reinkopiert hat.
Irgendwann merkt man dann selber dass es da ein Problem gibt, oder Excel kommt mit einer Meldung, und teilt Dir mit dass es so nicht weitergehen kann… das Du Gefahr läufst Deine Datei zu korrumpieren!
Als ob es Deine Schuld wäre… und nicht etwa die von Excel selber!
Ungerecht.
Aber so ist das Leben. Und manchmal auch Excel.
Glücklicherweise gibt es eine Lösung.
Lösung:
Bei einem Zellenformat Chaos wie demjenigen im Bild, bleiben tatsächlich nicht mehr viele Optionen. Von Hand durchforsten ist keine Lösung… zumindest keine effiziente.
Dann muss VBA Code her…
Mit diesem Code Schnipsel werden sämtliche benutzerdefinierte Zellenformate in Excel auf den Standard zurückgesetzt. Bereits formatierte Zellen bleiben danach bestehen auf dem Arbeitsblatt.
ACHTUNG: bevor ihr das auf Eure Datei anwendet, eine Kopie der Datei machen! Dann erst benutzen! Ebenfalls muss ich erwähnen, dass alle Formate gelöscht werden und auf die Standardeinstellung zurückgesetzt werden… vielleicht auch solche die Ihr gerne beibehalten möchtet!
Hier der Code schön farbig formatiert…
…und hier das gleiche als Text um in ein neues Modul zu kopieren in Eurer Arbeitsmappe (und mit F5 ausführen).
'---------------------------------------------------------------------------
' Method : BenutzerdefinierteZellenFormateZuruecksetzen
' Author : ROLU
' Date : 24.09.2018
' Purpose: Rolls back custom formatting to default included in excel
'---------------------------------------------------------------------------
Sub ZellenFormatZuruecksetzen()
Dim lStyle As Long, j As Long
With ActiveWorkbook
lStyle = .Styles.Count
For j = lStyle To 1 Step -1
If Not .Styles(j).BuiltIn Then .Styles(j).Delete
Next j
End With
End Sub
Bevor ich (eigentlich einfach mit CTRL+A und Format > Standard) das mache, lösche ich immer zuerst sämtliche ungültigen Namensbezüge, Checke Bedingte Formatierungen und benutze auch Excel Inquire. Das blosse zurücksetzen der Formatierungen ist ein wenig Schwachatmig…
Der Code den ich zeige löscht die benutzerdefinierten Zellen Formate. Die bereits damit formatierten Zellen bleiben dabei übrigens bestehen. Ich sehe jedoch wie Du auf die Idee kommst dass es sich um reguläre Zellenformate handelt… werde im Post nachbessern und hervorheben dass ich die benutzerdefinierten Formate meine.
Definitiv eine hilfreiche Funktion! Könnte Excel auch direkt mit einbauen…
Ich arbeite schon länger mit einem ähnlichen Makro, da gerade bei alten Dateien der Formate-Wust die Dateigröße explodieren lässt. Ein Klick und schon ist die Datei wieder schön klein!
Danke!
Code-Ergänzungen, um die benutzerdefinierten Zellformat-Vorlagen selektiv zu löschen:
‘—————————————————————————
‘ Method : BenutzerdefinierteZellenFormateZuruecksetzen
‘ Author : ROLU
‘ Date : 24.09.2018
‘ Purpose: Rolls back custom formatting to default included in excel
‘—————————————————————————
Sub ZellenFormatZuruecksetzen()
Dim lStyle As Long, j As Long, sStyleName As String, lCount As Long
lCount = 0
With ActiveWorkbook
lStyle = .Styles.Count
For j = lStyle To 1 Step -1
sStyleName = .Styles(j).Name
If Not .Styles(j).BuiltIn Then
If MsgBox(“Soll die benutzerdefinierte Zellformat-Vorlage ‘” & sStyleName & “‘ definitiv gelöscht werden?”, 4372, “Benutzerdefinierte Zellformat-Vorlage löschen?”) = vbYes Then
.Styles(j).Delete
lCount = lCount + 1
End If
End If
Next j
End With
If lCount = 1 Then
MsgBox “Es wurde 1 benutzerdefinierte Zellformat-Vorlage gelöscht…”, 4160, “Gelöschte benutzerdefinierte Zellformat-Vorlagen”
ElseIf lCount > 1 Then
MsgBox “Es wurden ” & lCount & ” benutzerdefinierten Zellformat-Vorlagen gelöscht…”, 4160, “Gelöschte benutzerdefinierte Zellformat-Vorlagen”
Else
MsgBox “Es wurden keine benutzerdefinierten Zellformat-Vorlagen gelöscht…”, 4160, “Gelöschte benutzerdefinierte Zellformat-Vorlagen”
End If
End Sub