Welcome to Part 3 of creating fully automated user forms in Microsoft Excel!
Before we begin, we recommend you to read Part 1 to learn to create a fully automated data entry form from scratch.
Later, read Part 2 to learn recording form responses, creating a form button, and making it active on completing the form and inactive otherwise.
In the third part, we will elucidate on creating a database integrated with the user form that we created in Part 1 and make the form button responsive.
The form should save and export the data entered by the user in a tabular database in Excel. All responses are in one place!
Making the submit button responsive
Let’s continue with making the SUBMIT button responsive or clickable in the form.
- Cut (CTRL+X) the button.
- Paste (CTRL+V) it slightly far away below the form.
- Once pasted, copy (CTRL+C) the button.
- Go back to the original position of the SUBMIT button, but do not paste it yet!
- Go to the Home tab.
- Pull down on Paste.
- Click the Linked Picture or Picture icon to paste as a picture.
- Go to the Picture Format tab and select a style for the button.
Take a look at the new style applied to the button.
Integrating the form with a database
It’s time to integrate the user form with the database to store the information for further processing and handling.
Let’s begin creating a database in a different sheet that will record the user responses in a tabular dataset. It will help us gather every user’s data in one place.
- Create a new sheet.
- Create the heading names similar to form field names.
- Select the headings and few rows below them, and press CTRL+T.
- Check My table has headers and click OK.
- Select a table style from the Table Design tab.
Before proceeding further, note that we will start recording macros to export the data to the database. Follow the steps carefully here.
- Before starting to record, create a new sheet.
- Go to the View tab.
- Pull down on Macro, and press Record macro.
- Or, start recording a macro from the status bar below.
- Set a name for your macro and hit OK.
- Copy the recorded responses of your form.
- Go to the new sheet.
- Right-click on a cell and press Paste Special.
- Choose Values under Paste and Transpose under Operations.
- Click OK.
- Without clicking away, copy the selection. Press CTRL+C.
- Go to the sheet with the database.
- Click on the cell under the first heading.
- Go to the Home tab.
- Pull down on Insert and press Insert copied cells.
- Go back to the user form.
- Click on the first response in the user form.
- Press and hold CTRL and select all other form responses other than the option field.
- Press Delete your keyboard to delete all responses.
- Click on the first response again.
- Stop recording the macro from the status bar or open the Macro options in the View tab.
Once the macro recording stops, follow the steps below.
- Right-click on the SUBMIT button.
- Click Assign Macro.
- Choose the macro name you recorded just now.
- Hit OK, and you’re good to go!
Delete the entry from the database and try filling up the form. Check if the responses are getting exported to the database.
- Now, save the workbook as a Macro-enabled workbook by clicking Save As in the File tab.
You now know how to successfully create a fully automated user data entry form in Microsoft Excel.
This tutorial was a step-by-step advanced guide to creating a fully automated user form in Excel. If you want to learn to create a form from scratch, check the PART 1 of these guides. Stay tuned for more advanced and informative tutorials like this!