Contextures

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.

UserForm with TextBoxes and worksheet button

Basic UserForm - Demo

This video shows how the simple Data Entry UserForm works.

Set Up the Worksheet

This video shows how to create the file, and set up the table where the data will be stored.

Show the Developer Tab

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

 

Last updated: May 18, 2022 10:10 AM