Creating Fully Automated User Forms in Excel [No VBA] PART 3

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.

Read here: Creating Fully Automated User Forms in Excel [No VBA] PART 1

Read here: Creating Fully Automated User Forms in Excel [No VBA] PART 2

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.

Conclusion

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!