Invoice Generator

' Invoice Generator
' Sheet structure: Invoice sheet with client details, line items, and totals
' Requires a "Clients" sheet with columns: ClientID (A), Name (B), Address (C), Email (D)
' Requires a "Settings" sheet with: CompanyName (B1), Address (B2), VAT Rate (B3), Invoice Prefix (B4)

Sub GenerateInvoice()
    ' Create a new invoice from line item data
    On Error GoTo ErrorHandler

    Application.ScreenUpdating = False

    Dim wsInvoice As Worksheet
    Dim wsSettings As Worksheet

    Set wsInvoice = ThisWorkbook.Sheets("Invoice")
    Set wsSettings = ThisWorkbook.Sheets("Settings")

    ' Get settings
    Dim companyName As String
    Dim companyAddress As String
    Dim vatRate As Double
    Dim invoicePrefix As String

    companyName = wsSettings.Range("B1").Value
    companyAddress = wsSettings.Range("B2").Value
    vatRate = Val(wsSettings.Range("B3").Value) / 100
    invoicePrefix = wsSettings.Range("B4").Value

    If companyName = "" Then
        MsgBox "Please configure company details in the Settings sheet.", vbExclamation
        Exit Sub
    End If

    ' Generate invoice number
    Dim invoiceNumber As String
    Dim lastInvoiceNum As Long
    lastInvoiceNum = Val(wsSettings.Range("B5").Value)
    lastInvoiceNum = lastInvoiceNum + 1
    wsSettings.Range("B5").Value = lastInvoiceNum
    invoiceNumber = invoicePrefix & Format(lastInvoiceNum, "0000")

    ' Clear previous invoice content (rows 10 onwards)
    wsInvoice.Range("A10:F100").ClearContents
    wsInvoice.Range("A10:F100").ClearFormats

    ' Write invoice header
    wsInvoice.Range("A1").Value = companyName
    wsInvoice.Range("A1").Font.Size = 18
    wsInvoice.Range("A1").Font.Bold = True

    wsInvoice.Range("A2").Value = companyAddress

    wsInvoice.Range("D1").Value = "INVOICE"
    wsInvoice.Range("D1").Font.Size = 24
    wsInvoice.Range("D1").Font.Bold = True
    wsInvoice.Range("D1").Font.Color = RGB(0, 102, 204)

    wsInvoice.Range("D2").Value = "Invoice No:"
    wsInvoice.Range("E2").Value = invoiceNumber
    wsInvoice.Range("E2").Font.Bold = True

    wsInvoice.Range("D3").Value = "Date:"
    wsInvoice.Range("E3").Value = Format(Date, "dd/mm/yyyy")

    wsInvoice.Range("D4").Value = "Due Date:"
    wsInvoice.Range("E4").Value = Format(DateAdd("d", 30, Date), "dd/mm/yyyy")

    ' Client details
    Dim clientName As String
    clientName = wsInvoice.Range("A5").Value
    If clientName = "" Then
        clientName = InputBox("Enter client name:", "Client Details")
        If clientName = "" Then Exit Sub
        wsInvoice.Range("A5").Value = clientName
    End If

    wsInvoice.Range("A4").Value = "Bill To:"
    wsInvoice.Range("A4").Font.Bold = True

    ' Look up client address
    Dim wsClients As Worksheet
    On Error Resume Next
    Set wsClients = ThisWorkbook.Sheets("Clients")
    On Error GoTo ErrorHandler

    If Not wsClients Is Nothing Then
        Dim clientRow As Long
        Dim lastClientRow As Long
        lastClientRow = wsClients.Cells(wsClients.Rows.Count, "B").End(xlUp).Row

        For clientRow = 2 To lastClientRow
            If wsClients.Cells(clientRow, 2).Value = clientName Then
                wsInvoice.Range("A6").Value = wsClients.Cells(clientRow, 3).Value
                wsInvoice.Range("A7").Value = wsClients.Cells(clientRow, 4).Value
                Exit For
            End If
        Next clientRow
    End If

    ' Line item headers
    Dim headerRow As Long
    headerRow = 10

    wsInvoice.Cells(headerRow, 1).Value = "Description"
    wsInvoice.Cells(headerRow, 2).Value = "Quantity"
    wsInvoice.Cells(headerRow, 3).Value = "Unit Price"
    wsInvoice.Cells(headerRow, 4).Value = "Amount"

    With wsInvoice.Range("A" & headerRow & ":D" & headerRow)
        .Font.Bold = True
        .Interior.Color = RGB(0, 102, 204)
        .Font.Color = RGB(255, 255, 255)
    End With

    ' Prompt for line items
    Dim itemRow As Long
    itemRow = headerRow + 1
    Dim subtotal As Double
    subtotal = 0

    Dim addMore As VbMsgBoxResult
    addMore = vbYes

    Do While addMore = vbYes
        Dim itemDesc As String
        Dim itemQty As Double
        Dim itemPrice As Double

        itemDesc = InputBox("Enter item description (or leave blank to finish):", "Line Item " & (itemRow - headerRow))
        If itemDesc = "" Then Exit Do

        itemQty = Val(InputBox("Enter quantity:", "Quantity", "1"))
        itemPrice = Val(InputBox("Enter unit price:", "Unit Price"))

        wsInvoice.Cells(itemRow, 1).Value = itemDesc
        wsInvoice.Cells(itemRow, 2).Value = itemQty
        wsInvoice.Cells(itemRow, 2).NumberFormat = "0"
        wsInvoice.Cells(itemRow, 3).Value = itemPrice
        wsInvoice.Cells(itemRow, 3).NumberFormat = "#,##0.00"
        wsInvoice.Cells(itemRow, 4).Value = itemQty * itemPrice
        wsInvoice.Cells(itemRow, 4).NumberFormat = "#,##0.00"

        ' Alternate row shading
        If (itemRow - headerRow) Mod 2 = 0 Then
            wsInvoice.Range("A" & itemRow & ":D" & itemRow).Interior.Color = RGB(240, 245, 250)
        End If

        subtotal = subtotal + (itemQty * itemPrice)
        itemRow = itemRow + 1

        addMore = MsgBox("Add another line item?", vbYesNo + vbQuestion)
    Loop

    ' Calculate totals
    Dim totalsRow As Long
    totalsRow = itemRow + 1

    Dim vatAmount As Double
    vatAmount = subtotal * vatRate

    Dim grandTotal As Double
    grandTotal = subtotal + vatAmount

    ' Write totals
    wsInvoice.Cells(totalsRow, 3).Value = "Subtotal:"
    wsInvoice.Cells(totalsRow, 3).Font.Bold = True
    wsInvoice.Cells(totalsRow, 4).Value = subtotal
    wsInvoice.Cells(totalsRow, 4).NumberFormat = "#,##0.00"

    wsInvoice.Cells(totalsRow + 1, 3).Value = "VAT (" & Format(vatRate * 100, "0") & "%):"
    wsInvoice.Cells(totalsRow + 1, 3).Font.Bold = True
    wsInvoice.Cells(totalsRow + 1, 4).Value = vatAmount
    wsInvoice.Cells(totalsRow + 1, 4).NumberFormat = "#,##0.00"

    wsInvoice.Cells(totalsRow + 2, 3).Value = "TOTAL:"
    wsInvoice.Cells(totalsRow + 2, 3).Font.Bold = True
    wsInvoice.Cells(totalsRow + 2, 3).Font.Size = 14
    wsInvoice.Cells(totalsRow + 2, 4).Value = grandTotal
    wsInvoice.Cells(totalsRow + 2, 4).NumberFormat = "#,##0.00"
    wsInvoice.Cells(totalsRow + 2, 4).Font.Bold = True
    wsInvoice.Cells(totalsRow + 2, 4).Font.Size = 14
    wsInvoice.Cells(totalsRow + 2, 4).Interior.Color = RGB(0, 102, 204)
    wsInvoice.Cells(totalsRow + 2, 4).Font.Color = RGB(255, 255, 255)

    ' Payment terms footer
    wsInvoice.Cells(totalsRow + 4, 1).Value = "Payment Terms: Net 30 days"
    wsInvoice.Cells(totalsRow + 5, 1).Value = "Thank you for your business!"
    wsInvoice.Cells(totalsRow + 5, 1).Font.Italic = True

    ' Auto-fit columns
    wsInvoice.Columns("A:E").AutoFit

    Application.ScreenUpdating = True

    MsgBox "Invoice " & invoiceNumber & " generated successfully!" & vbCrLf & _
           "Total: " & Format(grandTotal, "#,##0.00"), vbInformation

    Exit Sub

