Home

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 LearningPractical Learning: Introducing Workbooks

  1. Start Microsoft Excel
  2. Open the CPAR1 workbook
  3. To save it, press F12
  4. In the Save As Type combo box, select Excel Macro-Enabled Workbook (*.xlsm)
  5. Change the name of the file to College Park Auto Repair1
  6. Click Save
  7. On the Ribbon, click Developer
  8. In the Controls section of the Ribbon, click Insert
  9. In the ActiveX Controls section, click Command Button
  10. On the worksheet, click on the right side of Invoice #
  11. Right-click the newly added button and click Properties
  12. Using the Properties window, change the characteristics of the button as follows:
    (Name): cmdOpenAutoRepair
    Caption: open Auto Repair
  13. In the Controls section of the Ribbon, click Insert
  14. In the ActiveX Controls section, click Command Button
  15. Change its properties as follows:
    (Name): cmdNewAutoRepair
    Caption: New Auto Repair
  16. In the Controls section of the Ribbon, click Insert
  17. In the ActiveX Controls section, click Command Button (ActiveX Control)
  18. On the worksheet, click under the previously added button
  19. Using the Properties window, change the characteristics of the button as follows:
    (Name): cmdSaveAutoRepair
    Caption: Save and Close Auto Repair
  20. Move and enlarge the button appropriately:
     
    CPAR
  21. On the worksheet, right-click the New Auto Repair button and click View Code
  22. 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
  23. In the Object combo box, select cmdOpenAutoRepair
  24. 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
  25. In the Object combo box, select cmdSaveAutoRepair
  26. 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
  27. Return to Microsoft Excel
  28. Create a repair order with an invoice number of 1001
  29. Click Save Auto Repair
  30. Click New Auto Repair
  31. Return to Microsoft Excel
 
 
     
 

Home Copyright © 2009-2010 FunctionX, Inc.