Post Quick Parcels

Excel Macros & VBA Part 11 - Post Quick Parcels (AQA AS Level 2001)

The following project uses many of the techniques we have studied so far.

Create a global variable in Module1:

Public NewID As Integer

This is used in the two data input forms, AddConsignment and AddParcels so must be public and available to all parts of the worksheet.

Set up the first worksheet of a spreadsheet as follows (just add the headings for now, not the data):

Rename this worksheet 'Consignments'. Click on the Button icon in the Control toolbar and add a button across E2:F3. Click on the Properties icon and set the Caption to 'Add Consignment'. Double-click the button and set the code as follows:

Private Sub AddConsignmentButton_Click()
Dim Count As Integer
Count = 2
While Cells(Count, 1) <> ""
Count = Count + 1
Wend
NewID = Count - 1
AddConsignment.Show
Sheets("Consignments").Select
End Sub

This sets the button to run a user form called AddConsignment. The variable Count is used to locate the next free row in the Consignments sheet. The AddConsignments form is then displayed, along with the worksheet in Excel.

Now create the second and third work sheets as follows:

Now create the form to add a consignment:

Launch VB (Alt-F11) and click the second button in the toolbar and select User Form from the list of options. A blank user form appears inside the VB editor.
Use the toolbox to add four text boxes and use the properties window to give them names as follows: ConsignmentNumber, CustomerID, Destination and Instructions. Add labels for the boxes.
Add two buttons, one with the name and caption 'Cancel' and the other 'Save and Add Parcels'.

Double click the form background to add a procedure for when the form is opened or activated. The default event is Click so use the drop-down list to change this to FormActivate.

Private Sub UserForm_Activate()
ConsignmentNumber = NewID
End Sub

The NewID variable was assigned when the button was clicked and its value is now assigned to ConsignmentNumber, so the user does not need to enter this, it is found automatically.

Double-click the cancel button and complete the code as follows:

Private Sub CancelButton_Click()
Unload AddConsignment
End Sub

This removes the AddConsignment form from the screen. Now double-click the SaveCloseButton and add this code:

Private Sub SaveCloseButton_Click()
If CustomerID = "" Or Destination = "" Then
MsgBox "CustomerID and Destination are required", vbExclamation, "Problem"
Else
Cells(NewID + 1, 1) = NewID
Cells(NewID + 1, 2) = CustomerID
Cells(NewID + 1, 3) = Destination
Cells(NewID + 1, 4) = Instructions
AddConsig.Hide
AddParcel.Show
End If
End Sub

This copies the values in the form controls into the spreadsheet cells, and then hides one form and opens the other. We can now test the form by clicking on the 'Add Consignment' button on the Consignments worksheet.

Clicking on 'Save and Add Parcels' runs the code which copies the data from the form to the spreadsheet and then closes this form and opens the Parcels form. Now we must create the parcels form as follows:

Switch to the VB editor and create a new form
Add five text boxes as shown in the diagram called ParcelID, ParcelLength, ParcelBreadth, ParcelHieght and ParcelWeight, with labels to the left.
Add command buttons with the captions 'Close' and 'Save'. Set the Visible property of the Close button to False (it switches to True when one parcel has been entered).

When the Parcels form is activated this code is run:

Private Sub UserForm_Activate()
Dim Count As Integer
Sheets("Parcels").Select
Count = 2
While Cells(Count, 1) <> ""
Count = Count + 1
Wend
ParcelID = Count - 1
End Sub

This finds the next empty row in the Parcels sheet.

The code for the four measurement boxes is pretty much the same in each case:

Private Sub ParcelBreadth_AfterUpdate()
If (Val(ParcelBreadth) <= 0 Or Val(ParcelBreadth) > 1.5) And ParcelBreadth <> "" Then
MsgBox "Sorry, but the breadth must be a numeric value greater than 0 but at most 1.5", vbExclamation, "Problem"
ParcelBreadth = ""
End If
End Sub

(Substitute the names of the other variables). This code validates the user input to check it is within the specified range of values. The code for the Close button is:

Private Sub CloseButton_Click()
Unload AddParcel
End Sub

The code for the Save button is the most extensive in the project:

Private Sub SaveButton_Click()
Dim Count As Integer, RunningWeight As Double

'Checks that Length, Breadth, Height and Weight have all been filled in.
If ParcelLength = "" Or ParcelBreadth = "" Or ParcelHeight = "" Or ParcelWeight = "" Then
MsgBox "Sorry, but the Length, Breadth, Height and Weight of the parcel must be entered before you can continue!", vbExclamation, "Problem"
Else
'Validation for total Parcel dimensions
If Val(ParcelLength) + Val(ParcelBreadth) + Val(ParcelHeight) > 3 Then
MsgBox "Sorry, but the Length, Breadth and Height must not add up to more than 3m!", vbExclamation, "Problem"
Else
'Validation on weight of all parcels in consignment
RunningWeight = 0
If CloseButton.Visible Then
Count = 2
While Cells(Count, 2) <> NewID
Count = Count + 1
Wend
While Cells(Count, 2) = NewID
RunningWeight = RunningWeight + Cells(Count, 6)
Count = Count + 1
Wend
End If
If (RunningWeight + ParcelWeight) > 200 Then
MsgBox "Sorry, but the total weight of the parcels so far adds up to " & RunningWeight & ", so you cannot add this parcel as well as the total mustn't add up to more than 200kg!", vbExclamation, "Problem"
Else
'If the program gets to here then all validation checks have passed.
'Data is copied to spreadsheet, form cleared for next Parcel.
Cells(ParcelID + 1, 1) = ParcelID
Cells(ParcelID + 1, 2) = NewID
Cells(ParcelID + 1, 3) = Val(ParcelLength)
Cells(ParcelID + 1, 4) = Val(ParcelBreadth)
Cells(ParcelID + 1, 5) = Val(ParcelHeight)
Cells(ParcelID + 1, 6) = Val(ParcelWeight)
Cells(ParcelID, 7).Copy
ActiveSheet.Paste Destination:=Cells(ParcelID + 1, 7)

