To make it easier for users to enter data in a workbook, you can create an Excel UserForm. Written instructions and step-by-step videos in this tutorial show you the steps.
To create a UserForm requires some programming, and you can see the steps in the videos shown below, and in the written instructions. In this tutorial:
Video: Basic UserForm - Demo
Before you start to build a Data Entry UserForm with Text Boxes, you can watch this short video that shows the completed form, and how it works.
You'll see how to enter data in the form, and then send that data to a hidden worksheet.
In this example, a parts inventory is stored on a hidden worksheet in a Microsoft Excel file..
This video shows how to create the Excel file, and set up the table where the data will be stored.
Set Up Worksheet
To start from scratch, follow the steps below:
This is the information that you will collect in the UserForm.
Create Named Table
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:
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.
UserForms are created with Excel VBA programming, in the Visual Basic Editor.
This video shows how to create the UserForm, and give it a name and a caption. The written steps are below the video.
Open Visual Basic Editor
Now you're ready to get started on the UserForm, so you will open the VBE window
To open the Visual Basic Editor, follow these steps:
The VBE opens, and at the left, you should see the Project Explorer, which lists all the workbooks you have open – even the hidden workbooks, such as add-ins.
You will be adding a UserForm to the PartsDbText01.xlsm workbook, and you should see its name in the list, preceded by "VBAProject".
You will also be using the Properties Window, which is usually shown below the Project Explorer.
Create a UserForm
Now that the VBE is set up, you can follow the steps below, to create a UserForm in the PartsDbText01.xlsm workbook
A blank UserForm appears, and the Toolbox should open.
You will use the tools on the Controls tab in the Toolbox to add controls, such as buttons, to the UserForm
When you add a UserForm to the workbook, it is automatically selected in the VBE.
Before you add any controls to the UserForm, you will give it a name, and change the caption in its Title Bar.
To name the UserForm, follow these steps:
After you change the form's name, you'll see two changes in the Visual Basic Explorer
Change UserForm Title Bar
Next, to change the caption in the UserForm's Title Bar, follow these steps:
The next step is to add four textboxes on the UserForm, where the data will be entered. A label will be added beside each textbox, to identify it.
To see how to add Textboxes to the UserForm, you can watch this short video. The written instructions are below the video
Add First Textbox to UserForm
The objects on a UserForm, such as buttons, and textboxes, are called controls.
To allow users to enter data, you can add textbox controls to the form, with label controls to describe them.
In this workbook's PartsData sheet, there are four columns (fields) – PartID, Location, Date and Qty.
In the UserForm, you will add a text box for each of these columns.
Follow these steps to add the first text box, for the PartID field:
To help people enter data, you can add label controls beside each of the textboxes, so it is clear what should be entered in each textbox.
Follow these steps to add a label for the first textbox:
With the new label selected, double-click on the Caption property in the Properties window.
Type Part and then press the Enter key
The UserForm now has one textbox and label, for the PartID field.
You'll add three more textboxes and labels to the UserForm. A quick way to create additional textboxes is to copy the existing textbox, click on the UserForm, and paste.
Follow these steps to create the additional textboxes and labels:
Name the New Textboxes
Next, follow the steps below, to name each of the new textboxes, and change its label's caption.
Align Textboxes and Labels
If the textboxes are not aligned, you can follow these steps to align them:
To allow users to perform an action, you can add command buttons to the UserForm.
This form will have 2 buttons:
This video shows how to add buttons to the UserForm, and the code that runs when you click those buttons.
Add Buttons to UserForm
To make the buttons perform an action, you create code that runs when the button is clicked.
Add code to the cmdAdd button
UserForm Add 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.
There is an explanation of how this code works, in the next section.
NOTE: After you paste the code, on the Menu bar, choose View | Object, to return to the UserForm
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, if you're interested.
At the start of the code, two variables are defined.
Dim iRow As Long
Dim ws As Worksheet Set ws = Worksheets("PartsData")
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
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.
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
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
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
Add code to the cmdClose button
Private Sub cmdClose_Click() Unload Me End Sub
To allow users to close the form by pressing the Esc key:
To prevent users from closing the form by clicking the X button
When the UserForm is opened, there is an X at the top right. In addition to using the Close Form button, people will be able to close the form by using the X. If you want to prevent that, follow these steps.
Private Sub UserForm_QueryClose(Cancel As Integer, _ CloseMode As Integer) If CloseMode = vbFormControlMenu Then Cancel = True MsgBox "Please use the Close Form button!" End If End Sub
Now, if someone clicks the X in the UserForm, they'll see your message.
This video shows how to test the UserForm textboxes and buttons.
To test the form, you can run it from the VBE.
If the tab order was incorrect (e.g. when you pressed the tab key you moved to the wrong textbox or button), you can change it
This video shows how to add a button to the worksheet, to open the UserForm, and hide the database sheet.
To make it easy for users to open the UserForm, you can add a button to a worksheet.
To finish the workbook, you can hide the sheet that contains the database.
The three videos below are older versions of the "Create Excel UserForm" instructions. In these videos, Excel 2003 was used, and the same steps can be followed in later versions.
Tip: When saving the file in Excel 2007 or later, save the Excel workbook as a macro-enable file type.
Before building the UserForm to input data, you can watch this short video that shows the completed form, and how it works.
In part 1 of 3, you'll see how to create a blank Userform. Then you'll name the UserForm, and next you'll add text boxes and labels.
Users will be able to type data into the text boxes. Labels are added beside the text boxes, to describe what users should enter into the text box
In Part 2 of 3, you'll learn how to add buttons and a title on the UserForm.
With buttons on the UserForm, a user can click to make something happen. For example, click a button after entering data in the text boxes, when you're ready to move the data to the worksheet storage area.
In Part 3 of 3, you'll learn how to add VBA code to the controls, and you'll see how to test the UserForm.
The VBA code runs when a specific event occurs, such as clicking a button, or entering a combo box. In this example, the user will click a button, and the VBA code will move the data to the worksheet storage area.
Last updated: January 19, 2024 3:59 PM