Sample Macro for Excel: Invoice Manager

This is the procedure I created using the Visual Basic editor for Excel Worksheet. I am using this macro for the sales/charge invoice data entry for one of my clients.

On Sheet Macro

------------------------------------------

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Count > 1 Then Exit Sub

If Not Intersect(Target, Range("C18:L9999")) Is Nothing And Range("C" & Target.Row).Value <> Empty Then

   Range("B2").Value = Target.Row

   Inv_Load

End If

End Sub

Macros on Module

---------------------------------------------------

Option Explicit

Dim myRow As Long

Dim myCol As Long

Dim myInvoice

-----------------------------

Sub Findbtn_Click()

Inv_Find

End Sub

---------------------------

Sub Inv_Find()

With Sheet1

Dim inv_number As String

Dim cust_name

Dim myRange

inv_number = Range("F3")

If Len(inv_number) > 0 Then

   Set myRange = .Range("C18:J99")

   cust_name = Application.WorksheetFunction.VLookup(inv_number, myRange, 3, False)

   MsgBox "Invoice no. " & inv_number & " was issued to " & cust_name

   .Range("F5").Value = cust_name

Else

   MsgBox "You didn't enter an invoice number!"

End If

MsgBox "Invoice no. " & inv_number & " was issued to " & cust_name

'.Shapes("NewInvGroup").Visible = msoTrue

End With

End Sub

----------------------------

Sub Loadbtn_Click()

'Inv_Load

End Sub

---------------------------

Sub Inv_Load()

With Sheet1

  If Range("B2").Value = Empty Then Exit Sub

  Range("B4").Value = True 'Set Invoice Load to True

  myRow = Range("B2").Value 'Invoice Row

  For myCol = 3 To 12

     Range(Cells(16, myCol).Value).Value = Cells(myRow, myCol).Value

  Next myCol

  Range("F9").Value = Range("F7").Value + Range("F8").Value

  Range("B4").Value = True 'Set Invoice Load to True

  Range("B5").Value = False  'Set New Inv to False

  .Shapes("NewInvGroup").Visible = False

End With

End Sub

-----------------------------

Sub Clear_Display()

With Sheet1

   Range("F3,I3,I7,I8,F7,F8,F9,F11,I10,I11").ClearContents

   Range("F5") = Empty

   .Shapes("NewInvGroup").Visible = True

End With

End Sub

----------------------------

Sub Newbtn_Click()

Inv_New

End Sub

----------------------------

Sub Inv_New()

Dim rjSheet As Worksheet

Set rjSheet = Worksheets("Sheet1")

Range("F3").Value = Range("B3") + 1

   Range("B4").Value = True

   Range("B5").Value = True

   Range("I3,I7,I8,F7, F8,F9,I11").ClearContents

   Range("F5").Value = Empty

   rjSheet.Shapes("ExistInvGroup").Visible = msoFalse

   Range("B4").Value = False

End Sub

-----------------------------

Sub Inv_Val()

Dim tbInvNo, tbInvDate

Dim Msg, Style, Response

Msg = "Pls. enter amount of invoice."

Style = vbOKOnly + vbCritical

If Range("F3").Value = Empty Then

    Msg = "Pls. enter invoice number."

    Style = vbOKOnly + vbCritical

    Response = MsgBox(Msg, Style)

ElseIf Range("I3").Value = Empty Then

    Msg = "Pls. enter a valid date."

    Response = MsgBox(Msg, Style)

ElseIf Range("F5").Value = Empty Then

    Msg = "Pls. enter customer name."

    Response = MsgBox(Msg, Style)

Else

   procComputeVat          'call a procedure

   procValSuccess          'call a procedure

End If

End Sub

----------------------------

Sub Valbtn_Click()

Inv_Val

End Sub

---------------------------

Sub procComputeVat()

With Sheet1

Dim InvAmt, taxableSales, vatAmt, totalInvAmt As Currency

InvAmt = Range("F9").Value

taxableSales = InvAmt / 1.12

vatAmt = taxableSales * 0.12

totalInvAmt = Range("F9").Value + Range("I7").Value + Range("I8").Value

Range("F7").Value = taxableSales

Range("F8").Value = vatAmt

Range("F11").Value = totalInvAmt

End With

End Sub

-----------------------------

Sub procValSuccess()

Dim Msg, Style, Response

Msg = "Validation successful. Do you want to save this record?"

Style = vbYesNo

Response = MsgBox(Msg, Style)

If Response = 6 Then    ' User chose Yes.

    procSave            'call procedure

End If

End Sub

----------------------------

Sub procSave()

Dim rjSheet As Worksheet

Set rjSheet = Worksheets("Sheet1")

Dim myRow As Integer

Dim myCol As Integer

Dim lastInvNo

myRow = rjSheet.Range("B2")

myCol = 3

lastInvNo = rjSheet.Range("B3")

Dim InvAmt As Currency

For myCol = 3 To 12

   rjSheet.Cells(myRow, myCol) = rjSheet.Range(Cells(16, myCol).Value).Value

Next myCol

rjSheet.Range("B2").Value = myRow + 1

rjSheet.Range("B3").Value = lastInvNo + 1

End Sub