How to Create a Form in Microsoft Excel to Enter Data - Step by Step

Who I am
Catherine Le Nevez
@catherinelenevez
Author and references

What is an Excel form?

Forms in Excel are one method used to record information in spreadsheets. They are very useful for avoiding mistakes, as the inclusion of selection elements could simplify the task of adding information to an Excel file.

There are three types of modules, forms data, spreadsheets with form controls and user forms in VBA.

data forms

It is a tool Excel o a default option. It is possible to acquire one or more records. With the data module you can show the user only the information of one row of an entire table.



For example, if a cell has a formula, it is preserved because it cannot be changed via the form. Only the final result of the calculation will be displayed and the user will not be able to modify it.

Spreadsheets linked to form controls

You don't need to know how to program to create these types of forms. THE form controls they are introduced directly into the spreadsheet. Controls still create logical values.

It is an easy way to implement a form in Excel, because only the controls are added to the sheet. You can add combo boxes, buttons, check boxes, radio buttons, labels, practically any control belonging to VBA.

user forms in VBA

They are the familiar Visual Basic UserForm elements. It is the most graphical way to represent a form and they are created by the VBA editor. They are created in a simple way, by dragging the controls on the form and organizing it.

They also have a certain level of complexity because programming skills may be required to be optimal. What is certain is that its implementation saves more than a headache.



How to create a form in Microsoft Excel to enter data?

The first thing you need to do is enable developer options in the ribbon if it is not active. To activate it, click on File (shortcut Alt + A) and then on Options.

  • Select the ribbon customization option and in the second box, activate the Developer box.
  • In the programmer options select the Visual Basic option.
  • Select the Insert menu and click on the User Form option.


  • Add the necessary controls to the form. The best way to add it is to click on them and drag them to the module where you want to place it. Add everything you need such as labels to identify, text boxes where the user will enter data, lists, check boxes, radio buttons, scroll bars or buttons. You can add more controls, but usually these are the most used.
  • Once the user enters the data into the form, it is saved in an Excel sheet. But for this to happen, it is necessary program a button to perform the action.
  • The code used for registration is simple, it is only one line of code per register.

Worksheets ("Sheet1"). Range (“A1”). Value = Me.TextBox1.Value


This line indicates that the value added in the text box named TextBox is equal to the value in cell A1 found on Sheet1. You need to apply this same formula for each field you want to insert on the form, although it may vary slightly depending on the type of control.

TagsEccellere
add a comment of How to Create a Form in Microsoft Excel to Enter Data - Step by Step
Comment sent successfully! We will review it in the next few hours.