VBA Excel - Example Application: College Park Auto Repair

Example Application:

College Park Auto Repair

Description

This is an example of an application used by a fictitious car repair shop. The starting spreadsheet allows an employee to register a repair order. This includes a customer's name and the car.

To process an order, the employee must provide a list of auto parts that would have been used to repair the car. Then the clerk must enter the list of jobs that were performed for the order.

Practical Learning: Introducing Workbooks

Start Microsoft Excel
Open the CPAR1 workbook
To save it, press F12
In the Save As Type combo box, select Excel Macro-Enabled Workbook (*.xlsm)
Change the name of the file to College Park Auto Repair1
Click Save
On the Ribbon, click Developer

In the Controls section of the Ribbon, click Insert

In the ActiveX Controls section, click Command Button

On the worksheet, click on the right side of Invoice #

Right-click the newly added button and click Properties

Using the Properties window, change the characteristics of the button as follows:
(Name): cmdOpenAutoRepair
Caption: open Auto Repair

In the Controls section of the Ribbon, click Insert

In the ActiveX Controls section, click Command Button

Change its properties as follows:
(Name): cmdNewAutoRepair
Caption: New Auto Repair

In the Controls section of the Ribbon, click Insert

In the ActiveX Controls section, click Command Button (ActiveX Control)

On the worksheet, click under the previously added button

Using the Properties window, change the characteristics of the button as follows:
(Name): cmdSaveAutoRepair
Caption: Save and Close Auto Repair

Move and enlarge the button appropriately:

CPAR

On the worksheet, right-click the New Auto Repair button and click View Code

Write the code as follows:

Option Explicit

Private AutoRepairExists As Boolean

Private Sub cmdNewAutoRepair_Click()
    AutoRepairExists = False
    
    Range("D4") = "": Range("D5") = Date: Range("D8") = ""
    Range("D9") = "": Range("D10") = "": Range("G10") = ""
    Range("J10") = "": Range("D12") = "": Range("G12") = ""
    Range("J12") = "": Range("D13") = "": Range("G13") = ""
    Range("B16") = "": Range("C16") = "": Range("H16") = ""
    Range("I16") = "": Range("J16") = "": Range("B17") = ""
    Range("C17") = "": Range("H17") = "": Range("I17") = ""
    Range("B18") = "": Range("C18") = "": Range("H18") = ""
    Range("I18") = "": Range("B19") = "": Range("C19") = ""
    Range("H19") = "": Range("I19") = "": Range("B20") = ""
    Range("C20") = "": Range("H20") = "": Range("I20") = ""
    Range("B21") = "": Range("C21") = "": Range("H21") = ""
    Range("I21") = "": Range("B24") = "": Range("J24") = ""
    Range("B25") = "": Range("J25") = "": Range("B26") = ""
    Range("J26") = "": Range("B27") = "": Range("J27") = ""
    Range("B28") = "": Range("J28") = "": Range("B29") = ""
    Range("J29") = "": Range("J33") = "5.75%"
    
    Range("D4").Select
End Sub

In the Object combo box, select cmdOpenAutoRepair

Implement its Click event as follows:

Private Sub cmdOpenAutoRepair_Click()
    Dim InvoiceNumber As String
    Dim Filename As String
    
    InvoiceNumber = Range("D4")
    AutoRepairExists = True
    
    If InvoiceNumber = "" Then
        MsgBox "You must enter an invoice number in Cell D4"
        Range("D4").Select
    Else
        Workbooks.Open InvoiceNumber & ".xlsx"
    End If
End Sub

In the Object combo box, select cmdSaveAutoRepair

Implement its Click event as follows:

Private Sub cmdSaveAutoRepair_Click()
    Dim InvoiceNumber As String
    Dim CurrentAutoRepair As Workbook
    
    InvoiceNumber = Range("D4")
    
    If InvoiceNumber = "" Then
        MsgBox "You must enter an invoice number in Cell D4"
        Range("D4").Select
    Else
        If AutoRepairExists = True Then
            Set CurrentAutoRepair = Workbooks(1)
            CurrentAutoRepair.Save
        Else
            Set CurrentAutoRepair = Workbooks(1)
            CurrentAutoRepair.SaveAs InvoiceNumber & ".xlsx"
        End If
        
        ActiveWorkbook.Close
    End If
End Sub

Return to Microsoft Excel

Create a repair order with an invoice number of 1001

Click Save Auto Repair

Click New Auto Repair

Return to Microsoft Excel