264|EXCEL&VBA – Purchase Order Creation

Challenges
, , , ,
1

BYU Student Author: @Jason_Nguyen
Reviewers: @Kaden_Sandberg, @Keanu_Gauthier
Estimated Time to Solve: 60 Minutes

We provide the solution to this challenge using:

  • Excel

Need a program? Click here.

Overview
You work at the Cougar purchasing department. Today, the system is temporarily down, making your procurement system unavailable until the end of the day. Knowing that all sales must still be recorded during the day while having no permission to make adjustments to the master sales dataset, you want to write a program. Fortunately, you took IS201 during your sophomore year and learned a bit of VBA user forms. You want to utilize this tool to aid the recording duty of other clerks to create a temporary purchase order (PO) template.

Instructions

  1. Download the Challenge264_EXCELVBA_template file and open it.

  2. Create a button on the worksheet and label it “Purchase Order Creation”.

  3. Right-click the button and assign a macro that will display the user form when clicked.

  4. In the Developer tab, under “Insert,” select “UserForm.”

  5. Name this form POform (in the Properties window).

  6. Design the POform with the following fields (TextBoxes):
    a. Vendor Name
    b. Product
    c. Quantity
    d. Price
    e. Add two Command Buttons to the form:
    f. A Submit button (name it btnSubmit and caption it “Submit”)
    g. A Cancel button (name it btnCancel and caption it “Cancel”)

  7. Write macro code under the “Purchase Order Creation” button to load and display the POform when the button is clicked.

  8. Program the Submit button with the following logic:
    a. Validate that all fields are filled and that Quantity and Price are positive numbers.
    b. Transfer the data to the next available row in the “PO” sheet under the appropriate columns:
    c. Vendor Name
    d. Product
    e. Quantity
    f. Price

  9. Calculate the total (Quantity × Price) within the macro (do not use a formula in the sheet).

  10. Record the current date in the same row.

  11. Clear the form fields after submission and display a confirmation message.

  12. Ensure the Submit button macro appends new data on the next empty row without overwriting existing data.

  13. Program the Cancel button so that clicking it will close the form without saving any entered data.

Data Files

Suggestions and Hints

Here is the code that will let the data being entered under the beneath row:

nextRow = ws.Cells(ws.Rows.Count, “A”).End(xlUp).row + 1

Solution

Challenge264_EXCELVBA_solution.xlsm

Solution Video: Challenge 264|EXCELVBA_solution_video.mp4