BYU Student Author: @klayton
Reviewers: @Hyrum, @MitchFrei, @Erick_Sizilio
Estimated Time to Solve: 30 Minutes
We provide the solution to this challenge using:
- Excel
- VBA
Need a program? Click here.
Overview
A small business owner, Emma, has just launched a new product line and wants to reach out to potential customers to introduce her products. Emma decides to collect customer information by hosting a giveaway on her social media page. To participate in the giveaway, participants must fill in an online form with their contact information.
The giveaway quickly gains popularity and Emma receives hundreds of entries with potential customers interested in her products. To keep track of all these new potential customers, Emma needs to add all the new entries to her Excel spreadsheet that she uses to manage her customer database.
Emma knows of your excel prowess and asks you to create a user form in excel to make it easier for her to add the customers into her existing customer database.
Instructions
The excel sheet provided is Emma’s customer database. It already has the user form created. To view it, open visual basic from the developer tab (alternatively you can hit Alt+F11). On the left there is a Forms and Modules Folder. If you do not already see the user form, double click on “userformCustomerInfo” to see it.
You’ll notice there are no text boxes. Add a text box for each of the fields.
- Give each text box a unique name that will make it easy to track in your VBA code
Next, double click the “Add to List” or “Reset” button and it should open a new window with two Private Subs named “cmdAddToList_Click()” and “cmdReset_Click()” This window is where you write your VBA code that dictates what happens when someone clicks either button. There are headers that are a roadmap for what your VBA code should include.
Some requirements for cmdAddToList_Click():
- First Name, Last Name, Phone Number, and Email are all required in order to paste the information onto the excel sheet. If any aren’t filled in, a message box should appear and prompt the user to fill in the required fields.
- Zip Code and Phone number should be read in as text, not numbers.
- After the customer information is pasted, each field should be cleared, and the form should stay open.
- The data should paste onto the next open row.
The cmdReset_Click() should only clear the fields on the form that have been populated.
There is a button on “Sheet1” with the text “Add New Customer Data” with the Macro “ShowUserForm()” assigned to it. In the Visual Basic Window, open “Module1” by double clicking on it. In this module you need to write code that will call on the user form.
Add the following customer information using the user form:
-
Michelle | Rodriguez | 444 Pine St | San Francisco | CA | 94101 | (415)-567-8901 | michelle.rodriguez@example.com
-
Ryan | Martinez | 333 Maple Ave | Seattle | Washington | 98101 | 206-678-9012 | ryan.martinez@example.com
-
Grace | Turner | 222 Cedar St | New York | NY | 10001 | (212)-901-2345 | grace.turner@example.com
-
Brian | Mitchell | 111 Oak St | Los Angeles | California | 90001 | 323-012-3456 | brian.mitchell@example.com
Data Files
When you download the file, as it is a macro-enabled workbook, your computer will naturally flag it as unsafe. In order to unblock the file, right-click the file name after downloading it, and hit properties. At the bottom of the properties window, there will be a box where you can check “Unblock.” Hit apply and the file should be good to go.
Suggestions and Hints
- There is a “Toolbox” button on the ribbon. Click it to open a small window with all the tools you’ll need to modify the User Form. You’ll want to use the “TextBox” tool.
- To rename the text boxes, open the properties windows (there is a button on the ribbon or hit F4) and name is the box. Common practice is to keep the type of tool it is in the name. For example, I named my text box that contains the first name “txtFirstName.”
- in VBA, the symbol for a text format is “@.” Use this in conjunction with “.NumberFormat” to set the input to text.
- The Sub ShowUserForm() only needs one line of code: “userformCustomerInfo.Show”
Solution
Challenge40_Solution.xlsm
Solution Video: Challenge 40|EXCEL&VBA – User Form Fun
