Enter Data in an Excel UserForm

To make it easier for users to enter data in a workbook, you can create a UserForm. It requires some programming, and the steps are clearly shown in the following short videos.

In this example, inventory data is stored on a hidden worksheet. Users enter inventory data by opening the UserForm, filling in the boxes, and clicking a button.

Advantages to this method -- it's easy for users to enter the data, and the inventory data is protected from accidental deletion or editing.

The data is stored in a table on a hidden worksheet. In this example, there are four columns of data stored: PartID, Location, Date and Quantity.

When the user clicks the Add this part button on the UserForm, the record will be added to the bottom of this list.

UserForms are created in the Visual Basic Editor. In Excel, to open the Visual Basic Editor, hold the Alt key, and tap the F11 key on your keyboard.

This is where you'll add a UserForm and enter the programming code.

To get started, you'll insert a UserForm, by clicking on the Insert menu, then clicking UserForm. This will create a blank UserForm for your workbook.

The objects on a UserForm, such as buttons, and boxes where users can type, are called controls.

To allow users to enter data, you can add textbox controls to the form, with label controls to describe what the textboxes are for.

In the Part 1 video, shown below, you can see the steps to create the UserForm, add a textbox, and add a label for that textbox.

To allow users to perform an action, you can add command buttons to the user form. This form has two buttons:

Add This Part - users will click this button to add inventory data to the database

Close Form - users will click this button to close the form, when they're finished entering data.

The following video shows how to add the buttons.

The next step is to add code, to make the buttons perform an action when the button is clicked. The next video shows how to add the code.

The final step is to create a sheet with a button that users can click, to open the UserForm.

This button runs a macro that open the UserForm.

Sub Rectangle1_Click()
    frmParts.Show
End Sub

To download a sample file that contains the UserForm and all the code, you can visit the Create a UserForm In Excel page on my website.