Home > Macros >UserForms Basic Excel UserForm for Data Entry Step-by-step videos show how to make a basic Excel UserForm with text boxes, for data entry. You can get the completed Excel workbook, in the Download section, at the end of the page. Author: Debra Dalgleish

Introduction: UserForm with TextBoxes

In the videos below, see how to build a basic UserForm in Excel, with text boxes for data entry.

In the completed workbook, click a worksheet button, and a macro opens the UserForm.

Basic UserForm - Demo

This video shows how the simple Data Entry UserForm works.

Set Up Worksheet This video shows how to create the file, and set up the table where the data will be stored. Set Up Worksheet To start from scratch, follow the steps below: Open a new, blank workbook Double-click on the sheet tab for Sheet1 Type PartsData as the sheet name, then press Enter, to complete the name In cells A1:D1, enter the headings for the parts inventory database, as shown below – PartID, Location, Date, Qty. This is the information that you will collect in the UserForm. Create Named Table Next, to create a named table, select one of the heading cells, and on the Ribbon's Insert tab, click the Table command In the Create Table dialog box, make sure that the data range is $A$1:$D$1, and add a check mark to the box for My table has headers, then click OK The headings, and row 2, have been converted to a named table, with drop down arrows in the heading cells, and automatic formatting When you select a cell in the named table, you will see a Design tab on the Ribbon, under the TableTools tab Save Workbook Macro Enabled Next, to save the file, follow these steps: Click the Ribbon's File tab, and click Save. Select a folder, and in the File name box, enter the name PartsDbText01 From the Save as Type drop down, select Excel Macro-Enabled Workbook (*.xlsm) Click the Save button, to save the file.

Show Developer Tab UserForms are created in the Visual Basic Editor (VBE), where you can see the Visual Basic for Applications (VBA) code. Before you start working in the VBE, check the Excel Ribbon, to see if it shows the Developer tab. If you do NOT see a Developer tab on the Excel Ribbon, follow the steps in this video, to show the Developer tab in Excel.

Create the UserForm This video shows how to create the UserForm, and give it a name and a caption.

Add Textboxes

This video shows how to add the textboxes, where the data will be entered.

Add Buttons

This video shows how to add buttons to the UserForm, and the code that runs when you click them.

Notes:

• The video shows how to copy the code from a PDF file, and paste it into the UserForm.
• Instead, copy the code from the UserForm Button Code section below.
• You will see this code at the 2:03 mark in the video.

After you complete this step, go to the Test the UserForm section, which is below the button code sections.

UserForm Button Code

Copy the code below, without the Private Sub and End Sub lines, and paste it into the UserForm, as shown in the video above.

• NOTE: In some places, you’ll see “xl” in the code, such as “xlRows”.
  • The characters after the x is a lower-case “L”, not the number 1.

There is an explanation of how this code works, in the next section.

Private Sub cmdAdd_Click()

Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("PartsData")

'find first empty row in database
iRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _
    SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1

'check for a part number
If Trim(Me.txtPart.Value) = "" Then
  Me.txtPart.SetFocus
  MsgBox "Please enter a part number"
  Exit Sub
End If

'copy the data to the database
'use protect and unprotect lines, 
'     with your password
'     if worksheet is protected
With ws
'  .Unprotect Password:="password"  
  .Cells(iRow, 1).Value = Me.txtPart.Value
  .Cells(iRow, 2).Value = Me.txtLoc.Value
  .Cells(iRow, 3).Value = Me.txtDate.Value
  .Cells(iRow, 4).Value = Me.txtQty.Value
'  .Protect Password:="password"
End With

'clear the data
Me.txtPart.Value = ""
Me.txtLoc.Value = ""
Me.txtDate.Value = ""
Me.txtQty.Value = ""
Me.txtPart.SetFocus

End Sub

How the Add Button Code Works

Here are a few details on how the Add button code works.

Variables

At the start of the code, two variables are defined.

• The iRow variable stores the number of rows on the PartsData worksheet

Dim iRow As Long

• The ws variable is set as the PartsData worksheet, where the data will be stored.

Dim ws As Worksheet
Set ws = Worksheets("PartsData")

Step 1

When the new data is added to the database, it should go in the first empty row on the PartsData sheet. The code finds the last entry in the sheet, and gets its row number. Then 1 is added to that number, and stored in the iRow variable.

'find first empty row in database
  iRow = ws.Cells.Find(What:="*",  SearchOrder:=xlRows, _
  SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1

Step 2

Before the new data is added to the database, the code checks to make sure that a part has been entered in the txtPart textbox.

'check for a part number
If Trim(Me.txtPart.Value) = ""  Then

If not, the txtPart textbox is selected.

Me.txtPart.SetFocus

A warning message is shown, and the procedure stops running. The record can't be added to the PartsData sheet, unless a Part has been entered.

MsgBox "Please enter a part number"
  Exit  Sub
  End If

Step 3

Next, the new data is added to the database, in the first empty row. The iRow variable stores that row number, and the other number in each line of code is the column number.

 'copy the data to the database
With ws
  .Cells(iRow, 1).Value = Me.txtPart.Value
  .Cells(iRow, 2).Value = Me.txtLoc.Value
  .Cells(iRow, 3).Value = Me.txtDate.Value
  .Cells(iRow, 4).Value = Me.txtQty.Value
End With

Step 4

Finally, the data is cleared from each of the textboxes, and the txtPart textbox is selected, so you are ready to enter another part, if desired.

'clear the data
Me.txtPart.Value = ""
Me.txtLoc.Value = ""
Me.txtDate.Value = ""
Me.txtQty.Value = ""
Me.txtPart.SetFocus

Test the UserForm

This video shows how to test the UserForm textboxes and buttons.

Finish the Workbook

This video shows how to add a button to the worksheet, to open the UserForm, and hide the database sheet.

Get the Sample File

• UserForm: Download the completed Basic Excel UserForm with TextBoxes, to see how it works. The zipped file is in xlsm format, and contains macro. Be sure to enable macros, when you open the workbook, if you want to test the UserForm.

Related Tutorials

Create a Basic UserForm

UserForm Dependent ComboBoxes

UserForm ComboBox VBA

UserForm TextBox Validation Code

UserForm with Help Pages