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:
- 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
