209|EXCEL&VBA – Fill out an invoice using the User Form

Challenges
, , , ,
1

BYU Student Author: @Jimmy_Han
Reviewers: @Trent_Barlow, @Carter_Lee
Estimated Time to Solve: 20 Minutes

We provide the solution to this challenge using:

  • Excel
  • VBA

Need a program? Click here.

Overview
As a new sales associate at Cougar Acoustic Shop, you were given a special task by the sales manager. They wanted you to create a computer program (VBA script) that makes it easier to fill out invoices for acoustic guitars. So, when you press a button, a form pops up, and you just have to type in all the info about the sale – like the customer’s name, the credit card number, the inventory number, the price, and any extras. The goal is to speed up the sales process and make sure all the details are correct. It’s a bit technical, but it’s exciting because it fits with your love for music and your goal to make the customer experience better at Cougar Acoustic Shop.

Instructions

  1. Open the Chalenge209_Template.xlsx file in the Data Files section.
  2. Open VBA window (Alt + F11) and create a user form.
  3. In the user form, create labels of: Invoice #, Customer name, customer’s address, customer’s phone number, credit/debit card option, credit card number, inventory number, Inventory name, price per item, and amount.
  4. Next to each label, create a blank textbox so that users can enter data.
  5. Make sure to assign a name to each textbox so that you can conveniently refer to it in the VBA code.
  6. Create a command button within the user form. If the command button is clicked, the data that the user puts into each textbox should be transferred to the appropriate areas in the invoice template.
  7. Save and close the VBA. Now assign the macro to the orange button that is already placed on the top of the spreadsheet.
  8. Make sure to SAVE your file as “Excel Macro-Enabled Workbook” to enable the macro next time you open the file.

Data Files

Suggestions and Hints

If the macro in the solution file is not enabled, close the file first. Then, right-click the file and select Properties. In the Properties dialog box, check the Unblock box, and then click OK.

To specify which worksheet you are transferring the data entered by the user to, these two lines of code will be helpful.

Dim ws As Worksheet 
Set ws = ThisWorkbook.Sheets("Invoice Form")

Solution

Challenge209_Solution.xlsm

Solution Video: Challenge 209|EXCEL&VBA – Fill out an invoice using User Form