Microsoft Excel Examples:
Georgetown Cleaning Services
 
 

Introduction

In this exercise, we will create an application that can be used to process orders for a dry cleaning store.

Practical Learning: Creating the Application
  1. Start Microsoft Excel with its default workbook and its worksheets
  2. Right-click Sheet1 and click Rename
  3. Change its name to CleaningOrders
  4. Save the file as Georgetown Cleaning Services
  5. On the main menu, click View -> Toolbars -> Control Toolbox
  6. Scroll down in the list of controls, click Microsoft Date and Time Picker 6.0 (SP4), and click the worksheet.
    If you don't see Microsoft Date and Time Picker 6.0 (SP4) (meaning if you don't have it), press Esc. When designing the worksheet, you will use the cells
  7. Right-click the new Date Time Control and click Properties
  8. In the Control Toolbox, click the ComboBox control and click the worksheet
  9. Complete the design of the worksheet as follows:
     
  10. Save the workbook
  11. Click the Date Time Picker control on the right side of Date Left
  12. In the Properties window, click (Name) and type dtpDateLeft
  13. Click the Date Time Picker control on the right side of Time Left
  14. In the Properties window, click (Name) and type dtpTimeLeft
  15. Change its Format to 2 - dtpTime
  16. Click the Date Time Picker control on the right side of Date Expected
  17. In the Properties window, click (Name) and type dtpDateExpected
  18. Click the Date Time Picker control on the right side of Time Left
  19. In the Properties window, click (Name) and type dtpTimeExpected
  20. Change its Format to 2 - dtpTime
  21. On the main menu, click Tools -> Visual Basic Editor
  22. In the Project window, double-click Sheet1 (CleaningOrders)
  23. In the Object combo box, select Worksheet
  24. In the Procedure combo box, select Activate and implement it as follows:
      
    Private Sub Worksheet_Activate()
    Me.ComboBox1.AddItem ("None")
    Me.ComboBox1.AddItem ("Women Suit")
    Me.ComboBox1.AddItem ("Dress")
    Me.ComboBox1.AddItem ("Regular Skirt")
    Me.ComboBox1.AddItem ("Skirt With Hook")
    Me.ComboBox1.AddItem ("Men 's Suit 2Pc")
    Me.ComboBox1.AddItem ("Men 's Suit 3Pc")
    Me.ComboBox1.AddItem ("Sweaters")
    Me.ComboBox1.AddItem ("Silk Shirt")
    Me.ComboBox1.AddItem ("Tie")
    Me.ComboBox1.AddItem ("Coat")
    Me.ComboBox1.AddItem ("Jacket")
    Me.ComboBox1.AddItem ("Swede")
    
    Me.ComboBox2.AddItem ("None")
    Me.ComboBox2.AddItem ("Women Suit")
    Me.ComboBox2.AddItem ("Dress")
    Me.ComboBox2.AddItem ("Regular Skirt")
    Me.ComboBox2.AddItem ("Skirt With Hook")
    Me.ComboBox2.AddItem ("Men 's Suit 2Pc")
    Me.ComboBox2.AddItem ("Men 's Suit 3Pc")
    Me.ComboBox2.AddItem ("Sweaters")
    Me.ComboBox2.AddItem ("Silk Shirt")
    Me.ComboBox2.AddItem ("Tie")
    Me.ComboBox2.AddItem ("Coat")
    Me.ComboBox2.AddItem ("Jacket")
    Me.ComboBox2.AddItem ("Swede")
    
    Me.ComboBox3.AddItem ("None")
    Me.ComboBox3.AddItem ("Women Suit")
    Me.ComboBox3.AddItem ("Dress")
    Me.ComboBox3.AddItem ("Regular Skirt")
    Me.ComboBox3.AddItem ("Skirt With Hook")
    Me.ComboBox3.AddItem ("Men 's Suit 2Pc")
    Me.ComboBox3.AddItem ("Men 's Suit 3Pc")
    Me.ComboBox3.AddItem ("Sweaters")
    Me.ComboBox3.AddItem ("Silk Shirt")
    Me.ComboBox3.AddItem ("Tie")
    Me.ComboBox3.AddItem ("Coat")
    Me.ComboBox3.AddItem ("Jacket")
    Me.ComboBox3.AddItem ("Swede")
    
    Me.ComboBox4.AddItem ("None")
    Me.ComboBox4.AddItem ("Women Suit")
    Me.ComboBox4.AddItem ("Dress")
    Me.ComboBox4.AddItem ("Regular Skirt")
    Me.ComboBox4.AddItem ("Skirt With Hook")
    Me.ComboBox4.AddItem ("Men 's Suit 2Pc")
    Me.ComboBox4.AddItem ("Men 's Suit 3Pc")
    Me.ComboBox4.AddItem ("Sweaters")
    Me.ComboBox4.AddItem ("Silk Shirt")
    Me.ComboBox4.AddItem ("Tie")
    Me.ComboBox4.AddItem ("Coat")
    Me.ComboBox4.AddItem ("Jacket")
    Me.ComboBox4.AddItem ("Swede")
End Sub
  25. In the Object combo box, select dtpTimeLeft
  26. In the Procedure combo box, select Change
  27. Implement the event as follows:
      
    Private Sub dtpTimeLeft_Change()
    Dim DateLeft As Date
    Dim TimeLeft As Date
    Dim Time9AM As Date
    
    ' The time at 9AM
    Time9AM = TimeSerial(9, 0, 0)
    
    ' Retrieve the date and time the customer left the clothes
    DateLeft = Me.dtpDateLeft.Value
    TimeLeft = Me.dtpTimeLeft.Value
    
    ' If the customer left the clothes before 9AM
    ' the clothes are promised to be ready the same day
    If TimeLeft <= Time9AM Then
        Me.dtpDateExpected.Value = DateLeft
        Me.dtpTimeExpected.Value = TimeSerial(17, 0, 0)
    Else ' Otherwise, the clothes will be ready the following day
        Me.dtpDateExpected.Value = DateAdd("d", 1, DateLeft)
        Me.dtpTimeExpected.Value = TimeSerial(8, 0, 0)
    End If
End Sub
  28. Close Microsoft Visual Basic and return to Microsoft Excel
  29. If you created the worksheet as in the above screenshot, type the following in the designated cell:
     
    Cell Type
    F14 =D14*E14
    F15 =D15*E15
    F16 =D16*E16
    F17 =D17*E17
    F18 =D18*E18
    F19 =D19*E19
    I14 =SUM(F14:F19)
    I16 =I14*I15
    I17 =I14+I16
  30. Format cells D14:D19 to display a Number with 2 Decimal Places
  31. Format cells I14, I16, and I17 to display Currency
  32. Click the Exit Design Mode button of the Control Toolbox
  33. Format the worksheet as you see fit
     
    Georgetown Cleaning Services - After Formatting
  34. Save the workbook and test it by processing a few cleaning orders
 
Home Copyright © 2004-2009 FunctionX, Inc.