ParcelLength = ""
ParcelBreadth = ""
ParcelHeight = ""
ParcelWeight = ""
Count = 2
While Cells(Count, 1) <> ""
Count = Count + 1
Wend
ParcelID = Count - 1 'Next Parcel ID set.
CloseButton.Visible = True 'Shows Close button after 1 parcel has been entered.
ParcelLength.SetFocus
End If
End If
End If
End Sub

The code does the following:

Checks that Length, Breadth, Height and Weight have all been filled in.
Checks that the total Parcel dimensions are within the permitted limit
Checks that the total weight of all parcels in consignment is within the permitted limit
Assigns values to the cells on the worksheet from the controls on the form
Resets the controls on the form to empty and sets the next ParcelID to the appropriate value

We now create the Consignment Note worksheet:

In D3 we have:

=LOOKUP($D$2,Consignments!$A$2:$B$65536)

This takes the contents of D2, the Consignment Number, and looks up the Customer ID from the Consignments sheet in the range specified (almost the whole of column B).

These lookups are repeated in D5 and D6 for Destination and Instructions:

=LOOKUP($D$2,Consignments!$A$2:$C$65536)

=LOOKUP($D$2,Consignments!$A$2:$D$65536)

In D8, D9 and D10 we have:

=COUNTIF(Parcels!B2:B65536,D2)

=SUMIF(Parcels!B2:B65536,D2,Parcels!F2:F65536)

=SUMIF(Parcels!B2:B65536,D2,Parcels!G2:G65536)

These count the number of parcels if the ID is the same as that in D2, and sum the parcel weights and charges if the ID is the same as that in D2.

The parcels with a Consignment Number matching that specified in D2 (picked up from the Consignments sheet when the routine is run) are listed in the cells from row 13. Cell G13 contains the lookup =VLOOKUP(F13,Prices!$A$2:$B$16,2).

The Consignment Note sheet is driven from a second button on the Consignments sheet, labeled 'View Consignment Note'. The code for this button is as follows:

Private Sub ConsignmentNote_Click()
Dim ConID As Integer, Count As Integer, Count2 As Integer

Sheets("Consignments").Select
ConID = Val(Cells(ActiveCell.Row, 1))
If ConID < 1 Then
MsgBox "Sorry, but you must click in the row of the the consignment whose note you which to view!", vbExclamation, "Problem"
Else
Sheets("Consignment Note").Select
Sheets("Consignment Note").Range("B13:G49").ClearContents
Sheets("Consignment Note").Cells(2, 4) = ConID
Count = 2
While Val(Sheets("Parcels").Cells(Count, 2)) <> ConID
Count = Count + 1
Wend
Count2 = 13
While Sheets("Parcels").Cells(Count, 2) = ConID
Sheets("Parcels").Cells(Count, 1).Copy
Sheets("Consignment Note").Paste Destination:=Sheets("Consignment Note").Cells(Count2, 2)
Sheets("Parcels").Cells(Count, 3).Copy
Sheets("Consignment Note").Paste Destination:=Sheets("Consignment Note").Cells(Count2, 3)
Sheets("Parcels").Cells(Count, 4).Copy
Sheets("Consignment Note").Paste Destination:=Sheets("Consignment Note").Cells(Count2, 4)
Sheets("Parcels").Cells(Count, 5).Copy
Sheets("Consignment Note").Paste Destination:=Sheets("Consignment Note").Cells(Count2, 5)
Sheets("Parcels").Cells(Count, 6).Copy
Sheets("Consignment Note").Paste Destination:=Sheets("Consignment Note").Cells(Count2, 6)
Sheets("Parcels").Cells(Count, 7).Copy
Sheets("Consignment Note").Paste Destination:=Sheets("Consignment Note").Cells(Count2, 7)
Count = Count + 1
Count2 = Count2 + 1
Wend
Sheets("Consignment Note").Select
ActiveWindow.SelectedSheets.PrintPreview
Sheets("Consignments").Select
End If
End Sub

This code does the following:

Uses the row chosen by the user before clicking the button to set a variable, ConID
Switches to the Consignment Note worksheet and clears the contents of cells in the range B13:G49
Searches the Parcels worksheet for the first parcel with the Consignment Number equal to ConID
Copies the parcel data from the Parcels worksheet to the Consignment Note sheet
Provides a Print Preview

The last worksheet, Daily Stats, prints out the value and weight of all parcels in the Parcels sheet.

A third button is added to the Consignments worksheet, labeled 'View Daily Statistics'. The code for this button is as follows:

Private Sub ViewStatsbutton_Click()
Sheets("Daily Stats").Select
ActiveWindow.SelectedSheets.PrintPreview
Sheets("Consignments").Select
End Sub

This code provides a print preview for the user.

Use the Back Button to Return to the previous page