ErrorHandler:
    Application.ScreenUpdating = True
    MsgBox "Error generating invoice: " & Err.Description, vbCritical
End Sub

Sub ExportInvoiceToPDF()
    ' Export current invoice sheet to PDF
    On Error GoTo ErrorHandler

    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Invoice")

    Dim invoiceNum As String
    invoiceNum = ws.Range("E2").Value

    If invoiceNum = "" Then
        MsgBox "No invoice found. Please generate an invoice first.", vbExclamation
        Exit Sub
    End If

    ' Set print area
    Dim lastRow As Long
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row + 2

    ws.PageSetup.PrintArea = "A1:E" & lastRow
    ws.PageSetup.Orientation = xlPortrait
    ws.PageSetup.FitToPagesWide = 1
    ws.PageSetup.FitToPagesTall = 1
    ws.PageSetup.TopMargin = Application.InchesToPoints(0.75)
    ws.PageSetup.BottomMargin = Application.InchesToPoints(0.75)

    ' Save as PDF
    Dim pdfPath As String
    pdfPath = ThisWorkbook.Path & "" & invoiceNum & ".pdf"

    ws.ExportAsFixedFormat Type:=xlTypePDF, _
        Filename:=pdfPath, _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, _
        IgnorePrintAreas:=False

    MsgBox "Invoice exported to PDF:" & vbCrLf & pdfPath, vbInformation

    ' Open PDF
    Shell "explorer.exe """ & pdfPath & """", vbNormalFocus

    Exit Sub

ErrorHandler:
    MsgBox "Error exporting PDF: " & Err.Description, vbCritical
End Sub