Beitrag aus SmartTools Excel Weekly
Inhalt einer Tabellenzelle in der Kopfzeile formatieren
Excel 365 2024 2021 2019 2016 2013
FRAGE Sie hatten in Ihrem Newsletter kürzlich eine Makrolösung vorgestellt, mit der sich der Inhalt einer Tabellenzelle dynamisch in die Kopfzeile integrieren lässt. Das habe ich gleich ausprobiert und es funktioniert fast perfekt. Mich stören nur zwei Dinge: In meinem Fall steht in der Tabellenzelle ein Datum, dem ich das Zahlenformat "JJJJ" zugewiesen habe. Wenn ich Ihr Makro einsetze, erscheint in der Kopfzeile trotzdem das vollständige Datum und nicht nur die Jahreszahl. Kann das Makro nur feste Zahlen- oder Textwerte verarbeiten? Der zweite Punkt betrifft die Formatierung der Kopfzeile selbst. Im Seitenlayout-Dialog hatte ich die Kopfzeilenbereiche in spezieller Schrift und Farbe formatiert. Bei Einsatz des Makros werden diese Formatierungen aber wieder auf Standardwerte zurückgesetzt, womit die Kopfzeile vom generellen Layout meiner Dokumente abweicht. Lässt sich die Kopfzeilenformatierung trotz Makro beibehalten?
U. Sieger
ANTWORT Beide Punkte sind lösbar; Punkt 1 ganz leicht und Punkt 2 mit ein wenig Geduld.
Vorab kurz zusammengefasst die Schritte, die wir in der zurückliegenden Ausgabe unseres Newsletters vorgestellt hatten. Ziel ist es, den Inhalt einer Tabellenzelle, der sich von Mal zu Mal ändern kann, dynamisch in die Kopfzeile des Tabellenblatts zu übernehmen. Im Beispielfall soll der Inhalt aus Zelle C12 im linken Bereich der Kopfzeile erscheinen.
Dazu erstellen Sie VBA-Code im "Workbook_BeforePrint"-Ereignis, das Excel automatisch beim Drucken eines Tabellenblatts ausführt. Die Ereignisprozedur legen Sie folgendermaßen an:
- Wechseln Sie aus Ihrer Excel-Arbeitsmappe mit Alt + F11 in die Entwicklungsumgebung.
- Dort wählen Sie Ansicht-Projekt-Explorer an, um das Unterfenster mit den geladenen VBA-Projekten einzublenden.
- Falls mehrere VBA-Projekte angezeigt werden, suchen Sie anhand des Dateinamens das Projekt der aktuellen Datei, und blenden Sie dann die untergeordneten Zweige ein.
- Im Zweig "Microsoft Excel Objekte" doppelklicken Sie auf das Element "DieseArbeitsmappe".
- Damit öffnen Sie das Codefenster des Elements "DieseArbeitsmappe". Am oberen Rand befinden sich zwei Dropdownfelder, aus denen Sie links das Objekt "Workbook" und rechts das Ereignis "BeforePrint" auswählen.
Das eingefügte Prozedurgerüst füllen Sie anschließend mit den gewünschten VBA-Anweisungen. Wir hatten damals folgenden Prozedurcode vorgestellt, der den Text "Rechnung" und danach den Inhalt aus Zelle C12 des Tabellenblatts "Rechnung" in den linken Kopfzeilenbereich einfügt:
Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim shInvoice As Worksheet
Dim objPgSetup As PageSetup
Dim strInvNr As String
Dim strHdrText As String
On Error GoTo Header_End
Set shInvoice = ThisWorkbook.Sheets("Rechnung")
strInvNr = shInvoice.Range("C12").Value
strHdrText = "Rechnung " & strInvNr
Set objPgSetup = shInvoice.PageSetup
objPgSetup.LeftHeader = strHdrText
Header_End:
Set objPgSetup = Nothing
Set shInvoice = Nothing
Exit Sub
End Sub
Wenn Zelle C12 in Ihrem Fall ein Datum enthält, dem Sie das Zahlenformat "JJJJ" zugewiesen haben (um nur die vierstellige Jahreszahl anzuzeigen), erscheint beim Drucken in der Kopfzeile dennoch das vollständige Datum. Das können Sie ganz einfach umgehen, indem Sie im Makro nicht die "Value"-Eigenschaft der Zelle verwenden, sondern die "Text"-Eigenschaft. Ändern Sie dazu die Codezeile, die mit "strInvNr =" beginnt in:
strInvNr = shInvoice.Range("C12").Text
Etwas komplexer ist die Formatierung der Kopfzeile per VBA-Code. Manuelle Anpassungen im Seitenlayout-Dialog werden beim Ausführen des Makrocodes zurückgesetzt. Und programmiertechnisch stellt das Objektmodell von Excel keine speziellen Eigenschaften oder Methoden zum Formatieren der Kopfzeile zur Verfügung.
Sie können lediglich Steuercodes verwenden, die Sie direkt in den Text für die Kopfzeilenbereiche einbauen. Folgende Steuercodes stellt Excel dafür bereit:
- &B: Formatiert den nachfolgenden Text fett
- &I: Formatiert den nachfolgenden Text kursiv
- &U: Unterstreicht den nachfolgenden Text
- &<Zahl>: Formatiert den nachfolgenden Text in der durch <Zahl> angegebenen Schriftgröße (zulässige Werte zwischen 8 und 72)
- &"<Schriftart>": Formatiert den nachfolgenden Text in der angegebenen Schriftart (verwenden Sie als <Schriftart> exakt die Bezeichnung, die im Schriftart-Dropdownfeld des Excel-Menübands angezeigt wird)
- &"<Schriftart>,<Schriftstil>": Ergänzend zum vorherigen Steuercode können Sie zusammen mit der <Schriftart> auch den <Schriftstil> angeben. Erlaubt sind die Ausdrücke "Bold" (fett), "Italic" (kursiv) und "Regular" (normal). Beispiel: &"Courier New,Italic"
- &K<Hex-Farbcode>: Formatiert den nachfolgenden Text in der gewünschten Farbe. Den <Hex-Farbcode> können Sie ermitteln, indem Sie einer Zelle eine benutzerdefinierte Farbe zuweisen. Im Farben-Dialog finden Sie auf der Registerkarte Benutzerdefiniert ein Feld Hex, aus dem Sie die Ziffernfolge ohne das führende #-Zeichen übernehmen.
In obigem Codebeispiel wird die String-Variable "strHdrText" in den linken Kopfzeilenbereich geschrieben. Sie setzt sich aus dem festen Text "Rechnung" und dem zuvor ermittelten Inhalt der Zelle C12 zusammen:
strHdrText = "Rechnung " & strInvNr
Um den Kopfzeilentext zu formatieren, setzen Sie die erwähnten Steuercodes ein. Hier ein Beispiel:
strHdrText = "&8&""Eras Medium ITC,Bold""Rechnung " & _
"&10&K808000&""Eras Medium ITC,Regular""" & strInvNr
Damit erreichen Sie folgende Formatierung:
- Der feste Text "Rechnung" wird in Schriftgröße "8", in der Schriftart "Eras Medium ITC" und in fett (Bold) formatiert.
- Der variable Text mit dem Inhalt aus C12 wird in der Schriftgröße "10" und ebenfalls in der Schriftart "Eras Medium ITC", dieses Mal aber in der Standard-Variante (Regular) formatiert. Außerdem erhält er die Farbe mit dem Hexcode "808000".
Beachten Sie die mehrfachen Anführungszeichen in der Codezeile. Sie sind nötig bei den Schriftartbezeichnungen, da diese innerhalb der Zeichenkette ebenfalls in Anführungszeichen eingeschlossen werden müssen.
Die Formatierung der Kopfzeile ist somit zwar etwas knifflig, aber immerhin ist sie möglich.