Automatisierte Rechnungsvorlage mit VBA in Excel erstellen

Schritt 6: Rechnungsübersicht (Tracker) erstellen

• Fügen Sie ein neues Arbeitsblatt hinzu und benennen Sie es z.B. als „Übersicht“.
• Erstellen Sie Spaltenüberschriften für die Daten, die Sie aus der Rechnung erfassen möchten (bei Bedarf anpassen), z. B.:
  Rechnung, Datum, Fälligkeit, Betrag, Kunde sowie eine zusätzliche Spalte für den Zahlungsstatus.
• Markieren Sie die Überschriften und drücken Sie Strg + T, um den Bereich als Tabelle zu formatieren.
• Benennen Sie die Tabelle im Reiter Tabellenentwurf in InvTracker um.
• Fügen Sie in der Spalte Zahlungsstatus ein Kontrollkästchen (Checkbox) ein, um zu markieren, ob eine Rechnung bezahlt wurde. Dazu klicken Sie auf der Registerkarte „Einfügen“ in der Gruppe „Steuerelemente“ auf „Kontrollkästchen“.

Schritt 7: Makro für Rechnung speichern erstellen

Damit die eingegebenen Rechnungen auch im Tabellenblatt „Übersicht“ nach der Eingabe gespeichert werden, muss ein Makro erstellt werden. Dazu:

• Falls die Registerkarte „Entwicklertools“ bei Ihnen nicht bereits vorhanden ist. Aktivieren Sie diese in dem Sie auf das Menüband mit Rechtsklick klicken und auf „Menüband anpassen“ gehen und ein Häkchen bei „Entwicklertools“ setzen.
• Auf der Registerkarte „Entwicklertools“ öffnen Sie den Visual Basic Editor. Im Visual Basic Editor wählen Sie die von Ihnen gespeicherte Datei „VBAProject (Rechnung mit Makros.xlsm)“ aus und fügen über das Menü „Einfügen“ > „Modul“ ein neues Modul hinzu:

• Nun geben Sie folgendes Makro in das neue Modul ein, um die Daten aus der Rechnungsvorlage in den Tabellenblatt „Übersicht“ zu speichern:

Sub RecordInvoice()

Dim inv As Long
Dim invDate As Date
Dim dueDate As Date
Dim amount As Currency
Dim customer As String
Dim invoiceSheet As Worksheet
Dim trackerSheet As Worksheet
Dim tbl As ListObject
Dim targetRow As Range

Set invoiceSheet = Sheet2
Set trackerSheet = Sheet3
Set tbl = trackerSheet.ListObjects("InvTracker")

' Read values from invoice
inv = invoiceSheet.Range("G9").Value
invDate = invoiceSheet.Range("G10").Value
dueDate = invoiceSheet.Range("G11").Value
amount = invoiceSheet.Range("G12").Value
customer = invoiceSheet.Range("G14").Value

' Safely decide where to write the data
If tbl.DataBodyRange Is Nothing Then
    ' Table has no rows — add one
    Set targetRow = tbl.ListRows.Add.Range
ElseIf Application.CountA(tbl.DataBodyRange.Rows(1).Resize(1, 5)) = 0 Then
    ' First row is empty — reuse it
    Set targetRow = tbl.DataBodyRange.Rows(1)
Else
    ' Add new row
    Set targetRow = tbl.ListRows.Add.Range
End If

' Write values to the table
targetRow.Cells(1).Value = inv
targetRow.Cells(2).Value = invDate
targetRow.Cells(3).Value = dueDate
targetRow.Cells(4).Value = amount
targetRow.Cells(5).Value = customer

MsgBox "Rechnung gespeichert!", vbInformation

End Sub

• Speichern Sie das eingegebene Makro, in dem Sie einfach das Modul Fenster schließen oder auf das Speichern Symbol klicken.
• Für eine einfache Verwendung des Makros fügen Sie eine Form-Schaltfläche und weisen das gespeicherte Makro der Form zu. Dazu klicken Sie auf der Registerkarte „Einfügen“ auf „Formen“ und wählen eine Form aus. Zeichnen Sie diese Form auf eine beliebige Stelle auf der Rechnung z.B. neben der Rechnungsvorlage.

• Nun weisen Sie das gespeicherte Makro der Form-Schaltfläche zu in dem Sie mit der rechten Maustaste auf die Form klicken und auf „Makro zuweisen“ gehen. Wählen Sie anschließend das Makro aus.

Schritt 8: Makro für zurücksetzen der Rechnung erstellen

Um eine neue Rechnung zu erstellen (löscht die alte Rechnung und vergibt eine neue fortlaufende Rechnungsnummer) muss ein neues Makro erstellt werden und einer neuen Form – Schaltfläche zugewiesen werden:

• Öffnen Sie erneut über die Registerkarte „Entwicklertools“ den „Visual Basic Editor“. Fügen Sie den folgenden Code in den bereits erstellten Module1 oberhalb des Makros „Sub RecordInvoice()“ ein:

Sub ResetInvoice()

Dim inv As Long

‚ Issue new invoice number
inv = Range(„G9“)
Range(„G9“) = inv + 1

‚ Clear cells ready for new invoice details
Range(„G14:G18,InvItems[[Artikelnummer]:[Rabatt]]“).ClearContents
Range(„G14“).Select

‚ Confirm new invoice is ready and number
MsgBox „Neue Rechnung. Neue Rechnungsnummer ist “ & inv + 1, vbInformation

End Sub

• Nun weisen Sie das gespeicherte Makro der Form-Schaltfläche zu in dem Sie mit der rechten Maustaste auf die Form klicken und auf „Makro zuweisen“ gehen. Wählen Sie anschließend das Makro aus.

Schritt 9: Bedingte Formatierung hinzufügen

Um überfällige und unbezahlte Rechnungen im Tabellenblatt „Übersicht“ hervorzuheben, fügen Sie eine bedingte Formatierung der Spalte „Status“ ein:

• Wählen Sie die Tabellenzeilen aus (ohne die Spaltenüberschriften). Gehe dann auf „Start“ > „Bedingte Formatierung“ > „Neue Regel“ > „Formel zur Ermittlung der zu formatierenden Zellen verwenden“ und geben folgende Formel ein:

Formel: =UND(NICHT($F2);$C2<HEUTE())

• Wählen Sie eine rote Füllung. Wenn die Rechnung überfällig ist und nicht als bezahlt markiert wurde, wird die Zelle dann mit der Füllung hervorgehoben.