Welcome to Part 2 of creating a fully automated user form in Microsoft Excel!
Before we begin, we recommend you to read Part 1 of creating a fully automated data entry form from scratch.
Read here: Creating a Fully Automated User Form PART 1
In the second part, we will elucidate on advanced topics like creating a database and integrating it with the automated form created in Part 1.
We will learn to record results or responses in understandable figures and create a button and activate it, without using VBA within a moment!
Recording responses of qualitative elements in the form
Now that the form is ready, let’s get started with recording or tracking user’s responses in one place. Follow the steps below to record responses.
First, it is important to make sense of the qualitative non-numerical elements by giving them a quantifiable identity on interaction.
Here, we need to record responses for the option buttons, numerically. Here’s how.
- Press CTRL and click on an option button.
- Right-click on the selected button, and press Format Control.
- Go to the Control tab in the new window.
- Click in Cell Link.
- Click on a cell where you want to record the button’s responses.
- You can also select a cell in a different sheet if you wish to record responses in a new sheet.
- Click OK.
You will see “1” displayed in the selected cell after pressing OK. Click away and notice that selecting the first option displays 1, and the second one displays 2.
Now, we have a numerical response for a qualitative element in Excel.
We’re recording the responses in a different sheet for better readability and understanding. You can choose to record responses in the same sheet as the form.
Recording responses of the entire form
The next step is to record the responses of the entire form.
We will record responses entered in the name, email, mobile, plan, and newsletter subscription fields. Here’s how.
- Select a blank area in the sheet or record the responses in a new sheet.
- Type = in a blank cell and click on the first entry field.
- And again type = and select the other response fields in a sequence of cells.
To track the response of the option buttons, use the IF formula. Here’s how.
- Type the IF formula in the sequence. Here’s where the control cell that displays 1 for yes and 2 for no is put to use.
- Type =IF(format control cell=1,”Yes”,”No”).
- Hit ENTER and you will see Yes if Yes is selected and No if No is selected.
If you’re tracking responses on a different sheet, the merged cells will impact response recording. Don’t worry, if you’re seeing the VALUE error. Here’s how you can fix it.
- Type = and navigate to the sheet with the form.
- Select a response, but don’t press ENTER yet!
- If you see a cell range in the formula bar, remove whatever comes after the first cell address.
- Press ENTER.
Your responses will record correctly without displaying the value error.
Creating a SUBMIT button of the form
Now, let us get started with creating a SUBMIT button for the user form. The users will click on the button, and responses get saved in the database that is integrated with the form. You will read about it further in this guide.
Here’s how to create a SUBMIT button.
- Select a few cells as per the size of the button.
- Click Merge & Center in the Home tab.
- Type SUBMIT or any text you like.
- Align the text to the center.
- Apply a text and cell color to display when the form is incomplete.
This is how the SUBMIT button looks like when the form is incomplete.
The next step is to activate the SUBMIT button once the form is complete. We will use the COUNTA formula to achieve this.
- You easily can do it in the same sheet or in a different sheet where you have recorded responses previously.
- Type =COUNTA(first field, second field,…). This will count the number of filled fields in your form.
- For the options field, select the text response from recorded responses.
- Copy the formula and press ENTER.
The COUNTA formula counts a cell only if there is any data entered in it. So, if 4 out of 5 fields are filled, the COUNTA result will be 4.
- Make sure you copied the formula.
- Click on the SUBMIT button and go to the home tab.
- Click on Conditional Formatting.
- Press New Rule.
- Choose Use a formula to determine which cells to format.
- Paste the formula in the dialogue box below and type =(total no. of fields). Here, the total is 5 fields, which have to be responded to for the SUBMIT button to activate.
This tells the formatting tool that if the result of the COUNTA formula is equal to the total number of fields, then apply a specific set of colors to the SUBMIT button.
- Now, click on Format below.
- Select a cell and font color to be displayed when the button is activated.
We know that the total number of fields will be displayed only if all fields have some data filled in them. If the result is lesser than the total, the button will not activate (or change its color).
Check this out!
Once you have applied conditional formatting to the button, the next step is to make the button responsive. When the user clicks on the button, the data in the form is saved and transferred to the database.
Creating Fully Automated User Forms in Excel [Without VBA] PART 3
Conclusion
This guide was part 2 of creating a fully automated user form in Excel. We learned to record quantitative and qualitative responses in a form and constructed active and inactive SUBMIT buttons in the form.
Part 3 is all about making the SUBMIT button responsive and being able to save and export the user data to a database and more.